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?
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.
Query USER_TAB_COLUMNS to get a list of columns and their types.
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!
dbms_metadata.get_ddl to get the same info within PL/SQL and use that to create the clone; although you can also just clone an object through that package without ever seeing the DDL, which handles constraints but not other indexes - they can be done on top easily though. Agree users posting DDL and MCVE would be nice...