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,3344 4,",")
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?
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,3344
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?
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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
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.