Package and temp table question

[loriek]

First, I'm not a developer, so many thanks for assistance.

I've got a developer who wants to:

Create a package
Have a temp table created in that package
Have many different statements run against that temp table

Is this possible?

Many thanks ....

 

[jimirvine]

It can be done, yes. HOWEVER there is very rarely a valid business reason for doing so. Rather than post steps on how to do such a task, which would imply that it is a normally accepted practice to do such a thing, get your developer to post here exactly what it is he/she wants to do. We may find that there is a much easier and more efficient way of achieving the requirements.

 

[Dagon]

Something like a global temporary table may be a better alternative. The GTT only needs to be created once and data inserted into it can only be seen in the session running the PL/SQL proc. Once the session disconnects, the data disappears. Also, many instances of the proc can run simultaneously and they will only see data they have inserted.

 

[jimirvine]

[qusote]Something like a global temporary table may be a better alternative[/quote] True, but only if a Temporary table is in fact needed at all i.e. the op isn't doing this to try to make multi-tables joins better (like one might do in sqlserver) or to store a set of lookup data where an associative array may suit better, or possibly a ref cursor.
Which is why I asked for more details

 

[loriek]

Here's some more details.

Again, trying to create a package....


/* Creation of a temp table to store values being processed */
CREATE GLOBAL TEMPORARY TABLE DataChecks
(STUDY_ID VARCHAR2(35), local_case_number VARCHAR2(100),
checkdetail VARCHAR2(250), CheckID VARCHAR2(5))
on commit preserve rows

/* Processing logic (Inserts, Updates, etc. on the temp table */
INSERT INTO DataChecks ('ARV','US-0001','Check 1','0001');
INSERT INTO DataChecks ('ARV','US-0001','Check 2','0002');
INSERT INTO DataChecks ('ARV','US-0001','Check 3','0003');
INSERT INTO DataChecks ('ARV','US-0001','Check 4','0004');

/* Retrieve the end results */
SELECT * FROM DataChecks;

/* Drop the temp table */
DROP TABLE DataChecks;


Getting this error:
Error: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Line: 5
Text: CREATE GLOBAL TEMPORARY TABLE DataChecks(STUDY_ID VARCHAR2(35), local_case_number VARCHAR2(100), checkdetail VARCHAR2(250), CheckID VARCHAR2(5)) on commit preserve rows;

 

[Dagon]

Is it doing this inside a package ? If so, dynamic SQL should be used e.g.

execute immediate 'CREATE GLOBAL TEMPORARY TABLE DataChecks
(STUDY_ID VARCHAR2(35), local_case_number VARCHAR2(100),
checkdetail VARCHAR2(250), CheckID VARCHAR2(5))
on commit preserve rows';

Also, I would question whether you really need to drop and re-create the GTT. See my earlier comments.

 

[Beilstwh]

Why do you not create the GTT outside of the procedure and NEVER drop it. Everytime the procedure runs, it will use the table and when the procedure finishes, the data is flushed. It makes NO sense to create and drop A GTT.

Bill
Oracle DBA/Developer
New York State, USA

 

[Thargy]

loriek,

jimirvine was correct. Please establish your business requirement before proceeding further. I regularly have to deal with folks who don't see any need for a clear cut statement of requirement. In one recent outbreak of this madness, a dev team rewrote the code three times. Each time they were assured that all was essentially correct, and just 'a few minor tweaks' would be needed at the end.

Having wasted two man weeks of effort on incorrect requirements, the dev team have now point blank refused to write a single line of code until the requirements are established.

Temporary tables, GTT's, refcursors, elves, pixies and the man in the moon are all irrelevant until you're devoting time and effort to that which will deliver what your business needs.

I'm being declamatory I know, but I've seen this so often, that I just can't button my lip.

Regards

T

Grinding away at things Oracular