Error - Identifier must be declared

[Running Bear]

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 -
PROCEDURE FCONTDBA.SET_SEQUENCES
On line: 7
PLS-00201: identifier 'TABLE_NAME' must be declared

Procedure -
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

[DaPi]

You will need to build a string for the select statement and use execute immediate.

[Running Bear]

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 -
ORA-00905: missing keyword
ORA-06512: at "FCONTDBA.SET_SEQUENCES", line 7
ORA-06512: at line 1


Call -
call Set_Sequences('TL_ID', 'Traffic_Lights')


Procedure -

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;

[DaPi]

Should be something like:
EXECUTE IMMEDIATE ('SELECT max('|| pk_name ||') From ' || table_name) INTO max_pk ;

RT_M: http://www.csee.umbc.edu/help/oracle8/server.815/a67842/10_dynam.htm#8749

[Running Bear]

Dapi,

That has sorted it. Thanks for all your help really, really appreciated! :D


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