Plsql - Evaluate a dynamic string

Question

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.

Do you really need that? Usually global temporary tables are used for this purpose. — ibre5041
– ibre5041, Commented Jan 19, 2017 at 11:56
Why do you need to create tables dynamically? Typically, tables should be created outside of PL/SQL (e.g. as part of a one-time deployment script) and not constantly dropped and recreated. — Boneist
– Boneist, Commented Jan 19, 2017 at 11:58
the idea is get a list with all the tables names (... for example: tablas := tablesarray('RESUME', 'NOTES', 'ITYPE'); ... ). After that declare all the querys into variables like this: code RESUME_create := 'CREATE TABLE RESUME (R_Resume_Idate NUMBER(3,0) )'; NOTES_create := 'CREATE TABLE RESUME (R_Resume_Idate NUMBER(3,0) )'; ITYPE_create := 'CREATE TABLE RESUME (R_Resume_Idate NUMBER(3,0) )'; code After that, into a loop read tablas, concatenate "_create" and evaluate into a execute immediate — julio
– julio, Commented Jan 19, 2017 at 12:05
@Boneist i need that because i have 50 tables that must created, and i don't want that all sql script be executed one by one (because the guy that prepare that sql script made one .sql for table) — julio
– julio, Commented Jan 19, 2017 at 12:08

Aleksej · Accepted Answer · 2017-01-19 11:54:32Z

1

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;
/

answered Jan 19, 2017 at 11:54

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

julio Over a year ago

the idea is get a list with all the tables names (... for example: tablas := tablesarray('RESUME', 'NOTES', 'ITYPE'); ... ). After that declare all the querys into variables like this: code RESUME_create := 'CREATE TABLE RESUME (R_Resume_Idate NUMBER(3,0) )'; NOTES_create := 'CREATE TABLE RESUME (R_Resume_Idate NUMBER(3,0) )'; ITYPE_create := 'CREATE TABLE RESUME (R_Resume_Idate NUMBER(3,0) )'; code After that, into a loop read tablas, concatenate "_create" and evaluate into a execute immediate

Collectives™ on Stack Overflow

Plsql - Evaluate a dynamic string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related