-
Identifier must be declared ***Resolved****
I've written a procedure to update the sequences in my DB. I'm getting an error when I compile it. Says the identifier 'table_name' must be declared, which it is as an input param. Does anyone know what the probem might be, and how to fix it?
Error -
Code:
PROCEDURE FCONTDBA.SET_SEQUENCES
On line: 7
PLS-00201: identifier 'TABLE_NAME' must be declared
Procedure -
Code:
CREATE Or REPLACE PROCEDURE Set_Sequences (pk_name In VARCHAR2, table_name In VARCHAR2) IS
max_pk NUMBER;
BEGIN
SELECT max(pk_name) INTO max_pk From table_name;
If max_pk = 0 Then
max_pk :=1;
End if;
EXECUTE IMMEDIATE ('drop sequence ' || pk_name);
EXECUTE IMMEDIATE ('create sequence ' || pk_name || ' start with ' || max_pk || ' increment by 1 min value 1 nocycle cache 20 noorder');
COMMIT;
END;
Regards Allan
Last edited by Running Bear; 10-16-2003 at 03:05 AM.
-
You will need to build a string for the select statement and use execute immediate.
-
Dapi,
Thanks for that I've now managed to get it to compile, however when I call it. I'm getting an error missing keyword -
Any thoughts?
Error -
Code:
ORA-00905: missing keyword
ORA-06512: at "FCONTDBA.SET_SEQUENCES", line 7
ORA-06512: at line 1
Call -
Code:
call Set_Sequences('TL_ID', 'Traffic_Lights')
Procedure -
Code:
CREATE OR REPLACE PROCEDURE Set_Sequences (pk_name IN VARCHAR2, table_name IN VARCHAR2) IS
max_pk NUMBER;
BEGIN
EXECUTE IMMEDIATE ('SELECT max('|| pk_name ||') INTO ' || max_pk || ' From ' || table_name);
If max_pk = 0 then
max_pk :=1;
end if;
EXECUTE IMMEDIATE ('drop sequence ' || pk_name);
EXECUTE IMMEDIATE ('create sequence ' || pk_name || ' start with ' || max_pk || ' increment by 1 min value 1 nocycle cache 20 noorder');
COMMIT;
END;
-
Should be something like:
EXECUTE IMMEDIATE ('SELECT max('|| pk_name ||') From ' || table_name) INTO max_pk ;
RT_M: http://www.csee.umbc.edu/help/oracle...dynam.htm#8749
-
Dapi,
That has sorted it. Thanks for all your help really, really appreciated!
Code:
CREATE OR REPLACE PROCEDURE Set_Sequences (pk_name IN VARCHAR2, table_name IN VARCHAR2) IS
max_pk NUMBER;
BEGIN
EXECUTE IMMEDIATE ('SELECT max('|| pk_name ||') From ' || table_name) INTO max_pk;
EXECUTE IMMEDIATE ('drop sequence ' || pk_name);
EXECUTE IMMEDIATE ('create sequence ' || pk_name || ' start with ' || max_pk || ' increment by 1 minvalue 1 nocycle cache 20 noorder');
COMMIT;
END;
Thanks for the link to the user guide, will come in handy in the future.
Cheers Al
Last edited by Running Bear; 10-15-2003 at 12:29 PM.
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
|