How to do the following in Oracle?

kosenrufu
kosenrufu used Ask the Experts™
on
I have this:

DROP TABLE TempTbl;        



CREATE GLOBAL TEMPORARY TABLE TempTbl (
  UPC_CD  NUMBER
) ON COMMIT DELETE ROWS;

store string to array named arrVal
arrVal () = Split(1234,344,334434,33444,",")

For i =0 to Ubound(arrVal ())

INSERT INTO  TempTbl(UPC_CD) VALUES(arrVal (i));

Next i

SELECT * FROM TempTbl;


I want to store a string of numbers into an array and then loop through the array and append the numbers to the temporary table. I want to detect if the temporary table exists, if not then create, else drop and then create.

How can this be done?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

Commented:
I'm going to guess this was code ported from sql server.

global temporary tables aren't used the same way as sql server temp tables.

you don't create and drop them on the fly.  You create them once, just like a normal table.
Most Valuable Expert 2011
Top Expert 2012

Commented:
you can use a function like str2tbl (search this site)

or you can do something like this if 10g or higher

INSERT INTO temptbl(upc_cd)
    SELECT     REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL)
          FROM (SELECT '1234,344,334434,33444' s FROM DUAL)
    CONNECT BY LEVEL <= LENGTH(s) - LENGTH(REPLACE(s, ',')) + 1
               AND REGEXP_SUBSTR(s, '[^,]+', 1, LEVEL) IS NOT NULL
Most Valuable Expert 2011
Top Expert 2012

Commented:
what is the real goal here?  I assume you're going to want to use the data in the temp table in some way.  

What is that use?  Based on the question thus far, I'm guessing you're going to create a cursor and iterate through each row.  If so,  you might want to reconsider using set operations.  Less code and much more efficient.  Maybe even eliminate the need to have a temp table

Pursuing this should be in a separate question though as it's beyond the scope of what was originally asked here
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I want to store UPC numbers into a temp table read from a local text file so I can use it to query data from another oracle table on the server
Most Valuable Expert 2011
Top Expert 2012

Commented:
ok, that's probably a good use for it then.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You might also look at External Tables.

This way you don't need to involve a temporary table at all:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tables013.htm#ADMIN12896

Most Valuable Expert 2011
Top Expert 2012

Commented:
external tables will help load the "lines" of data from your file
but you'll still need to do the line splitting to parse the fields from each line into rows of your temp table.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
It depends on the layout of the flat file.  I'm going out on a limb here and guessing a little that the flat file isn't in CSV format and the overall 'porting' of the code part way through just happened to create the CSV.

Now if the flat file is in CSV format, then yes, the external table probably won't help much except to get the rows from the file into some variable that can then be selected and inserted in to the temp table.

Even with the regexp insert example, you need the value from the flat file somehow.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial