CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sqlddl
julio
julio

Reputation: 3

Plsql - Evaluate a dynamic string

I need to build a process that creates tables dynamically.

I have this:

declare
type array is table of varchar2(30) index by binary_integer;
  a array;
  expression varchar2(2000);
  RESUME_create LONG;  

procedure createTables ( texto in VARCHAR2 ) is 
    begin 
      dbms_output.put_line('the parameter is: ' || texto);
      expression := 'begin ' || texto || '; end;';
      dbms_output.put_line(expression);
      execute immediate expression;    
    end;

RESUME_create   := 'CREATE TABLE RESUME (
     R_Resume_date          DATE         DEFAULT SYSDATE NOT NULL ,
     R_Resume_source        CHAR(3)      DEFAULT ''001'' NOT NULL ,
     R_Resume_channel       CHAR(3)      DEFAULT ''001'' NOT NULL )';  

  createTables('RESUME_create');
end;
/

So this is just an example.

So imagine that I need to declare multiples CREATE TABLEs and call the createTable into a loop passing multiples string that the function has to evaluate and execute.

Upvotes: 0

Views: 1813

Answers (1)

Aleksej
Aleksej

Reputation: 22969

If I un understand well, you need to run a set of DDL statements stored in a collection. If so, you can use something like:

declare
    type   tArray is table of varchar2(1000) index by pls_integer;
    vArray tArray ;
begin
    vArray(1) := 'create table firstTab  ( a number, b number)';
    vArray(2) := 'create table secondTab ( c number, d varchar2(10))';
    --
    for i in vArray.first .. vArray.last loop
        execute immediate vArray(i);
    end loop;
end;
/

Upvotes: 1

Related Questions