Home » SQL & PL/SQL » SQL & PL/SQL » Creating a table and referencing that table in same package. (Oracle 11g PL/SQL)
Creating a table and referencing that table in same package. [message #608373] Wed, 19 February 2014 04:53 Go to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Hi Guys,

I'm creating a PL/SQL package and I want dynamically creates a number of tables in the database using the EXECUTE IMMEDIATE statement in my package before processing data into those tables.

I want to then reference these tables as a PL/SQL table ROWTYPE (see below).

TYPE l_record IS TABLE OF LIQ_HOLDING%ROWTYPE;
l_table L_RECORD;

Obvisouly the package won't compile because the LIQ_HOLDING table that I'm creating doesn't exist at the time the package compiles.

How do I get around this problem? would I have to create a type record which replicates the LIQ_HOLDING table?

Any suggestions to a solution to this problem would be great.





Re: Creating a table and referencing that table in same package. [message #608374 is a reply to message #608373] Wed, 19 February 2014 04:58 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
Don't dynamically create tables, it should never be necessary.
Why do you think you need to?
Re: Creating a table and referencing that table in same package. [message #608375 is a reply to message #608373] Wed, 19 February 2014 05:00 Go to previous messageGo to next message
ThomasG
Messages: 3113
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The solution is pretty simple. DON'T create tables dynamically.

Depending on what problem you are actually trying to solve with those dynamic tables, there are usually better ways.

So why do you want to create dynamic tables?
Re: Creating a table and referencing that table in same package. [message #608376 is a reply to message #608373] Wed, 19 February 2014 05:02 Go to previous messageGo to next message
John Watson
Messages: 4794
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

I think the only answers you will get are "don't do this", which is probably not the answer you are looking for. Can you explain why you need to do this? For example, I see this sort of thing attempted by developers with a SQL Server background, who try to emulate the temporary table functionality. This is never needed in an Oracle environment.
Re: Creating a table and referencing that table in same package. [message #608377 is a reply to message #608374] Wed, 19 February 2014 05:06 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
These tables will be just holding tables while I process the data and will be dropped once the batch process has finished.
I'd hold the data in a PL/SQL record but because there are 4 million+ rows it'll eat memory if all those records are stuck in memory, hence holding it in dynamic created database tables.
Re: Creating a table and referencing that table in same package. [message #608378 is a reply to message #608377] Wed, 19 February 2014 05:08 Go to previous messageGo to next message
John Watson
Messages: 4794
Registered: January 2010
Location: Global Village
Senior Member
Do you actually need PL/SQL at all? Why not a simply sub-query that selects the four million rows?
Re: Creating a table and referencing that table in same package. [message #608380 is a reply to message #608378] Wed, 19 February 2014 05:14 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Hi John,
Thanks for the input, I would do this in normal circumstances but I'm working at a place where the database doesn't follow the normal standard of primary/foreign keys and referential integrity (there isn't any) so to do a SQL query that links all the tables I get the data from would cripple the performance. Hence using a holding table to get all the required information for a customer before processing.
Re: Creating a table and referencing that table in same package. [message #608382 is a reply to message #608380] Wed, 19 February 2014 05:25 Go to previous messageGo to next message
John Watson
Messages: 4794
Registered: January 2010
Location: Global Village
Senior Member
Why would joining a few tables to retrieve a few million rows cripple performance? Why would there be any memory problems? Why isn't normal SQL enough? If you must do row-by-row processing, why not use a cursor?
I am sorry because it sounded an interesting problem, but I do not think I can assist without knowing what you are trying to do and what the problem is.
Re: Creating a table and referencing that table in same package. [message #608383 is a reply to message #608380] Wed, 19 February 2014 05:29 Go to previous messageGo to next message
gazzag
Messages: 356
Registered: November 2010
Location: Bristol, UK
Senior Member
Look up Temporary tables.

HTH
-g
Re: Creating a table and referencing that table in same package. [message #608386 is a reply to message #608382] Wed, 19 February 2014 05:35 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Okay guys, I don't want to post all the stuff I'm doing regarding my program.

All I'm asking is a there a solution to dynamically creating tables in the same package as the one where your referencing those dynamic table as a ROWTYPE. Forget about performance, memory problems, SQL or row by row processing.

Lets pretend it's a trick question for experienced PL/SQL developers and find out if there is an answer to that trick question.
Re: Creating a table and referencing that table in same package. [message #608388 is a reply to message #608386] Wed, 19 February 2014 05:36 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you really need a temporary table, and I agree with John - you probably don't, then permanently create a global temporary table and use that.

This approach will almost certainly not be faster than doing it in pure sql though, it's rare that anything is.
Re: Creating a table and referencing that table in same package. [message #608391 is a reply to message #608386] Wed, 19 February 2014 05:45 Go to previous messageGo to next message
gazzag
Messages: 356
Registered: November 2010
Location: Bristol, UK
Senior Member
I doubt any of the regulars here would relish giving you bad advice. I'd seriously reconsider your design - you'll get some good help if you do.
Re: Creating a table and referencing that table in same package. [message #608393 is a reply to message #608388] Wed, 19 February 2014 05:56 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
When you say permanently create a global temporary table, isn't that the same as creating a normal table and dropping the normal table at the end of the batch process?
But the question is can I dynamically create a table whether it be normal or temporary in a PL/SQL package that I reference a variable PL/SQL ROWTYPE of that table, Or is it a case of creating a PL/SQL record that replicates the structure of the holding table and use that PL/SQL as the ROWTYPE.
Re: Creating a table and referencing that table in same package. [message #608394 is a reply to message #608393] Wed, 19 February 2014 06:04 Go to previous messageGo to next message
flyboy
Messages: 1778
Registered: November 2006
Senior Member
skbytes wrote on Wed, 19 February 2014 12:56
When you say permanently create a global temporary table, isn't that the same as creating a normal table and dropping the normal table at the end of the batch process?

No, it is not. Read about it in the link gazzag provided, or the ones below in my post.
skbytes wrote on Wed, 19 February 2014 12:56
But the question is can I dynamically create a table whether it be normal or temporary in a PL/SQL package that I reference a variable PL/SQL ROWTYPE of that table,

You can create. You cannot reference.
skbytes wrote on Wed, 19 February 2014 12:56
Or is it a case of creating a PL/SQL record that replicates the structure of the holding table and use that PL/SQL as the ROWTYPE.

How will you (and hence Oracle) obtain the structure of non-existing table?

Generally, the correct solution depends on realizing what is dynamic on those "dynamic tables".

If it is their structure (columns - count and/or data types), you have a serious problem referencing them and decrypting their meaning anywhere.

If it is their data (rows), then (as many others said) create that table(s) only once before package creation as global temporary one(s).
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:48812348054
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1164655862293
You will be able to "reference" the table(s) simultaneously - other parallel processes will not fail creating the already existing table, each of them will see only its own data.
Quote:
DO NOT dynamically create them, DO NOT dynamically create them,
please -- do NOT dynamically create them.
Re: Creating a table and referencing that table in same package. [message #608396 is a reply to message #608393] Wed, 19 February 2014 06:13 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
The batch process has a number of queries that populate two holding tables (that will be created at the beginning and dropped at the end of the process) that will be used to run through my validation program that will be placed into a permenant results table. The data comes from a number of tables and because of no referential integrity (foregin keys) and bad database design (i'm only contracting at the place, so I'm given what I'm given) it would be hard to link these tables together into one SQL query. PL/SQL seems a better soultion as for each customer record I get the data it needs from a number of tables using the limited indexes that I have to use and put this data into one temporary table. The other temorary table is a subset (month of data) of data of a yearly massive transaction table. I then use an index on both the temprary tables that helps in the performance of validating the data and then outputing the results to a permenant table at the other end.
Maybe there isn't a solution, maybe I just need to create the tables at the beginning of the process in a SQL script... that would be the easy solution and then drop them at the end.
Re: Creating a table and referencing that table in same package. [message #608397 is a reply to message #608386] Wed, 19 February 2014 06:18 Go to previous messageGo to next message
John Watson
Messages: 4794
Registered: January 2010
Location: Global Village
Senior Member
skbytes wrote on Wed, 19 February 2014 11:35
Okay guys, I don't want to post all the stuff I'm doing regarding my program.

All I'm asking is a there a solution to dynamically creating tables in the same package as the one where your referencing those dynamic table as a ROWTYPE. Forget about performance, memory problems, SQL or row by row processing.

Lets pretend it's a trick question for experienced PL/SQL developers and find out if there is an answer to that trick question.
OK. You could sneak up on the problem by using any of several techniques for tricking Oracle into running code that you do not directly invoke. For example, both Fine Grained Access Control and Virtual Private Database have policies that can invoke a PL/SQL procedure as part of SQL statement, but they are not subject to the same limitations as a trigger.
This would be classed as a "side effect". It could also be classed as a "SQL injection". Names like that do suggest that they are not a good idea Smile

Re: Creating a table and referencing that table in same package. [message #608400 is a reply to message #608397] Wed, 19 February 2014 06:54 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Thanks Guys you've all been a big help and thanks for all your responses and taking your time to respond, I think i'll decide on the easy option of creating the temporary tables in an SQL script and then run the the PL/SQL package and then drop them afterwards.

I just thought there may have been a solution or a way of puttting all the code into one package rather having 2 SCRIPTS, one to create the tables and then the PL/SQL package.
Re: Creating a table and referencing that table in same package. [message #608402 is a reply to message #608400] Wed, 19 February 2014 07:20 Go to previous messageGo to next message
gazzag
Messages: 356
Registered: November 2010
Location: Bristol, UK
Senior Member
You could always run your PL/SQL in an anonymous block within your table creation script.
Re: Creating a table and referencing that table in same package. [message #608404 is a reply to message #608402] Wed, 19 February 2014 07:41 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you planning on leaving the package in the DB when the process is finished?
Because if you are that would be a reason to create the temp tables once and leave them alone, otherwise you're just going to invalidate the package when you drop them.
Re: Creating a table and referencing that table in same package. [message #608408 is a reply to message #608404] Wed, 19 February 2014 08:06 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
>then run the the PL/SQL package and then drop them afterwards.
CREATE VIEW would be MUCH less resource intensive; than moving actual data blocks into new (temp) tables; only to be dropped soon there after.
Re: Creating a table and referencing that table in same package. [message #608409 is a reply to message #608404] Wed, 19 February 2014 08:10 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Hi Cookiemonster, the package will be recompiled the next time it's executed with the SQL table creation script. But it's a good point and i'll talk to the DBA and maybe just truncated the temporary (now perm tables I guess) tables once the process is finished rather than drop them.

Thanks Gazza for the anonymous block idea.... I always like to create a package rather than anonymous block as this is a month end process and I can stick this in the DBMS_JOB's queue if its a package. Plus the code is in the database at least whether it be invalid/valid code.
Re: Creating a table and referencing that table in same package. [message #608410 is a reply to message #608409] Wed, 19 February 2014 08:25 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Hi Blackswan,

Because there's no referencial integrity or good indexing on the database tables at the moment to link the tables together a view wouldn't be much different to what I'm doing at the moment.

Anyway it was just a question of seeing if you could trick Oracle and reference a database table that hadn't been created yet... but judging by all the responses there isn't a way. Unless I create a one package that will create another whole package in a string variable and then create it by using Execute Immediate Smile lol which there no way I'm going to do....

But thanks for your response.
Re: Creating a table and referencing that table in same package. [message #608412 is a reply to message #608410] Wed, 19 February 2014 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 23035
Registered: January 2009
Senior Member
> a view wouldn't be much different to what I'm doing at the moment.
your CREATE TABLE physically moves data block while CREATE VIEW does not move data blocks.
If you deem that to be "not much different"; then we have very different perspectives regarding database realities.

If/when EXECUTE IMMEDIATE is used to CREATE TABLE; then any & all SQL against that table must also be done via EXECUTE IMMEDIATE;
which scales as well as my pet goat can fly.
Re: Creating a table and referencing that table in same package. [message #608415 is a reply to message #608409] Wed, 19 February 2014 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 11181
Registered: September 2008
Location: Rainy Manchester
Senior Member
skbytes wrote on Wed, 19 February 2014 14:10
Hi Cookiemonster, the package will be recompiled the next time it's executed with the SQL table creation script. But it's a good point and i'll talk to the DBA and maybe just truncated the temporary (now perm tables I guess) tables once the process is finished rather than drop them.

You need to read up on global temp tables because you're worrying about non existent problems.
A GTT holds data either for the life of a transaction or the life of a session depending on how it's set up, once that's done all data it held disappears without you having to do a thing.
It's not going to be holding big chunks of memory / disk space between runs.
Re: Creating a table and referencing that table in same package. [message #608418 is a reply to message #608412] Wed, 19 February 2014 09:01 Go to previous messageGo to next message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Hi Blackswan,

A normal view is just a view of the physical data block you will move eventually if you have to access that data. A view is there to take away the complexity of all the joins, etc that you'd have to do if the view wasn't there. I understand what your saying but with the database I'm working on I can't really link all the tables together to create one view I'm afraid.

thanks for the input though.
Re: Creating a table and referencing that table in same package. [message #608419 is a reply to message #608418] Wed, 19 February 2014 09:05 Go to previous message
skbytes
Messages: 21
Registered: February 2014
Location: Chester
Junior Member
Thanks Cookiemonster, I think it's a great idea global temp tables in the final cut of the program I guess. I think think the testing guys who'll test the program will need a cut of the data that's being put into the holding tables to see if it's correct or not.
Previous Topic: Exception while creating table using EXECUTE IMMEDIATE
Next Topic: Value multiplication help
Goto Forum:
  


Current Time: Mon Nov 24 06:36:18 CST 2014

Total time taken to generate the page: 0.11267 seconds