CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
PianoSong
PianoSong

Reputation: 340

How can I execute 'describe table_name' command from a PL/SQL procedure?

I have the following procedure in Oracle: 

create procedure clone_tables
(current_table_name varchar2, cloned_table_name varchar2);

I have to clone a table, but I receive only it's name. In this case, I think I have to get it's structure, so describe table_name command would be enough. Now, execute immediate or dbms_sql.execute() use only SQL statements.

Is there another way I can do that?

Upvotes: 2

Views: 4863

Answers (3)

user5683823
user5683823

Reputation:

Since you mention DESCRIBE (...), you must be using SQL*Plus - or a graphical program that understands SQL*Plus commands, like Toad or SQL Developer. Unfortunately, you CAN'T execute the DESCRIBE command in SQL or in PL/SQL, because DESCRIBE is a SQL*Plus command, it is NOT a SQL or PL/SQL command.

If you do use SQL Developer or Toad, they have a feature where you bring up a table and it gives you the SQL (not PL/SQL - that is not needed, plain and very fast SQL is all that's needed) to re-create the tables, INCLUDING constraints and comments. Below I am reproducing the output of using this feature in SQL Developer, on a practice SQL table. This only creates the table structure, not its data; you will still have to copy the data over, for example with 

INSERT INTO (new_table) (SELECT * FROM old_table)

The advantage over Alexsej's solution is that the data type will be copied exactly; in Aleksej's solution the columns will not necessarily be EXACTLY the same - [for example, in the old table you may have a VARCHAR2(300) column; the width, 300, will not be copied with his method, and instead the width of the actual data present in the table will be used.] Edit: As Alex Poole pointed out in a comment, what I said here (in square brackets) is INCORRECT, cloning a table with Aleksej's solution WILL preserve column widths and such. (Also, his method will not copy the constraints, like NOT NULL and UNIQUE.)

The method I am recommending still doesn't re-create triggers, but it does re-create constraints and indexes.

Here is an example of what SQL Developer can do for you, with NO EFFORT on your part:

  CREATE TABLE "INTRO"."COURSES" 
   (    "CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE, 
    "DESCRIPTION" VARCHAR2(30 BYTE) NOT NULL ENABLE, 
    "CATEGORY" CHAR(3 BYTE) NOT NULL ENABLE, 
    "DURATION" NUMBER(2,0) NOT NULL ENABLE, 
     CONSTRAINT "COURSES_PK" PRIMARY KEY ("CODE")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
     CONSTRAINT "COURSES_CAT_CHK" CHECK (CATEGORY in ('GEN','BLD','DSG')) ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

   COMMENT ON COLUMN "INTRO"."COURSES"."CODE" IS 'Unique course code';
   COMMENT ON COLUMN "INTRO"."COURSES"."DESCRIPTION" IS 'Course description (title)';
   COMMENT ON COLUMN "INTRO"."COURSES"."CATEGORY" IS 'Course category (GEN, BLD or DSG)';
   COMMENT ON COLUMN "INTRO"."COURSES"."DURATION" IS 'Course duration (in days)';

Good luck!

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

If you need to build a clone of a table, you can use:

create or replace procedure clone_tables (current_table_name varchar2,
                                          cloned_table_name varchar2
                                          ) as
begin
    execute immediate
      'create table ' || cloned_table_name ||
      ' as select * from ' || current_table_name
      ' where 1 = 0 ' ;  /* to avoid copying records */
end;
/

This will build a table with exactly the same columns of the starting one, with no need for scanning all the columns. This way you will not copy the records of the starting table; if you want to copy records, simply remove the WHERE condition. As correctly said by Alex Poole, this will only create the clone table, but will not create any trigger, index, foreign key, ... existing on the cloned table.

Upvotes: 2

Gaius
Gaius

Reputation: 2595

Query USER_TAB_COLUMNS to get a list of columns and their types.

Upvotes: 1

Related Questions