| tablename as input-parameter for procedure [message #87332] |
Sun, 19 December 2004 08:09  |
hudo
Messages: 165
Registered: May 2004
|
Senior Member |
|
|
I'd like to have the tablename as a parameter for a procedure:
CREATE OR REPLACE
PROCEDURE myproc( p_table VARCHAR2 )
AS
CURSOR c_forcolumn (p_table VARCHAR2)
IS
SELECT alpha.ROWID ,alpha.scope_column
FROM p_table alpha
ORDER BY scope_column;
BEGIN
NULL;
FOR rec IN myproc(the_scope_table)
LOOP
IF (rec.scope_column LIKE 'ABC___' ) THEN
---do something
UPDATE p_table
SET scope_column = 'XYZ'
WHERE rec.ROWID = p_table.ROWID;
NULL;
END IF;
END LOOP;
END myproc;
Is there a way to implement this ?
|
|
|
|
| Re: tablename as input-parameter for procedure [message #87336 is a reply to message #87332] |
Sun, 19 December 2004 22:47  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Yes,
You can achive this by using Dynamic SQl & Refcursors.
E.g. :
Declare
TYPE ALPHA_TYPE IS REF CURSOR;
ALPHA_REC ALPHA_TYPE;
L_rowid Varchar2(1000):=null;
L_Scopecol varchar2(1000):=null;
Begin
OPEN ALPHA_REC FOR
'SELECT ROWID,SCOPE_COLUMN FROM '||
||P_TABLE||' Order by SCOPE_COULMN';
Loop
Fetch ALPHA_REC into L_rowid,L_scopecol;
Exit when ALPHA_REC%notfound;
IF (rec.scope_column LIKE 'ABC___' ) THEN
---do something
execute immidiate 'UPDATE '||p_table||
' SET scope_column = '||''''XYZ''''||
' WHERE rec.ROWID = '||L_rowid;
END IF;
End Loop;
End;
HTH
Regards
Himanshu
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
