Re: Create a table in a procedure

landau1999it_at_yahoo.it (Andrew) wrote in message news:<e4d18387.0212190602.5b0a839a_at_posting.google.com>...
> FIrst of all Marry Christmas and Happy new Year to all people around.
> I'm a newbie on a Oracle DB world and I need need to create a procedure like that

   You don't provide details about the package your procedure is part of, did it compile succesfully ? Looks like package state is invalid. Does "show errors package pckTableManager" display anything ?

   Igor.

>
> PROCEDURE sp_GenerateTable(idLabel IN number) IS
> BEGIN
> -- Find the name of the table to create
> SELECT TableName INTO tableToCreate FROM LABEL
> WHERE LABELID = idLabel;
>
> -- sp_CreateTable(tableToCreate);
>
> EXECUTE IMMEDIATE 'CREATE TABLE '|| tableToCreate ||'
> (
> STAR varchar2(200),
> WARS varchar2(100)
> )';
>
> END;
>
> -- Eventually
>
> procedure sp_CreateTable(tableName IN varchar2) IS
> BEGIN
> EXECUTE IMMEDIATE 'CREATE TABLE '|| tableName ||'
> (
> TSAT varchar2(200),
> WARS varchar2(100)
> )';
> END;
>
> +++++++++++++++++++++++++++++++++++++++++++++++++
>
> but when I try ti execute the procedure I get this error
>
> SQL> exec pckTableManager.sp_generatetable(2);
> BEGIN pckTableManager.sp_generatetable(2); END;
>
> *
> ERRORE alla riga 1:
> ORA-04068: lo stato esistente dei package è stato eliminato
> ORA-04061: lo stato esistente di package "CSGD.PCKTABLEMANAGER" è stato
> invalidato
> ORA-04065: esecuzione non effettuata, modifica o eliminazione di package
> "CSGD.PCKTABLEMANAGER"
> ORA-06508: PL/SQL: impossibile trovare unità lib chiamata
> ORA-06512: a line 1
>
> (sorry it's a Ora9i on italian version. I hope that the error number will suffice).
> Thanks for the help and happy holydays to all !
Received on Thu Dec 19 2002 - 13:18:45 CST