Home » Other » Client Tools » Running Query using CSV file as a reference (Unsure)
Running Query using CSV file as a reference [message #544066] |
Sun, 19 February 2012 21:42 |
|
shang_man
Messages: 3 Registered: February 2012
|
Junior Member |
|
|
Hi There,
Firstly I need to say I am an absolute NOOB when it comes to SQL so please accept that I am about to ask an absolute NOOB question HAHA.
OK here goes...I need a script to run in TOAD that will reference a CSV file saved onto my local hard drive. I'll try and describe exactly what I need to do.
The current script which I use via TOAD on our companies READ ONLY database is this:
SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN ('1230001', '1230002', '1230003')
This will return a result for each number that exists within the table.number table along with the status of each number i.e. active or inactive. A very basic query.
What I need to be able to do is run that query but instead of having to copy each number into TOAD manually, I need TOAD to check a .csv file of said numbers and then return the results.
So I imagine the query would look something like:
SELECT d.number_id,
d.status_id,
FROM table.number_t d
WHERE d.number_id IN (check file c:\numbersfile.csv)
I have no idea how to make this work and I cannot find anything online anywhere that does'nt require some sort of "write" to the database. Its a strictly read only query - I do not require the output to be created to an external file as I can export the result via TOAD.
Any help here would be greatly appreciated. Thanks.
Mark
|
|
|
|
|
|
|
|
Re: Running Query using CSV file as a reference [message #544222 is a reply to message #544216] |
Mon, 20 February 2012 13:41 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Littlefoot,
The @ sign is the same as START in SQL*Plus and either one starts a script file and that is documented in the SQL*Plus User's Guide. However, I don't think I have seen any documented examples of this particular creative usage. Years ago, I saw some examples (on the OTN forum I think) where such syntax was used to add lines from a file into a procedure. For example, if you have a test.dat text file like:
and you create a procedure like:
create or replace procedure test_proc
(p_refcur out sys_refcursor)
as
begin
open p_refcur for
select * from dept where deptno in (
@ test.dat
);
end test_proc;
/
Then, if you query user_source, you see that it writes the values from the test.dat into the procedure:
SCOTT@orcl_11gR2> select text
2 from user_source
3 where name = 'TEST_PROC'
4 order by line
5 /
TEXT
--------------------------------------------------------------------------------
procedure test_proc
(p_refcur out sys_refcursor)
as
begin
open p_refcur for
select * from dept where deptno in (
10,20
);
end test_proc;
9 rows selected.
Fairly recently, I remember seeing a thread where somebody creatively used this same method just running a query in SQL*Plus, without creating a procedure. For example, if you have a test.csv file like:
and you run a query like this, either from the SQL*Plus prompt or from within a script:
select ename, job from emp where job in (
@test.csv
)
/
Then you get:
SCOTT@orcl_11gR2> select ename, job from emp where job in (
2 @test.csv
2 'CLERK','MANAGER'
3 )
4 /
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
ADAMS CLERK
JAMES CLERK
MILLER CLERK
7 rows selected.
I think that thread was in the OraFAQ forums, but I don't remember which sub-forum or what date and can't seem to find it. I saved a test file of the above on Feb 09, 2012, so I think it must have been around that date, give or take a few days.
|
|
|
|
|
|
|
Re: Running Query using CSV file as a reference [message #544284 is a reply to message #544273] |
Tue, 21 February 2012 01:22 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Here is an example of the external table method.
-- contents of c:\my_oracle_files\numbersfile.csv:
'1230001','1230002','1230003',
SCOTT@orcl_11gR2> CREATE TABLE your_table
2 (number_id NUMBER,
3 status_id NUMBER)
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO your_table VALUES (1230001, 1)
3 INTO your_table VALUES (1230002, 2)
4 INTO your_table VALUES (1230003, 3)
5 INTO your_table VALUES (1230004, 4)
6 SELECT * FROM DUAL
7 /
4 rows created.
SCOTT@orcl_11gR2> SELECT * FROM your_table
2 /
NUMBER_ID STATUS_ID
---------- ----------
1230001 1
1230002 2
1230003 3
1230004 4
4 rows selected.
SCOTT@orcl_11gR2> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\my_oracle_files'
2 /
Directory created.
SCOTT@orcl_11gR2> CREATE TABLE your_external_table
2 (col1 NUMBER)
3 ORGANIZATION external
4 (TYPE oracle_loader
5 DEFAULT DIRECTORY my_dir
6 ACCESS PARAMETERS
7 (RECORDS DELIMITED BY 0x'2C'
8 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "'" LDRTRIM
9 ("COL1" CHAR TERMINATED BY "," OPTIONALLY ENCLOSED BY "'"))
10 location ('numbersfile.csv'))
11 /
Table created.
SCOTT@orcl_11gR2> SELECT * FROM your_external_table
2 /
COL1
----------
1230001
1230002
1230003
4 rows selected.
SCOTT@orcl_11gR2> SELECT d.number_id, d.status_id
2 FROM your_table d
3 WHERE d.number_id IN
4 (SELECT *
5 FROM your_external_table)
6 /
NUMBER_ID STATUS_ID
---------- ----------
1230001 1
1230002 2
1230003 3
3 rows selected.
[Updated on: Tue, 21 February 2012 01:25] Report message to a moderator
|
|
|
Re: Running Query using CSV file as a reference [message #544285 is a reply to message #544284] |
Tue, 21 February 2012 01:31 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Here is an example of the SQL*Loader method.
-- contents of numbersfile.csv:
'1230001','1230002','1230003',
-- contents of SQL*Loader control file test.ctl:
LOAD DATA
INFILE numbersfile.csv "str x'2C'"
INTO TABLE data_from_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'"
(col1 INTEGER EXTERNAL)
SCOTT@orcl_11gR2> CREATE TABLE your_table
2 (number_id NUMBER,
3 status_id NUMBER)
4 /
Table created.
SCOTT@orcl_11gR2> INSERT ALL
2 INTO your_table VALUES (1230001, 1)
3 INTO your_table VALUES (1230002, 2)
4 INTO your_table VALUES (1230003, 3)
5 INTO your_table VALUES (1230004, 4)
6 SELECT * FROM DUAL
7 /
4 rows created.
SCOTT@orcl_11gR2> SELECT * FROM your_table
2 /
NUMBER_ID STATUS_ID
---------- ----------
1230001 1
1230002 2
1230003 3
1230004 4
4 rows selected.
SCOTT@orcl_11gR2> CREATE TABLE data_from_file
2 (col1 NUMBER)
3 /
Table created.
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
SCOTT@orcl_11gR2> SELECT * FROM data_from_file
2 /
COL1
----------
1230001
1230002
1230003
3 rows selected.
SCOTT@orcl_11gR2> SELECT d.number_id, d.status_id
2 FROM your_table d
3 WHERE d.number_id IN
4 (SELECT *
5 FROM data_from_file)
6 /
NUMBER_ID STATUS_ID
---------- ----------
1230001 1
1230002 2
1230003 3
3 rows selected.
|
|
|
Re: Running Query using CSV file as a reference [message #544286 is a reply to message #544285] |
Tue, 21 February 2012 01:35 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Can you post the contents of your csv file? The format makes a significant difference. For example, is it like:
'1230001','1230002','1230003',
or like:
'1230001',
'1230002',
'1230003',
It makes a difference whether it is on one line or multiple lines, with or without quotes, and with or without commas. It would also help to have the data type of your number_id, like number or varchar2.
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:59:52 CDT 2024
|