I've created one procedure in that I've used the ref cursor which is defined in the
DBK_REPORTS
Package.
The input parameter for this procedure was my input query and the output parameters are
queryresult
as cursor and message. What I did is, within the
try
..
catch
block, I've opened this
refcursor
for my input query and if any syntactic errors occurs which will be Exception under the condition
OTHERS
, and it'll be stored to the output parameter message. It was done by assigning the result of
SQLERRM
function.
create or replace package DBK_REPORTS
is
TYPE record_type IS REF CURSOR;
create or replace PROCEDURE DBP_QUERY_RESULT
(
O_QUERY IN CLOB,
O_QRY_RES OUT DBK_REPORTS.record_type,
O_MESSAGE OUT VARCHAR2
)IS
V_QUERY CLOB;
BEGIN
V_QUERY := O_QUERY;
BEGIN
OPEN O_QRY_RES FOR TO_CHAR(V_QUERY);
EXCEPTION
WHEN NO_DATA_FOUND THEN
O_MESSAGE := 'NO RECORDS FOUND';
WHEN OTHERS THEN
O_MESSAGE := sqlcode || sqlerrm;
END;
END DBP_QUERY_RESULT;