SHARE
TWEET

Untitled

a guest Jul 21st, 2019 84 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT * FROM v$version;
  2.      
  3. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  4. PL/SQL Release 11.2.0.4.0 - Production
  5. CORE    11.2.0.4.0  Production
  6. TNS for Linux: Version 11.2.0.4.0 - Production
  7. NLSRTL Version 11.2.0.4.0 - Production
  8.      
  9. select 'Test: ' || nvl2('...', 'things', 'nothing') from dual
  10. union
  11. select 'Test: ' || nvl2('', 'things', 'nothing') from dual;
  12.      
  13. Test: things
  14. Test: nothing
  15.      
  16. create or replace procedure my_schema.SP_READ_MEMBER(noP in varchar2, nameP in varchar2, idNoP in varchar2, birthdayP in varchar2, resultP out sys_refcursor)
  17. is
  18. v_prg_name varchar2(20) := 'SP_READ_MEMBER';
  19. sys_sql    varchar2(1000);
  20.  
  21. begin
  22.   Insertlog(SYSDATE, v_prg_name, '1.0 Start');
  23.   sys_sql :=  sys_sql || 'select a.no, a.name, a.id_no, to_char(a.birthday, ''yyyy/MM/dd'') as birthday, ''REGISTERED'' as type, email, mobile from rep where 1=1 ';
  24.   sys_sql :=  sys_sql || nvl2(noP,'and no='''|| noP ||'''', ''); --PLS-00201
  25.  
  26.   open resultP for sys_sql;
  27.   Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error');
  28.  
  29.   exception
  30.   when others then
  31.     declare
  32.       error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
  33.       error_code NUMBER := SQLCODE;
  34.       error_msg  VARCHAR2(300) := SQLERRM;
  35.     begin
  36.       rollback;
  37.       DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg);
  38.       Insertlog(SYSDATE, v_prg_name,  error_msg || ', 3.0 ERROR, sql:' || sys_sql);
  39.     end;
  40. end;
  41. /
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top