-
Dynamic SQL
Hi All,
Please help me in pointing the mistake in the following procedure. It compiles and meets the condition but does not perform any thing.
CREATE OR REPLACE PROCEDURE SP_DEL_ARCHIVE_TARGET ( archtable VARCHAR2)
IS
arch_count Number;
pversion Number;
tname_arch VARCHAR2(200):=archtable||' p,D_version V';
Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=ARCHIVE AND D_VERSION.ARCHIVE_STATUS IS NULL';
tCommitLimit NUMBER := 10000;
tCommitCount NUMBER := 0;
TYPE tCursorType IS REF CURSOR;
tDeleteCursor tCursorType;
tRowID ROWID;
tsql varchar2(2000);
--PROCEDURE: SP_DEL_ARCHIVE
--
--ENVIRONMENT/LANGUAGE: PL/SQL
--
-----------------------------------------------------------------------
--PURPOSE: This procedure deletes data from ARCHIVE tables
-- before loading the production data to this table to clean-up table
-- ensure that data loaded by a prevous imcomplete runs is cleaned-up.
--
-----------------------------------------------------------------------
--VERSION HISTORY:
--------------------------------------------------------------------------
-- Version 1.0.0
-- Date: Dec. 16, 2002
-- Author: Vikram Jit Sharma
-----------------------------------------------------------------------------
cursor C1 is SELECT VERSION_KEY FROM D_VERSION
WHERE D_VERSION.VERSION_STATUS='ARCHIVE' and
D_VERSION.ARCHIVE_STATUS is NULL;
BEGIN
Open C1;
Loop
FETCH C1 into pversion;
exit when C1%notfound;
tsql:= 'select count (*) from '||tname_arch||' WHERE p.version_key = V.version_key '||CONDITION;
EXECUTE IMMEDIATE tsql INTO arch_count;
IF arch_count > 0 THEN
OPEN tDeleteCursor FOR
'SELECT ROWID FROM ' || archtable || ' WHERE VERSION_KEY =:tversion_key' USING pversion;
LOOP
FETCH tDeleteCursor INTO tRowID;
EXIT WHEN tDeleteCursor%NOTFOUND;
EXECUTE IMMEDIATE 'DELETE ' || archtable || ' WHERE ROWID = :vrowid' USING tRowID ;
IF tCommitCount > tCommitLimit
THEN
COMMIT;
tCommitCount :=0;
ELSE
tCommitCount := tCommitCount + 1;
END IF;
END LOOP;
END IF;
CLOSE tDeleteCursor;
COMMIT;
end Loop;
Close C1;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END;
/
Regards and Thanks,
Vikram
-
How do you expect to find any problems when you catch all errors and ignore them? Take out the exception clause -- your use of it here is bad programming practice.
-
Re: Dynamic SQL
CREATE OR REPLACE PROCEDURE SP_DEL_ARCHIVE_TARGET ( archtable VARCHAR2)
First, you should specify what type of parameter you are passing in. IN is the default, so if you don't specify IN, then IN is what it is. Challenge: find any book on PL/SQL that doesn't recommend always specifying the type of parameter. However, that is not your error here.
IS
Convention is to use AS, not IS, but this is not the error either
EXECUTE IMMEDIATE 'DELETE ' || archtable || ' WHERE ROWID = :vrowid' USING tRowID ;
How is :vrowid being recognized by the execute immediate command? Doesn't that make :vrowid a string value, and not a bind variable?
-
Dynamic SQL
Hi Slimdave and stecal,
Thanks for your reply. The procedure is being called by informatica mapping where the parameter is passed.
The error is in the following line
EXECUTE IMMEDIATE tsql INTO arch_count;
Any comments and suggestions.
Regards and Thanks,
Vikram
-
What error is being raised?
-
Executing a single row select with a bind variable, fetching into a single define variable:
Code:
DECLARE
str varchar2(200);
val varchar2(20);
ret varchar2(20);
BEGIN
str := 'select msg from msg where msg = :b1';
val := 'Hello';
EXECUTE IMMEDIATE str INTO ret USING val;
dbms_output.put_line('Value fetched from table: '||ret);
END;
Is tsql a number or varchar2? You declare it as a varchar2, but do a select count(*) - which would be a number.
Last edited by stecal; 12-17-2002 at 02:57 PM.
-
DYNAMIC SQL
Hi Slimdave and Stecal,
Thanks for your help. There was no syntex error. The following was the error.
old
Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=ARCHIVE AND D_VERSION.ARCHIVE_STATUS IS NULL';
New
Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=''ARCHIVE'' AND D_VERSION.ARCHIVE_STATUS IS NULL';
Thanks slimdave for pointing the exception error.
Regards and Thanks,
Vikram
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
|