-
Error handling with 'OPEN FOR'
I am trying to add some error handling to this packaged procedure written by someone else. How can I check for an Oracle error in the query in m_sql_string? Or how can I check the results for an error so I can write the error to an error log table?
CREATE OR REPLACE PACAKGE P1
AS
TYPE CHCUR IS REF CURSOR;
PROCEDURE QRY_WR_STATUS_CHANGES (tsAfter IN VARCHAR2, rsResult OUT CHCUR);
END P1;
/
CREATE OR REPLACE PACAKGE BODY P1
AS
PROCEDURE QRY_WR_STATUS_CHANGES(tsAfter IN VARCHAR2, rsResult OUT CHCUR)
IS
m_sql_string VARCHAR2(30000);
BEGIN
m_sql_string := 'SELECT TS_STATUS, CD_STATUS, CD_WR, RowId
FROM TABLE_A
WHERE
(
NOT EXISTS (SELECT ''X'' FROM TABLE_B where TABLE_B.USER_NAME =TABLE_A.ID_OPER)
) AND
(
NOT EXISTS (SELECT ''X'' FROM TABLE_C where TABLE_C.wr = TABLE_A.CD_WR and
TABLE_C.dist = TABLE_A.CD_DIST)
)
AND
TABLE_A.TS_STATUS >
TO_DATE('''||tsAfter||''', '||'''MM/DD/YYYY HH24:MI:SS'')
AND CD_STATUS Like ''%X''';
OPEN rsResult FOR m_sql_string;
END QRY_WR_STATUS_CHANGES;
END P1;
/
Thanks in advance.
-
Shouldn't it be something like:
PROCEDURE QRY_WR_STATUS_CHANGES
(tsAfter IN VARCHAR2, rsResult OUT CHCUR)
IS
...
my_error varchar2(2000);
begin
...
exception
when others
then
my_error := sqlerrm;
rollback;
insert into your_log_table
(message
,log_date
,prog_source
)
values
(my_error
,sysdate
,'QRY_WR_STATUS_CHANGES'
)
;
commit;
raise;
end;
Regards
Ben de Boer
-
hi,
use:
CREATE OR REPLACE PACKAGE P1
and not
CREATE OR REPLACE PACAKGE P1
Cheers,
R.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
Thanks Bensr for your suggestion (and also for over-looking the typo). If I restructure it that way, will I be able to SELECT INTO the cursor variable rsResult, so that the results will be returned in that out parameter as they are now?
I was hoping to add error checking without restructuring, but couldn't figure out how.
-
I don't understand what you would like to return.
If it's the cursor var then maybe:
exception
when others
then
my_error := sqlerrm;
rollback;
insert into your_log_table
(message
,log_date
,prog_source
)
values
(my_error
,sysdate
,'QRY_WR_STATUS_CHANGES'
)
;
commit;
rsResult := null;
end;
or do you want to return the query results?
Regards
Ben de Boer
-
Yes, I want to return the query results, but also check those results to see if there is an error.
Unfortunatley, the WHEN OTHERS exception is not trapping errors that are being returned in the query.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|