Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE 'Create table'
EXECUTE IMMEDIATE 'Create table' [message #432034] Fri, 20 November 2009 10:20 Go to next message
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 #432036 is a reply to message #432034] Fri, 20 November 2009 10:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Don't do it. It's one of the worst design mistakes to create and drop database objects on the fly.
Re: EXECUTE IMMEDIATE 'Create table' [message #432037 is a reply to message #432034] Fri, 20 November 2009 10:42 Go to previous messageGo to next message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks.
So I have to create table in a separate procedure and then do insert data in another procedure? Thanks.
Re: EXECUTE IMMEDIATE 'Create table' [message #432038 is a reply to message #432037] Fri, 20 November 2009 10:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, you should not create any tables at runtime.
Your tables are a steady part of your application, just as your procedures & packages are.
Re: EXECUTE IMMEDIATE 'Create table' [message #432039 is a reply to message #432037] Fri, 20 November 2009 10:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #432061 is a reply to message #432034] Fri, 20 November 2009 14:22 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Simply make a view as follows

create view WIB as
select Tstate state, Tsale_cnt sale_cnt
from Tot_sale;

Then anytime you need it, you have the up to time data.
Re: EXECUTE IMMEDIATE 'Create table' [message #432065 is a reply to message #432034] Fri, 20 November 2009 17:25 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #432288 is a reply to message #432286] Mon, 23 November 2009 07:52 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
What did this add other than make use of an unneeded execute immediate statement for a simple insert?
Re: EXECUTE IMMEDIATE 'Create table' [message #432289 is a reply to message #432286] Mon, 23 November 2009 08:14 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
why DON'T you simply use a select from tot_sales in your code instead of making a temporary table?
Re: EXECUTE IMMEDIATE 'Create table' [message #432299 is a reply to message #432034] Mon, 23 November 2009 08:45 Go to previous messageGo to next message
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 #432300 is a reply to message #432299] Mon, 23 November 2009 08:50 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
So basically you are going to ignore the (very good) advice offerred to you today?
Re: EXECUTE IMMEDIATE 'Create table' [message #432301 is a reply to message #432300] Mon, 23 November 2009 08:53 Go to previous messageGo to next message
Bill B
Messages: 1482
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
lamnguyen14 wrote on Mon, 23 November 2009 16:04
But 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.
Re: EXECUTE IMMEDIATE 'Create table' [message #432313 is a reply to message #432034] Mon, 23 November 2009 09:31 Go to previous message
lamnguyen14
Messages: 119
Registered: March 2007
Location: Virginia
Senior Member
Thanks. I will do like what you suggest.
And again. Thank you very much for your help. Thanks to all of you.

Previous Topic: Designing Database with reporting tables
Next Topic: selecting random rows with selected fields that adds up to a specific value
Goto Forum:
  


Current Time: Sat Dec 03 19:53:28 CST 2016

Total time taken to generate the page: 0.05376 seconds