Home » SQL & PL/SQL » 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 Go to next message
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 #544078 is a reply to message #544066] Mon, 20 February 2012 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
EXTERNAL TABLES feature offers what you are looking for. However, file must be located on a database server (not your own PC - unless you have installed database on it, of course). So, ask your DBA to create a directory on a server for you and grant READ/WRITE privileges to user you are connected to when working with that file. It would be convenient if you could manipulate file yourself, so see with your system administrator whether you are allowed to map that directory on your computer (otherwise, someone with higher privileges will have to put that file onto the server every time you need to change it).

Alternatively, you could create a table in your own schema and load data from that CSV file into it using SQL*Loader. It is a very simple example (one column table) and that principle might work very well (if you know how to, you could even load it through TOAD (although that's not what I'd suggest)).
Re: Running Query using CSV file as a reference [message #544085 is a reply to message #544078] Mon, 20 February 2012 01:25 Go to previous messageGo to next message
shang_man
Messages: 3
Registered: February 2012
Junior Member
I should be able to upload the .csv file onto a read/write directory onto a directory within the Database server. Just to clarify, the .csv file would be created by myself. So in otherwords, I need to check a list of my own numbers against the status that is held in our DB.

Can you offer me an example of how I could use External Table in the fashion which you have described?

Thanks.
Re: Running Query using CSV file as a reference [message #544087 is a reply to message #544085] Mon, 20 February 2012 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You didn't mention database version you use; here's 10g documentation - get familiar with the concept, the way they are created and used.

Here's an OraFAQ article, you might find it useful. Search the board.

There are many examples on the Internet as well - search for them.
Re: Running Query using CSV file as a reference [message #544175 is a reply to message #544087] Mon, 20 February 2012 09:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
If your csv file is on your server, or you can move or copy it there, then you can:

SELECT d.number_id, d.status_id
FROM   your_table d
WHERE  d.number_id IN (
@ numbersfile.csv -- must be on a separate line
)
/

Re: Running Query using CSV file as a reference [message #544216 is a reply to message #544175] Mon, 20 February 2012 12:43 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is something new for me ... I tried to locate it in documentation, but - failed. Where is such a syntax described, Barbara?
Re: Running Query using CSV file as a reference [message #544222 is a reply to message #544216] Mon, 20 February 2012 13:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
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:

10,20


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:

'CLERK','MANAGER'


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 #544230 is a reply to message #544222] Mon, 20 February 2012 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59409
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.


You also saw it here (but I failed to retreive it). Smile

Regards
Michel
Re: Running Query using CSV file as a reference [message #544266 is a reply to message #544230] Tue, 21 February 2012 00:14 Go to previous messageGo to next message
Littlefoot
Messages: 19686
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for a wonderful explanation! What can I say, that's really c00l!

Now I'm going to save your code, for future reference.
Re: Running Query using CSV file as a reference [message #544272 is a reply to message #544175] Tue, 21 February 2012 00:52 Go to previous messageGo to next message
shang_man
Messages: 3
Registered: February 2012
Junior Member
Barbara Boehmer wrote on Tue, 21 February 2012 02:02
If your csv file is on your server, or you can move or copy it there, then you can:

SELECT d.number_id, d.status_id
FROM   your_table d
WHERE  d.number_id IN (
@ numbersfile.csv -- must be on a separate line
)
/



Hi Barbara,

I have tried this and no go. I am using TOAD 7.6.0.11, I have saved the file to the server and when I go to execute the statement it highlights the @ symbol - so I guess its not recognizing it? I believe we are using Oracle SQL 9i. Otherwise I am a little stumped.

I have not explored the external table info provided by littlefoot as yet as your method sounds much easier haha

Can you help me any further with this? thanks.
Re: Running Query using CSV file as a reference [message #544273 is a reply to message #544272] Tue, 21 February 2012 00:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
You need to run it from SQL*Plus, not Toad.
Re: Running Query using CSV file as a reference [message #544284 is a reply to message #544273] Tue, 21 February 2012 01:22 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7995
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 Go to previous message
Barbara Boehmer
Messages: 7995
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.

Previous Topic: iSQL*Plus
Next Topic: Unable to connect database.
Goto Forum:
  


Current Time: Tue Oct 21 12:53:42 CDT 2014

Total time taken to generate the page: 0.07362 seconds