Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: sqlplus pl/sql string handling

    greetings all,
    I have been trying to cretae and populate dynamic tables using
    bith sqlplus and pl/sql. In PL/SQL want to do the following:

    mytablename varchar2(30) := "mytab";
    todaydate date := sysdate;
    :mytablename := mytablename || todaydate;

    create table @mytablename ( {columns enclosed});

    I am not able to do this in either sqlplus or pl/sql. If there is away fo doing it please let me know. Thanks!

    --Guario

  2. #2
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    in sqlplus, something like

    declare
    mytablename varchar2(30) := 'mytab';
    begin
    execute immediate 'create table ' || mytablename
    || '_' || to_char (sysdate, 'yyyy_mm_dd')
    || '(x number)';
    end;
    /

    works in 8i, 9i, perhaps 8.0.

  3. #3
    Join Date
    Oct 2003
    Posts
    5

    Thanks!

    Thanks for the piece of code, it works. However, I am running into
    another problem. My original goal was to be able to tranfer data from
    one table to another using this type of naming. For example

    I tried extending your code by adding " select as * from another_table."
    It doesn't work. Technically you can say that I simply populate the
    table after it has been created. However, this is not a solution because
    I am not able to do the following:

    execute immediate 'insert into' || mytablename || to_char(sysdate, 'yyyy-mm-dd') || select * from static_table';

    Is there any possible way that I might be able to do this. Thanks! Your help is much appreciated.
    --Guario

    Originally posted by alberto.dellera
    in sqlplus, something like

    declare
    mytablename varchar2(30) := 'mytab';
    begin
    execute immediate 'create table ' || mytablename
    || '_' || to_char (sysdate, 'yyyy_mm_dd')
    || '(x number)';
    end;
    /

    works in 8i, 9i, perhaps 8.0.

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    What about a CTAS (Create table as select):

    declare
    mytablename varchar2(30) := 'dual';
    begin
    execute immediate 'create table ' || mytablename
    || '_' || to_char (sysdate, 'yyyy_mm_dd')

    || ' as select * from ' || mytablename;
    end;
    /

    You have the added bonus that with CTAS some constraints (i.e. null constraints) are maintained. And, especially, that the table is populated using 'direct write' mode, which is very fast since Oracle will insert the blocks directly into the datafile, without using the buffer cache (very good for latches too).

    Since it seems that you are making this copy for backup, you may also consider packing your rows to the most, using a pctfree 1:

    declare
    mytablename varchar2(30) := 'dual';
    begin
    execute immediate 'create table ' || mytablename
    || '_' || to_char (sysdate, 'yyyy_mm_dd')
    || ' pctfree 1 '
    || ' as select * from ' || mytablename;
    end;
    /

    And you may also add a storage clause to set pctincrease to zero, the size of the initial and next extent ... you could also make the copy and/or the select in parallel if correct to do so (i.e. there's not much activity on your system).

    By the way: your insert works, you have only made a mistake, try changing the statement by including the column names, and correct some typos:

    execute immediate 'insert into '
    || mytablename || '_' || to_char(sysdate, 'yyyy-mm-dd')
    || ' ( <colums> ) '
    || ' select <colums> from static_table';

    Of course, CTAS is a superior solution. Easier to maintain, and also all-or-nothing: if an error occurs, there will be no table, not an empty table.

    HTH
    Al
    Last edited by alberto.dellera; 10-11-03 at 15:25.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •