-
CREATE Table in a stored procedure
Hi,
Excuse me but my English is quite bad, I wil try to explain my problem.
I want to create a table in a stored procedure, apparantly it is impossible :/
Anybody would have a solution for me ?
Thanks
juvamine
-
Yes, you can create a table through a procedure. Here is some quick output to show.
1. describe that the table does not exist.
2. create a procedure
3. execute the procedure
4. describe that the table was created.
25-FEB-05 : SYS@k10gutf8 AS SYSDBA > desc testtable
ERROR:
ORA-04043: object testtable does not exist
25-FEB-05 : SYS@k10gutf8 AS SYSDBA > create PROCEDURE createta
2
3 BEGIN
4 EXECUTE IMMEDIATE
5 'CREATE TABLE testtable '||
6 ' (mycolumn NUMBER, '||
7 ' CONSTRAINT pk_testtable PRIMARY KEY (mycolumn
8
9 END createtable;
10 /
Procedure created.
25-FEB-05 : SYS@k10gutf8 AS SYSDBA > exec createtable
PL/SQL procedure successfully completed.
25-FEB-05 : SYS@k10gutf8 AS SYSDBA > desc testtable
Name Null? Type
----------------------------------------- -------- ----------
MYCOLUMN NOT NULL NUMBER
-
Thanks a lot !!!
execute command, is able to execute all sql query ?
EXECUTE COMMAND 'my sql query';
yes ?
thx again
juvamine
-
The "execute immediate" call is able to execute a string of SQL with a maximum of 2000 chars if i remember correctly. When you use ;'s in the statement its considered by the engine to be a block of PL/SQL instead of a mere query so it then needs to have a begin end; stucture.
-
i have used EXECUTE IMMEIDATE command with a LONG data type to create not only tables but procedures, packages, and package bodies. this data type stores up to 2GB and the last package i created was over 13,000 characters without spaces and over 17,000 characters with spaces.
so i think it just depends on the data type being used. then again i have concatenated many LONG data types together without any problems.
-
Hmmm... you could be right then. Always use varchar2 to store the SQL string and most of the time the string stays small. I use it a lot in normal procedures with the conveniance of binding the vars. I used to concatenate everything and got sometimes an error saying SQL string to large. But then again could be just me selecting a to small datatype.
-
maybe your 2 versions of Oracle ar different...that explain the difference in the weight of the varchar.
In my case, it's OK )
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|