Error - Identifier must be declared

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

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! :D

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