Re: Create table in if-statement

Main error: you can't use ddl-statements (create / alter / drop...) in pl/sql this way. If you are using 8i you can code execute immediate 'create table .....';

Rdbms-Versions before 8i require usage of package dbms_sql like: ....

   v_cu integer;
   v_ret integer
begin

   v_cu := dbms_sql.open_cursor;
   dbms_sql.parse(v_cu,'Create table......',dbms_SQL.V7);    v_Ret := dbms_sql.execute(v_cu);
  dbms_sql.close_cursor(v_cu);
....

Klaus

<poluha_at_my-deja.com> schrieb in im Newsbeitrag: 8kmgcm$t9r$1_at_nnrp1.deja.com...
> Hello!
>
> I'm trying to create a table in an if-statement but keep getting this
> compilation error:
> Line # = 57 Column # = 5 Error Text = PLS-00103: Stötte på
> symbolen "CREATE" när någon av följande förväntades:
>
> begin declare exit for goto if loop mod null pragma raise...
>
> It's in Swedish and it means: "Encountered symbol CREATE when the
> following were expected:..."
>
> What I want to do is to check whether a table already exists. If it
> doesn't I want to create it, otherwise I want to empty it. It goes like
> this:
> SELECT COUNT(*) INTO raderTelber FROM USER_TABLES WHERE
> TABLE_NAME='TELBER';
> IF raderTelber=0 THEN
> CREATE TABLE TELBER (
> MANAD_ID VARCHAR2(6) NOT NULL,
> ...
> );
> CREATE INDEX IX_ELBER ON TELBER(MANAD_ID,LJUSP_ID);
> ELSE
> TRUNCATE TABLE TELBER;
> END IF;
>
> What am I doing wrong here? Is there any other way I could accomplish
> this?
>
> Regards,
> P Poluha, Sweden
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Jul 14 2000 - 00:00:00 CDT