Duplicate table by entering columns

[NaPraia]

Next staff, I am engaged in a task of making a version and version control and I am
wanting to create a package in the bank (Oracle) that generates me a new table with some more fields.
I am thinking of passing the table name as a parameter.

ex.
I have the person table with the ID and name

Select all

create table Pessoa ( 
id number(5), 
nome varchar2(50))

fields, let's say that I used this command to create the table.
Now I would like to generate the following command (always include the 4fields below)

Select all

create table Pessoa_versao ( 
id number(5), 
nome varchar2(50), 
id_versao (5), --PK 
data_ativacao date, 
data_inativacao date, 
versao_no number(5))

I was able to be clear what I posted?
I will always get all the fields in the table that was passed by parameter and include these 4 fields.

Tips ???

[Trevisolli]

Brother,

has how to do this.
You can pass information by parameter, and within your procedure (package), use:

Select all

 
EXECUTE IMMEDIATE ('Create table...'); 

Give a sought here in the forum itself, by [ B] Immediate [/b], which will find interesting examples, like this:
http://en.glufke.net/oracle/viewtopic.p ... =immediate
Anything, send us.

[NaPraia]

As for the immediate is quiet
my problem is to generate the CREATE TABLE.
Know what fields you have in the table that was passed by parameter and what types are.

has the 'desc' command, but I do not know why it does not work on PL / SQL Developer.
In SQL Plus works no problem.

Suddenly if I can send the result of the

Select all

desc tabela 

to a variable where I could use this data to mount the Create.

Thanks for the tip.

[Trevisolli]

Brother,

If this information already exists in a bank table, you can access what you want, searching in the Oracle data dictionary, through the views below:

Select all

USER_TABLES 
ALL_TABLES 
DBA_TABLES

There are others also that they can help you.
Take a look at this code below and, see if it's what you need?

Select all

 
SELECT * 
  FROM all_tab_columns 
 WHERE table_name = 'EMP'; 

Any questions, send us.

[NaPraia]

Thanks, it was these the information I needed.
Now it was easy, just make a cursor.

Thanks

[NaPraia]

Problem solved
Thanks

I came here to post the code as the function was.

Select all

FUNCTION FNC_CREATE_TABLE_VERSAO (pi_tabela in varchar2) return varchar2 is 
 
  pi_create varchar(4000); 
  cursor c_create_table is 
    SELECT column_name col, data_type tip, 
           case when data_type <> 'DATE' then '('||data_length||')' end tam, 
           case when nullable = 'N' then 'NOT NULL' else 'NULL' end nul 
      FROM all_tab_columns 
     WHERE upper(table_name) = upper(pi_tabela); --'FML_DOCUMENTO'; 
    r_create_table c_create_table%ROWTYPE; 
 
  begin 
    pi_create := 'CREATE TABLE '||upper(pi_tabela)||'_VERSAO( '; 
    pi_create := pi_create||'OID_VERSAO NUMBER(5) NOT NULL, '; 
    OPEN c_create_table; 
    LOOP 
      FETCH c_create_table INTO r_create_table; 
      EXIT WHEN c_create_table%NOTFOUND; 
      pi_create := pi_create||r_create_table.col||' '||r_create_table.tip||' '||r_create_table.tam||' '||r_create_table.nul||', '; 
    END LOOP; 
    CLOSE c_create_table; 
 
    pi_create := pi_create||'DAT_ATIVACAO DATE NOT NULL, '; 
    pi_create := pi_create||'DAT_INATIVACAO DATE NULL, '; 
    pi_create := pi_create||'VERSAO_NO NUMBER(5) DEFAULT 0 NOT NULL, '; 
    pi_create := pi_create||'PRIMARY KEY (OID_VERSAO));'; 
 
    RETURN pi_create; 
 
END FNC_CREATE_TABLE_VERSAO;