Re: Create table in if-statement

To create a table in a stored procedure, you can use dbms_sql:

declare
  v_crs BINARY_INTEGER;
begin
  v_crs := dbms_sql.open_cursor;
  dbms_sql.parse(v_crs, 'create table telber...', dbms_sql.V7);   v_err := dbms_sql.execute(v_crs);
  dbms_sql.close_cursor(v_crs);
end;

To create the index, you can use the same method (it might be better to create a procedure which expects a ddl statement as a parameter).

Marc

poluha_at_my-deja.com wrote in message <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