Home » SQL & PL/SQL » SQL & PL/SQL » Declaring a Cursor in a Procedure
Declaring a Cursor in a Procedure [message #36629] Thu, 06 December 2001 18:44 Go to next message
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 Go to previous messageGo to next message
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

----------------------------------------------------------------------
Re: Declaring a Cursor in a Procedure [message #36637 is a reply to message #36629] Fri, 07 December 2001 03:47 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
try truncate table when you want to delete all rows in table.

----------------------------------------------------------------------
Previous Topic: Re: About SQL Pl/SqL error messages
Next Topic: book
Goto Forum:
  


Current Time: Thu Oct 29 21:29:12 CDT 2020