Hello Experts,
I have fairly simple requirement and I was not able to resolve, I am hoping someone could help me out with this.
I have an existing stored proc which captures data in a cursor and writes it to a file using UTL utilities. This is working fine. I want to add a condition before the cursor, if this condition is satisfied it should proceed to cursor and write the file, otherwise it just fail the procedure with a error message saying the condition not satisfied. I am doing something wrong here, can someone please correct the condition part. I think I might just use 'IF EXISTS'. Please advise. Thank you in advance.
Here is the template,
CREATE OR REPLACE PROCEDURE <PROC>
as
Variables defining
n_cnt number:=0;
...
...
..
---- Below is the condition I want to pass to go any further else I want to fail the procedure--
BEGIN
SELECT COUNT(TABLE_NAME) ERR into n_cnt
FROM EXTRACT
WHERE
AND STATUS = 'Error';
IF n_cnt <> 0 THEN Go ---Continue the procedure---
ELSE END PROCEDURE ---Fail the procedure---
---- Creates a Cursor/temp table which holds the data to be processed row by row which is used to write to a file
CURSOR <CURSOR>
IS
---- Main Select Query begins here ----
SELECT DISTINCT FIRSTNAME
, LASTNAME
FROM NAMES
;
BEGIN
UTL_FILE.PUTF(v_FILE_HANDLE, '%s%s\n',
'FIRSTNAME'||'|',
'LASTNAME');
OPEN CURSOR;
-- Fetch the Cursor into temporary record
FETCH CURSOR INTO REC;
WHILE CURSOR%FOUND
-- Using Loop to load the data
LOOP
-- Using UTL Package to write to a file
UTL_FILE.PUTF(v_FILE_HANDLE, '%s%s\n',
REC.FIRSTNAME||'|',
REC.LASTNAME );
end loop;
END '