| EXECUTE IMMEDIATE 'Create table' [message #432034] |
Fri, 20 November 2009 10:20  |
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
|
Senior Member |
|
|
Good morning.
Please help me. I have a procedure like this
**************************************************************
CREATE OR REPLACE PROCEDURE load_wib
IS
BEGIN
EXECUTE IMMEDIATE 'create table WIB (state varchar2 (2), sale_cnt number(9))';
insert into WIB (state, sale_cnt)
select Tstate, Tsale_cnt
from Tot_sale;
END;
****************************************************************
I would like to create and insert data to table in a same procedure. But I got a error message:
PL/SQL: ORA-00942: table or view does not exist
Would you please to show me a way to create and insert data to table in the same procedure.
Thank you very much.
LN
|
|
|
|
|
|
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432039 is a reply to message #432037] |
Fri, 20 November 2009 10:53  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
No you don't create tables on the fly at all. Never. Absolutely never.
Problem 1: What happens when someone executes the procedure when the table is already there? What if two people want to run the procedure at the same time?
Problem 2: You can't write any procedures of functions using that table, because you can't compile them, because the table is not there. You would need to do ANY operation on that table with dynamic SQL.
Problem 3: A lot of problems that would have resulted in compilation errors now will happen as runtime errors instead.
( There are hundreds of additional problems, too )
The design needs to be changed, so that you don't need to create the table. What is the actual business problem you are trying to solve.
|
|
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432065 is a reply to message #432034] |
Fri, 20 November 2009 17:25 |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Please, have a look at materialized views - it might be what you are looking for.
You should also show (not describe) the purpose of your code.
Keep in mind that you are executing DDL statement which is "CREATE TABLE" - you should avoid DDL in PL/SQL unless you really know what you are doing.
A small note: DDL would commit your transaction.
Just in a case if you don't know what is a transaction: read about it and it would be a good start.
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432279 is a reply to message #432065] |
Mon, 23 November 2009 06:49 |
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
|
Senior Member |
|
|
I would like to say thanks to all of you.
I want to create 2 temporay tables then I will insert the data in these tables to a steady table and will drop the temporary tables right after that.
thanks.
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432282 is a reply to message #432279] |
Mon, 23 November 2009 07:02 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, if you absolutely want to throw yourself off a cliff and break your application forever, you can do that.
But my ethics prevent me from helping you doing it.
There definitely would be a better solution for whatever you final goal is. Creating tables on the fly definitely isn't the solution, it's just a truck-load of new problems.
Depending on what you are actually trying to achieve a Global Temporary Table (that you create ONCE and then just use it in your code) might be what you are looking for.
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432284 is a reply to message #432279] |
Mon, 23 November 2009 07:34 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The best thing to do in your position would be to create proper temporary tables.
Oracle supports the concept of Temporary Tables - the table and it's columns exist permenantly in the database, just like any other table, so procedures can be compiled against it, but data inserted into these tables lasts only until the next commit, or until the end of your session (your choice at table creation time)
Have a look at the create table syntax
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432286 is a reply to message #432034] |
Mon, 23 November 2009 07:47 |
yzsind
Messages: 1
Registered: November 2009
|
Junior Member |
|
|
You can use 'EXECUTE IMMEDIATE' like this:
CREATE OR REPLACE PROCEDURE load_wib
IS
BEGIN
EXECUTE IMMEDIATE 'create table WIB (state varchar2 (2), sale_cnt number(9))';
EXECUTE IMMEDIATE 'insert into WIB (state, sale_cnt) select Tstate, Tsale_cnt from Tot_sale';
END;
|
|
|
|
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432299 is a reply to message #432034] |
Mon, 23 November 2009 08:45 |
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
|
Senior Member |
|
|
Thanks much for the helps for all of you. I appreciated that.
The reason I cannot select direct from tot_sales and insert to the main table. Because the main table is belong to other division. They need me to report the sale in separate tables and they will insert the data to their table. After insert data the temporary tables will be deleted.
I think I will create 2 temporary tables in the database. And I will include at the end of my code a line:
EXECUTE IMMEDIATE ('drop table <table_name> ');
Thanks.
|
|
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432301 is a reply to message #432300] |
Mon, 23 November 2009 08:53 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
At least dont create and drop the tables. Make them once, use them and then delete from the tables before the next load. That way you will not need to use an execute immediate with all the problems it has.
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432308 is a reply to message #432034] |
Mon, 23 November 2009 09:04 |
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
|
Senior Member |
|
|
I am not mean to ignore the very good advices from all of you. I am very appreciated that. But that is the easiest way for me to do.
Because, I tried to create a view but I got this message:
ORA-01031: insufficient privileges
and for the temporary tables. I read in the book, it says ther are two ways
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE my_temp_table (
column1 NUMBER,
column2 NUMBER
) ON COMMIT PRESERVE ROWS;
But I they want me to delete my tables right after they insert all data to their table. And I have never used the temporary tables before. I am a little bit affair of using that. I hope you understand my situation. I am very new to Oracle.
I was using Fortran for a long time and my branch just converted to Oracle.
Thanks.
|
|
|
|
| Re: EXECUTE IMMEDIATE 'Create table' [message #432312 is a reply to message #432308] |
Mon, 23 November 2009 09:18 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
lamnguyen14 wrote on Mon, 23 November 2009 16:04But that is the easiest way for me to do.
Well. If you think that this is the easiest way, then proceed to do it that way.
The actual easiest way would be to create the table once, and then leave it be and just do an insert (possibly with an additional "transaction ID" to separate one run of the transfer from another) and just insert / delete the records.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
