DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Error - Identifier must be declared

  1. #1
    Join Date
    May 2003
    Posts
    49

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You will need to build a string for the select statement and use execute immediate.

  3. #3
    Join Date
    May 2003
    Posts
    49
    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;

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  5. #5
    Join Date
    May 2003
    Posts
    49
    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
  •  


Click Here to Expand Forum to Full Width