-- Дать профайл пользователя
Procedure GetProfile ( ASuperKey varchar2, HTTPRES out varchar2 ) is
-- [17.08.2016 21:47]
 ouResult clob;
 tm number;
--
 lClobWriter pls_integer:=1;
 lBlockSize pls_integer:=16000;
 vBuf varchar2(16000);
--
 res number:=0;
 UBlock pf_types.pf_t_user_block; 
 
 -- Информация клиента 
 cursor user_cur is
  select us.us_name,decode(SubStr(us.us_phone,1,4),'7000',null,us.us_phone) phone,us.us_email,d.client_bank_account   
    from pf_users us 
   inner join pf_users_dop d on d.us_id = us.us_id
   where us.us_id = UBlock.rUser_ID; 
   
 us_info user_cur%ROWTYPE;
 
 vMsg varchar2(512);
begin
 pf_json_error('test','profile');
 tm:=MyGetTickCount;
 begin   
 
  UBlock:=pf_f_get_user_block(ASuperKey);
  if UBlock.rFind = 1 then   
   
   ouResult:=null;
   for us_info in user_cur 
   loop
    res:=1;
    ouResult:='"user":{"name":"'||us_info.us_name||'","phone":"'||us_info.phone||'","email":"'||us_info.us_email||'","pay":"'||us_info.client_bank_account||'"},';
   end loop;      
   
  else
   vMsg:='Пользователь не найден';
  end if;
    
  if res=0 then 
   ouResult:='"msg":"'||vMsg||'",';     
  end if;  
  
  ouResult:=ouResult||'"result":'||res; 
 exception
  when others then
   begin
    ouResult:=null;
    pf_json_error(SQLCODE||' - '||SQLErrm,dbms_utility.format_error_backtrace);
    
    if ouResult is null then
     ouResult:='"result":0,"error":"'||replace(replace(SQLCODE||' - '||SQLErrm,'"',''''),SQLCODE||' - ORA'||SQLCODE||': ')||'"';
    end if;
 
    if SQLCODE = -6508 or sqlcode = -20787 then 
     raise_application_error (-20787,'RCS');
    end if;
 
   end;
 end;
 
 tm:=MyGetTickCount-tm;
 ouResult:='{'||ouResult||',"speed":'||To_Char(tm)||'}';
 
 -- Записать клоб в ответ
 loop   
  vBuf:=SubStr(ouResult,lClobWriter,lBlockSize);   
  HTTPRES:=Concat(HTTPRES,vBuf);
  lClobWriter:=lClobWriter+lBlockSize;
  exit when lClobWriter>Length(ouResult);  
 end loop; 
  
end;

create or replace procedure pf_json_error ( ER_Msg varchar2, ER_JSON varchar2, AIP varchar2 default null ) as pragma autonomous_transaction;
-- [25/01/2015] Записывает ошибки
begin
 insert into pf_json_errors(er_date,er_msg,er_json,ip)
   values (sysdate,Er_msg,Er_JSON,AIP);
 commit;
end;

declare
 vRec clob;
 vv varchar2(123):='auZPqKRvzOtfTFvWTTLOmEsqq3447968';
begin
 Execute immediate 'begin pf_admin.adm_sms_hist(:b,1,:v); end;' using vv, out vRec;
end;