Link to home
Start Free TrialLog in
Avatar of kosenrufu
kosenrufu

asked on

How to do the following in Oracle?

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?
Avatar of Sean Stuber
Sean Stuber

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.
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
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
Avatar of kosenrufu

ASKER

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
ok, that's probably a good use for it then.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.