Home » SQL & PL/SQL » SQL & PL/SQL » dynamically create table
dynamically create table [message #314053] |
Tue, 15 April 2008 13:14  |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
One of our requirement is to create a external table dynamically. For this I have tried to use varchar2 and LONG. But he EXECUTE IMMEDIATE gives error. The DDL script prepared is larger than the limit. Is it possible to create table dynamically whose script is very big.
-Deb
|
|
|
Re: dynamically create table [message #314054 is a reply to message #314053] |
Tue, 15 April 2008 13:16   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
One of our requirement is to read the forum guidelines. If the forum guidelines are not followed then there is a memory leak in my system and my response (*^%&**(&( (gets garbled).
Regards
Raj
|
|
|
|
problem creating table dynamically [message #314063 is a reply to message #314053] |
Tue, 15 April 2008 13:41   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
while creating a external table dynamically, the DDL being generated being vary large giving error.
We are using EXECUTE IMEDIATE OR DBMS_SQL for dynamic query.
I have tried using LONG and same issue.
I tried CLOB, it looks like CLOB is not supported by both.
Is there a way out?
-Deb
|
|
|
|
Re: dynamically create table [message #314066 is a reply to message #314053] |
Tue, 15 April 2008 14:04   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
How can creating an external table dynamically ever be a requirement? I can only think of very very few valid business cases for this.
Far more probable is that your requirement is something else and you THINK you can only do this by creating an external table dynamically.
|
|
|
Re: problem creating table dynamically [message #314074 is a reply to message #314064] |
Tue, 15 April 2008 14:48   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
Can I get a small example.
My requrement is:
My DDL command(Its an external table creation command with more tham 900 columns) is very big.
So I want to pass the big string in small parts through an array to DBMS_SQl.parse.
I want to figure out how I can do it.
I just want the syntax/examples to pass an array of varchar2 to dbms_sql.parse.
-Deb
|
|
|
Re: dynamically create table [message #314077 is a reply to message #314066] |
Tue, 15 April 2008 14:54   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
Its like creating Generic way of importing flat file data from various sources, based on meta data. So based on the meta data the process need to create external table for bulk loading in to staging area dynamically.
However, one of the source file is having large no of columns failing to create table.
-Debesh
|
|
|
|
Re: dynamically create table [message #314093 is a reply to message #314081] |
Tue, 15 April 2008 15:26   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
I am sorry if I am not clear in stating the issue.
The application is using some metadata (user defined) tables to identify, what would be the columns, their type and size, file location etc.
Then it generates a DDL statement based on the meta info in a varchar string. It goes fine with smaller tables.
However, when the count of column is very largre (like 500 to 900), it fails as the size of the string crosses the 32760 limit for Varchar or LONG in the PLSQL procedure.
Currently, its using EXECUTE IMMEDIATE to create the table. I found the same issue if I use dbms_sql.parse. as the INput type is sill the VARCHAR2. I tried to pass a LONG too. But I faced the same issue. Imean when the string size goows above the limit, it gives raises exception.
I thought if I can pass a CLOB. But with 10g, I there is no support for it.
I am not sure how I can resolve it.
-Debesh
|
|
|
|
Re: dynamically create table [message #314106 is a reply to message #314053] |
Tue, 15 April 2008 15:44   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
I am not sure of the syntax for DBMS_SQL with an array for a DDL statement.
Can I get an example which is creating a table(may not be external table) using an array. Just structure of the syntax should be ok for me.
-Debesh
|
|
|
|
Re: dynamically create table [message #314136 is a reply to message #314106] |
Tue, 15 April 2008 20:02   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I created a execute_immediate_clob procedure for you below. You should be able to use it as:
execute_immediate_clob (your_clob);
instead of:
execute immediate your_clob;
The documentation says:
"To parse SQL statements larger than 32 KB, DBMS_SQL makes use of PL/SQL tables to pass a table of strings to the PARSE procedure. These strings are concatenated and then passed on to the Oracle server.
You can declare a local variable as the VARCHAR2S table-item type, and then use the PARSE procedure to parse a large SQL statement as VARCHAR2S.
The definition of the VARCHAR2S datatype is:
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;"
So, what my procedure does is accept a clob parameter, load it into a collection of 255 character strings, then pass it to dbms_sql.parse, and execute it. The fifth parameter to DBMS_SQL.PARSE is set to FALSE, so that a line feed is not added at the end of each line, so it does not matter if the code is broken in the middle of words.
-- procedure:
CREATE OR REPLACE PROCEDURE execute_immediate_clob
(p_sql_clob IN CLOB)
AS
v_sql_clob CLOB := p_sql_clob;
v_sql_255 DBMS_SQL.VARCHAR2S;
v_subscript INTEGER := 1;
v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
v_rows INTEGER;
BEGIN
WHILE v_sql_clob IS NOT NULL LOOP
v_sql_255 (v_subscript) := DBMS_LOB.SUBSTR (v_sql_clob, 255, 1);
v_subscript := v_subscript + 1;
v_sql_clob := DBMS_LOB.SUBSTR (v_sql_clob, DBMS_LOB.GETLENGTH (v_sql_clob) - 255, 256);
END LOOP;
DBMS_SQL.PARSE (v_cur, v_sql_255, v_sql_255.FIRST, v_sql_255.LAST, FALSE, DBMS_SQL.NATIVE);
v_rows := DBMS_SQL.EXECUTE (v_cur);
DBMS_SQL.CLOSE_CURSOR (v_cur);
END execute_immediate_clob;
/
-- demo using a short clob, (but it should work with any clob):
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE execute_immediate_clob
2 (p_sql_clob IN CLOB)
3 AS
4 v_sql_clob CLOB := p_sql_clob;
5 v_sql_255 DBMS_SQL.VARCHAR2S;
6 v_subscript INTEGER := 1;
7 v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
8 v_rows INTEGER;
9 BEGIN
10 WHILE v_sql_clob IS NOT NULL LOOP
11 v_sql_255 (v_subscript) := DBMS_LOB.SUBSTR (v_sql_clob, 255, 1);
12 v_subscript := v_subscript + 1;
13 v_sql_clob := DBMS_LOB.SUBSTR (v_sql_clob, DBMS_LOB.GETLENGTH (v_sql_clob) - 255, 256);
14 END LOOP;
15 DBMS_SQL.PARSE (v_cur, v_sql_255, v_sql_255.FIRST, v_sql_255.LAST, FALSE, DBMS_SQL.NATIVE);
16 v_rows := DBMS_SQL.EXECUTE (v_cur);
17 DBMS_SQL.CLOSE_CURSOR (v_cur);
18 END execute_immediate_clob;
19 /
Procedure created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> DESC emp2
ERROR:
ORA-04043: object emp2 does not exist
SCOTT@orcl_11g> DECLARE
2 v_sql_clob CLOB;
3 BEGIN
4 v_sql_clob :=
5 'CREATE TABLE emp2
6 (empno NUMBER(4,0),
7 ename VARCHAR2(10),
8 job VARCHAR2(9),
9 mgr NUMBER(4,0),
10 hitedate DATE,
11 sal NUMBER(7,2),
12 comm NUMBER(7,2),
13 deptno NUMBER(2,0),
14 CONSTRAINT pk_emp2 PRIMARY KEY (empno))';
15 execute_immediate_clob (v_sql_clob);
16 END;
17 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> DESC emp2
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HITEDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@orcl_11g>
|
|
|
|
Re: dynamically create table [message #314600 is a reply to message #314136] |
Thu, 17 April 2008 04:50   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
Thanks for the code. It looks great.
However, when I tested with large no of columns, I am facing issue.
The issue is when the create table command size growing above +30K. When I kept the column names small for the table and size was below 30 K, table got created, but when it went past 30K, it gave the following error. I have attached the error message.
Another thing I noted is your code is in 11g, while I want this on 10g. Codewise, it does not matter, but I think the size does matter when we think of size of the command to be parsed.
Thanks,
Debesh
|
|
|
|
Re: dynamically create table [message #314661 is a reply to message #314610] |
Thu, 17 April 2008 07:31   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
Here it is.
The errors are like
ORA-00900: invalid SQL statement
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1602
ORA-06512: at "SYS.DBMS_SYS_SQL", line 33
ORA-06512: at EXECUTE_IMMEDIATE_CLOB", line 15
ORA-06512: at line 1105
This error comes at
DBMS_SQL.PARSE (v_cur, v_sql_255, v_sql_255.FIRST, v_sql_255.LAST, FALSE, DBMS_SQL.NATIVE);of following code.
CREATE OR REPLACE PROCEDURE execute_immediate_clob
(p_sql_clob IN CLOB)
AS
v_sql_clob CLOB := p_sql_clob;
v_sql_255 DBMS_SQL.VARCHAR2S;
v_subscript INTEGER := 1;
v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
v_rows INTEGER;
BEGIN
WHILE v_sql_clob IS NOT NULL LOOP
v_sql_255 (v_subscript) := DBMS_LOB.SUBSTR (v_sql_clob, 255, 1);
v_subscript := v_subscript + 1;
v_sql_clob := DBMS_LOB.SUBSTR (v_sql_clob, DBMS_LOB.GETLENGTH (v_sql_clob) - 255, 256);
END LOOP;
DBMS_SQL.PARSE (v_cur, v_sql_255, v_sql_255.FIRST, v_sql_255.LAST, FALSE, DBMS_SQL.NATIVE);
v_rows := DBMS_SQL.EXECUTE (v_cur);
DBMS_SQL.CLOSE_CURSOR (v_cur);
END execute_immediate_clob;
|
|
|
|
Re: dynamically create table [message #314686 is a reply to message #314672] |
Thu, 17 April 2008 08:36   |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
The statement is correct, I checked with small column names first and then I increased the names by few character to increase the size of the command.
like zaqxswcdevfffdd21 varchar2(10),
it failed.
Thanks,
Debesh
|
|
|
|
|
Re: dynamically create table [message #314702 is a reply to message #314661] |
Thu, 17 April 2008 09:24   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
My assumption you are hitting this and hence you are getting the error you have described. I managed to tweak the code which @Barbara has given and successfully managed to create a table with around 813 columns.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349
Quote: |
Return Values
Table 52-53 SUBSTR Function Return Values
Return Description
RAW
Function overloading that has a BLOB or BFILE in parameter.
VARCHAR2
CLOB version.
NULL
Either:
- any input parameter is NULL
- amount < 1
- amount > 32767
- offset < 1
- offset > LOBMAXSIZE
|
My output is as follows :
declare
v_sql_clob clob;
begin
v_sql_clob := 'CREATE TABLE tmp_test_tab
(
...
);
execute_immediate_clob(v_sql_clob);
end;
Length of the clob is : 35802
SQL> select count(*) from user_tab_columns where table_name = 'TMP_TEST_TAB';
COUNT(*)
----------
813
I leave that interesting exercise for you to solve it.
Regards
Raj
|
|
|
Re: dynamically create table [message #314709 is a reply to message #314700] |
Thu, 17 April 2008 09:47   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
It is working for me. As I said earlier you have to tweak it a little and it will work.
DECLARE
v_sql_clob CLOB;
BEGIN
v_sql_clob :=
'
Create Table test5(zaqxswcdevfffda1 varchar2(10),
zaqxswcdevfffda2 varchar2(10),
zaqxswcdevfffda3 varchar2(10),
..
zaqxswcdevfffda993 varchar2(10),
zaqxswcdevfffda994 varchar2(10),
zaqxswcdevfffda995 varchar2(10)
)
');
execute_immediate_clob (v_sql_clob);
end;
/
PL/SQL procedure successfully completed.
Length of the clob is : 36673
1* select count(*) from user_tab_columns where table_name = 'TEST5'
SQL> /
COUNT(*)
----------
991
Regards
Raj
|
|
|
|
Re: dynamically create table [message #314722 is a reply to message #314718] |
Thu, 17 April 2008 10:50   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
No I am not. How do you know that you are not having any issue with substr? I tested it and found out there is an issue, if the size of the clob is more than 32767+255 characters. Why don't you instrument/add debug statements to the code @barbara has given and find it out yourself. My oracle version is
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Regards
Raj
[Updated on: Thu, 17 April 2008 10:51] Report message to a moderator
|
|
|
Re: dynamically create table [message #314805 is a reply to message #314722] |
Thu, 17 April 2008 16:44   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Raj is so right. I know that we can now use substr on lobs like dbms_lob.substr, for which the parameters are reversed, but in various tests it was found that dbms_lob.substr was faster. However, I did not realize the restriction. Since it was unable to do dbms_lob.substr on the large clob, it only loaded the first element into the v_sql_255, so when it tried to parse it, it was just parsing the first line, resulting in an error, since the create table statement was incomplete. So, I have provided the corrected code and demo below. Also, once you upgrade to 11g, the dbms_sql.parse overloading will allow you to just pass a clob to dbms_sql.parse directly, as you would a varchar2, and you won't have to hassle with all of this. I should have tested this more thoroughly the first time.
-- procedure:
CREATE OR REPLACE PROCEDURE execute_immediate_clob
(p_sql_clob IN CLOB)
AS
v_sql_clob CLOB := p_sql_clob;
v_sql_255 DBMS_SQL.VARCHAR2S;
v_subscript INTEGER := 0;
v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
v_rows INTEGER;
BEGIN
WHILE DBMS_LOB.GETLENGTH (v_sql_clob) >= 256 LOOP
v_subscript := v_subscript + 1;
v_sql_255 (v_subscript) := DBMS_LOB.SUBSTR (v_sql_clob, 255, 1);
v_sql_clob := SUBSTR (v_sql_clob, 256);
END LOOP;
v_subscript := v_subscript + 1;
v_sql_255 (v_subscript) := v_sql_clob;
DBMS_SQL.PARSE (v_cur, v_sql_255, v_sql_255.FIRST, v_sql_255.LAST, FALSE, DBMS_SQL.NATIVE);
v_rows := DBMS_SQL.EXECUTE (v_cur);
DBMS_SQL.CLOSE_CURSOR (v_cur);
END execute_immediate_clob;
/
-- demo:
SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE execute_immediate_clob
2 (p_sql_clob IN CLOB)
3 AS
4 v_sql_clob CLOB := p_sql_clob;
5 v_sql_255 DBMS_SQL.VARCHAR2S;
6 v_subscript INTEGER := 0;
7 v_cur INTEGER := DBMS_SQL.OPEN_CURSOR;
8 v_rows INTEGER;
9 BEGIN
10 WHILE DBMS_LOB.GETLENGTH (v_sql_clob) >= 256 LOOP
11 v_subscript := v_subscript + 1;
12 v_sql_255 (v_subscript) := DBMS_LOB.SUBSTR (v_sql_clob, 255, 1);
13 v_sql_clob := SUBSTR (v_sql_clob, 256);
14 END LOOP;
15 v_subscript := v_subscript + 1;
16 v_sql_255 (v_subscript) := v_sql_clob;
17 DBMS_SQL.PARSE (v_cur, v_sql_255, v_sql_255.FIRST, v_sql_255.LAST, FALSE, DBMS_SQL.NATIVE);
18 v_rows := DBMS_SQL.EXECUTE (v_cur);
19 DBMS_SQL.CLOSE_CURSOR (v_cur);
20 END execute_immediate_clob;
21 /
Procedure created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT COUNT (*) FROM user_tab_columns WHERE table_name = 'TEST5'
2 /
COUNT(*)
----------
0
SCOTT@orcl_11g> DECLARE
2 v_sql_clob CLOB;
3 BEGIN
4 v_sql_clob := 'CREATE TABLE test5 (zaqxswcdevfffdaBABS1 VARCHAR2(10)';
5 FOR i IN 2 .. 1000 LOOP
6 DBMS_LOB.APPEND (v_sql_clob, ',zaqxswcdevfffdaBABS' || i || ' VARCHAR2(10)');
7 END LOOP;
8 DBMS_LOB.APPEND (v_sql_clob, ')');
9 DBMS_OUTPUT.PUT_LINE (DBMS_LOB.GETLENGTH (v_sql_clob));
10 execute_immediate_clob (v_sql_clob);
11 END;
12 /
35913
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT COUNT (*) FROM user_tab_columns WHERE table_name = 'TEST5'
2 /
COUNT(*)
----------
1000
SCOTT@orcl_11g>
|
|
|
Re: dynamically create table [message #314840 is a reply to message #314805] |
Fri, 18 April 2008 00:21  |
sahaydebesh
Messages: 12 Registered: April 2008 Location: HBD
|
Junior Member |
|
|
Thanks Barbara for all help which is great. Thanks also to Raj.
Last night I arrived at this solution too. But that never belittle your help. You are rock.
The issue is closed now.
Thanks,
Debesh
|
|
|
Goto Forum:
Current Time: Wed Feb 12 01:46:01 CST 2025
|