| Learning to use Dynamic SQL [message #7135] |
Fri, 23 May 2003 00:30  |
David Buddrige
Messages: 1
Registered: May 2003
|
Junior Member |
|
|
Hi all,
I am experiementing with Dynamic SQL. I am wanting to write a procedure which will drop and then re-create any table specified in its single parameter which is a VARCHAR2 that is the tablename; ie. the declaration will be along the lines of:
PROCEDURE recreate_table( tableName VARCHAR2(100) );
In order to re-create the table, I need to determine what fields it has.
I am writing a SELECT query that queries the user_tables; this gives me the name of the table and a bunch of other information, but not the actual field-names and datatypes. I am guessing that there is a table of some name that has a one-to-many relationship with user_tables that describes each of the fields in any given table. Do you know what that table would be called? (I have tried user_fields but that table does not exist). Is there somewhere that I can get a definitive description of the various data_dictionary objects?
The other method that I have tried was to use the command 'DESCRIBE
'. While using this tecnique it is possible to get a good description of the fields of any given table, I am not sure how to get that information into a cursor which I can then loop through in order to generate the field descriptions in the TABLE CREATE string.
I have pasted in the text of the function that I have written so far at the end of this email.
thanks
David Buddrige
CREATE OR REPLACE PROCEDURE recreate_table
( table_Name VARCHAR2(100) )
IS
sql_string VARCHAR(255);
v_cursor NUMBER;
v_NumRows INTEGER;
describe_Rows INTEGER;
describe_String VARCHAR(255);
describe_cursor_num NUMBER;
TYPE description_Cursor IS REF CURSOR RETURN user_objects%ROWTYPE;
table_Description VARCHAR(255);
the_Name VARCHAR(20);
the_Type VARCHAR(20);
is_First_Type BOOLEAN;
BEGIN
-- The first thing to do is to find out the structure of the table.
-- To do this, you need to create a cursor onto the table returned
-- either by the statement 'DESCRIBE tablename' or a cursor into a
-- select statement that selects the appropriate user_* data-dictionary
-- tables that describe the structure of the table provided.
describe_Cursor_num := DBMS_SQL.OPEN_CURSOR;
describe_String := 'DESCRIBE ' || table_Name || ';';
DBMS_SQL.PARSE(describe_Cursor_num, describe_String, DBMS_SQL.v7);
describe_Rows := DBMS_SQL.EXECUTE(describe_Cursor_num);
-- OK, at this point we should have obtained a table describing the table
-- but we just need to create a cursor that will loop through it one row
-- at a time. We should use this cursor to build up the text of the
-- string 'table_description' which will later be used in the CREATE TABLE
-- command.
-- Don't know how to get results of EXECUTE into cursor description_Cursor...
OPEN description_Cursor;
FETCH description_Cursor INTO the_Name, the_Type;
is_First_Type = TRUE;
WHILE description_Cursor%FOUND LOOP
IF is_First_Type = TRUE THEN
is_First_Type = FALSE;
ELSE
table_Description := ', ';
END IF
table_Description := table_Description || the_Name || ' ' || the_Type;
FETCH description_Cursor INTO the_Name, the_Type;
END LOOP;
-- Now Drop the table. The exception is trapped incase the
-- table has not yet been created in the first place.
v_cursor := DBMS_SQL.OPEN_CURSOR;
sql_string := 'DROP TABLE ' || table_Name || ';';
BEGIN
DBMS_SQL.PARSE(v_Cursor, sql_string, DBMS_SQL.v7);
v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
-- Now re-create the table.
sql_string := 'CREATE TABLE ' || table_Name || table_Description
END recreate_table;
|
|
|
|
| Re: Learning to use Dynamic SQL [message #7138 is a reply to message #7135] |
Fri, 23 May 2003 01:49  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
USER_TABLES can be used to gather table info.
USER_TAB_COLUMNS can be used to gather column info.
As for the mechanism of Dynamic SQL. Look at the Oracle Online Documentation:Tahiti.Oracle.Com. Select your Oracle version and search for "Dynamic SQL".
As of Oracle 8i there's something called Native Dynamic SQL (NDS). With NDS you issue commands like:
EXECUTE IMMEDIATE 'your_query_string';
Besides, for the recreation of a table, try the following:
- Create a new table with the 'CREATE TABLE AS SELECT' statement.
- Drop the original table
- Rename the new table to the original table.
And how about Triggers? Foreign Keys? Indexes?
MHE
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
