| dba won't allow dynamic sql DDL - opinion? [message #40006] |
Tue, 03 September 2002 07:18  |
Jeff Adrian
Messages: 2
Registered: September 2002
|
Junior Member |
|
|
Hi,
We wrote a PL/SQL app that drops/creates a table as a scratch pad dynamically at run time. Depending on variables that exist at runtime, number of columns will vary. Column names are a function of number of columns, i.e. col_1, col_2, col_3, ..., col_n.
DBA's object, want to use predefined global temporary tables with maximum number of columns defined. They claim they want to avoid tablespace being consumed by processes that create but never drop tables but I suspect they just don't want anyone but themselves able to create a table in production.
I would like to know if anyone uses dynamic sql for DDL in production environments, as well as opinions for and against.
Thanks,
Jeff Adrian
|
|
|
|
|
|
| Re: dba won't allow dynamic sql DDL - opinion? [message #41183 is a reply to message #40006] |
Fri, 06 December 2002 02:06  |
Gaurav Mittal
Messages: 1
Registered: December 2002
|
Junior Member |
|
|
Hi,
I am trying to create tables dynamically. but i am not able create them as the create table statement does not consider variable containing table name.
I am writing what exactly i am trying to do
v_tablename:='TCT'||substr('TABLE NAME',5);
EXECUTE IMMEDIATE 'CREATE TABLE :v_tablename as select * from TABLE NAME';
I would like somebody to help me out this problem.
Thanks & Regards
Gaurav
|
|
|
|
| Re: dba won't allow dynamic sql DDL - opinion? [message #41289 is a reply to message #41183] |
Sat, 14 December 2002 02:54 |
 |
Barbara Boehmer
Messages: 9094
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following is a response to your e-mail, which stated:
"Hi barbara
thanx for an immediate response. Now i am able to create the tables. but now i have a problem to create the table1 and insert rows into it from the table2 I am creating table1. I explain u the problem
I have a table say TableA in which there r 2 fields: Tab_name and Tab_col.
Tab_name is name of the table and tab_col(Date Type) is name of the field of that table which r stored into the TableA.
I want to create tables with TCT as prefix to those tables and insert data into them on the basis of date like.
This is what i am exactly trying to do:
declare
v_tab varchar2(20):='TCD_TAB_TAB;
v_tab1 varchar2(30);
v_col varchar2(40):='TAB_TAB_DATE';
v_date date:='20/08/2002';
BEGIN
v_tab1:='TCT_T'||substr(v_tab,5);
EXECUTE IMMEDIATE 'CREATE table '||v_tab1||' as
select * from '||v_tab;
EXECUTE IMMEDIATE 'Insert into '||v_tab1||' select'||' * '||'from '||v_tab||' WHERE '||v_col||' between '||v_date||' and '||v_date;
END;
Please help me out its very urgent.
Thanks
Gaurav"
Response:
Gauarav,
Please try this and see if it is what you want:
DECLARE
v_date1 DATE := TO_DATE ('20/08/2002', 'DD/MM/YYYY');
v_date2 DATE := TO_DATE ('20/09/2002', 'DD/MM/YYYY');
BEGIN
FOR rec IN
(SELECT tab_name, tab_col
FROM TableA)
LOOP
EXECUTE IMMEDIATE
'CREATE TABLE TCT_' || SUBSTR (rec.tab_name, 5)
|| ' AS SELECT * FROM ' || rec.tab_name
|| ' WHERE 1 = 2';
EXECUTE IMMEDIATE
'INSERT INTO TCT_' || SUBSTR (rec.tab_name, 5)
|| ' SELECT * FROM ' || rec.tab_name
|| ' WHERE ' || rec.tab_col || ' BETWEEN :v_date1 AND :v_date2'
USING v_date1, v_date2;
END LOOP;
END;
/
In the future, please post your questions on the forums, rather than via e-mail, which will allow others to contribute and learn, and probably result in a quicker response. I noticed that you sent your e-mail almost a week ago and stated that it was urgent, but I did not get to reading my e-mails until today. Had you posted the question on the forum, I am sure that someone else could have provided an accurate response sooner.
Thanks,
Barbara
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
