Dynamic SQL

[archie06]

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;
/

[slimdave]

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.

[stecal]

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?

[archie06]

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.

[slimdave]

What error is being raised?

[stecal]

Executing a single row select with a bind variable, fetching into a single define variable:

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.

[archie06]

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.