Create a table using plsql

Question

Issues: While executing this query it throws following error ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 01422. 00000 - "exact fetch returns more than requested number of row

Other Issue is I am trying to get all primary key columns at one place but it is creating a separate sql line for the other column in the same primary key column.

DECLARE 
  v_create VARCHAR2(10000);
  v_alter          varchar2(10000);
  v_index          varchar2(10000);
BEGIN
  SELECT 'create table owner_new.' 
                ||table_name 
                ||' as select * from ' 
                ||owner 
                ||'.' 
                ||table_name 
                ||';', 
         'alter table owner_new.' 
                ||table_name 
                ||' add (Time timestamp not null default systimestamp, Action varchar2(10) not null default ''I'');', 
         'create index ' 
                ||cc.owner 
                ||'.' 
                ||cc.constraint_name 
                ||' on ' 
                ||cc.owner 
                ||'.' 
                ||cc.table_name 
                ||'(' 
                ||cc.column_name 
                ||' ASC ' 
                ||')' 
                ||';' 
  INTO   v_create, 
         v_alter, 
         v_index 
  FROM   all_tables, 
         all_cons_columns cc, 
         all_constraints pk 
  WHERE  cc.owner = pk.owner 
  AND    cc.constraint_name = pk.constraint_name 
  AND    pk.constraint_type = 'P' 
  AND    cc.owner = owner 
  AND    owner IN ('Old_owner', 
                   ) 
  EXECUTE immediate 
    v_create; 
  EXECUTE immediate 
    v_alter; 
  EXECUTE immediate 
    v_index; 
END;

Don'try to invent warm watter :). Use dbms_metadata.get_ddl http://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable — Mottor, Jul 25 '16 at 15:44
A `SELECT INTO` query must return exactly one row. Yours returns many rows. — GriffeyDog, Jul 25 '16 at 15:54
@Mottor: `DBMS_METADATA.GET_DDL` won't produce `CREATE TABLE xxxx AS SELECT * FROM ...`. — Bob Jarvis - Слава Україні, Jul 25 '16 at 16:35
@BobJarvis Why you need select *. The get_ddl will produce valid DDL with the necessary syntax, which can be changed after that. What want the OP do? — Mottor, Jul 25 '16 at 18:15
@Mottor: look at the statement OP's code produces. The code as shown produces a `CREATE TABLE AS SELECT *...` statement, so I don't believe the a GET_DDL call is going to do what he needs to have done. — Bob Jarvis - Слава Україні, Jul 25 '16 at 18:40
@BobJarvis And I do not believe that his statement will make exact copy of the table with all indexes, triggers and constraints. What is missing on get_ddl, can you easy make with insert into select * — Mottor, Jul 25 '16 at 18:58

score 1 · Answer 1 · answered Jul 25 '16 at 15:42

1

You are trying to do a select that returns multiple rows into a 3 sqls variables. Its not going to work.

Create a cursors for this query and execute immediate in loop

answered Jul 25 '16 at 15:42

6ton

4,174
1
22
37

Create a table using plsql

1 Answers1