Declaring a Cursor in a Procedure [message #36629] |
Thu, 06 December 2001 18:44 |
Elav
Messages: 7 Registered: October 2001
|
Junior Member |
|
|
Hi,
I wrote one procedure with a Cursor declared in it.
But when i tried to use the IN parameter of the Procedure in the Cursor, it gives me error. I've attaching my query also, for your reference. Need your assistanc on this.
Rgds
Elav
CREATE OR REPLACE PROCEDURE DES_SU.PROC_FLUSH_TABLE
(L_TABLE_NAME IN VARCHAR2) IS
BEGIN
-- When this is called for Aborted Imports
DECLARE
CURSOR del_rvnstg_cur
IS
SELECT ROWID
FROM L_TABLE_NAME
FOR UPDATE;
del_rec del_rvnstg_cur%ROWTYPE;
BEGIN
FOR del_rec IN del_rvnstg_cur
LOOP
DELETE
FROM L_TABLE_NAME
WHERE CURRENT OF del_rvnstg_cur;
IF MOD (del_rvnstg_cur%rowcount, 100000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
END;
/
--------
It gives me error "PLS-00201: identifier 'L_TABLE_NAME' must be declared".
----------------------------------------------------------------------
|
|
|
Re: Declaring a Cursor in a Procedure [message #36631 is a reply to message #36629] |
Thu, 06 December 2001 19:17 |
Satish Shrikhande
Messages: 167 Registered: October 2001
|
Senior Member |
|
|
create or replace procedure ppp as
str varchar2(10);
cursor c1 is
select table_name from user_tables;
begin
for z in c1 loop
str:='delete '|| z.table_name;
execute immediate str;
end loop;
end;
here ou will have to look for the constraints , as u can't define a master table records having records in child table , got me
----------------------------------------------------------------------
|
|
|
|