Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT * FROM v$version;
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
- PL/SQL Release 11.2.0.4.0 - Production
- CORE 11.2.0.4.0 Production
- TNS for Linux: Version 11.2.0.4.0 - Production
- NLSRTL Version 11.2.0.4.0 - Production
- select 'Test: ' || nvl2('...', 'things', 'nothing') from dual
- union
- select 'Test: ' || nvl2('', 'things', 'nothing') from dual;
- Test: things
- Test: nothing
- 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)
- is
- v_prg_name varchar2(20) := 'SP_READ_MEMBER';
- sys_sql varchar2(1000);
- begin
- Insertlog(SYSDATE, v_prg_name, '1.0 Start');
- 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 ';
- sys_sql := sys_sql || nvl2(noP,'and no='''|| noP ||'''', ''); --PLS-00201
- open resultP for sys_sql;
- Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error');
- exception
- when others then
- declare
- error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
- error_code NUMBER := SQLCODE;
- error_msg VARCHAR2(300) := SQLERRM;
- begin
- rollback;
- DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg);
- Insertlog(SYSDATE, v_prg_name, error_msg || ', 3.0 ERROR, sql:' || sys_sql);
- end;
- end;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement