OraFAQ Forum: SQL & PL/SQL » Creating a table and referencing that table in same package.

Home » SQL & PL/SQL » SQL & PL/SQL » Creating a table and referencing that table in same package. (Oracle 11g PL/SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creating a table and referencing that table in same package. [message #608373]

Wed, 19 February 2014 04:53

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.

Report message to a moderator

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

cookiemonster
Messages: 13920
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?

Report message to a moderator

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

ThomasG
Messages: 3211
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?

Report message to a moderator

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

John Watson
Messages: 8931
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.

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8931
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?

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8931
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.

Report message to a moderator

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

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Look up Temporary tables.

HTH
-g

Report message to a moderator

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

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.

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, 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.

Report message to a moderator

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

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.

Report message to a moderator

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

flyboy
Messages: 1903
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.

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8931
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

Smile

Report message to a moderator

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

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.

Report message to a moderator

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

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

You could always run your PL/SQL in an anonymous block within your table creation script.

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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

Smile

lol which there no way I'm going to do....

But thanks for your response.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

Previous Topic:	Exception while creating table using EXECUTE IMMEDIATE
Next Topic:	Value multiplication help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 17:53:03 CDT 2024