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;
Bluesathish was his alice name, He has been Working with Microsoft Visual studio from the past 7 years and worked its versions like VS2003, VS2005, VS2008 and Present VS2010. And in his free times he likes to play cricket and videogames, sometimes surfing in codeproject.
He likes to share things/thoughts/ideas which he knew, I think thats what he subscribed in CP