Home » SQL & PL/SQL » SQL & PL/SQL » How to build a query that gets its values from a file (Oracle SQL Developer Version 4.0.1.14 / windows 7.0)
How to build a query that gets its values from a file [message #676476] Tue, 11 June 2019 19:45 Go to next message
hartyshow@yahoo.fr
Messages: 2
Registered: June 2019
Junior Member
I'm trying to build a query that uses the values in a file.

I'm hoping to get something like this: `select Epm,Emp_num,Emp_Addr From Dir_Rimco_Table where Emp in (Dir_Emp_Rsed.txt)`

Constraints
-I can't create a new table
-I can't modify Dir_Rimco_Table
-Current Privileges
Attached sna.jpg

Any idea how I can do that?
  • Attachment: sna.jpg
    (Size: 117.92KB, Downloaded 1727 times)
Re: How to build a query that gets its values from a file [message #676477 is a reply to message #676476] Tue, 11 June 2019 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

SQL statements must be known & static at compile time.
Otherwise you must use EXECUTE IMMEDIATE (Please GOOGLE for details).
Re: How to build a query that gets its values from a file [message #676478 is a reply to message #676476] Wed, 12 June 2019 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Given the privileges you have the only ways I think are:
1) use SQL*Plus include and substitution variables features (if the file is on the client side)
2) use PL/SQL with UTL_FILE and EXECUTE IMMMEDIATE or REF CURSOR (if the file is on the server side)
3) build the statement at OS level

For instance with a file of employee names like:
C:\>type c:\emp.lst
KING
SMITH
ALLEN
CLARK
SCOTT

For example for 1):
SQL> column list new_value list
SQL> select ''''||replace(trim(both ',' from replace('
  2  @c:\emp.lst
  7  ','
  8  ',',')),',',''',''')||'''' list
  9  from dual
 10  /
LIST
--------------------------------------
'KING','SMITH','ALLEN','CLARK','SCOTT'

1 row selected.

SQL> select * from emp where ename in (&list)
  2  /
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10

5 rows selected.

For example for 2) (the dbms_output is there just to show the generated statement):
SQL> var c refcursor
SQL> declare
  2    file  utl_file.file_type;
  3    stmt  varchar2(1000);
  4    line  varchar2(50);
  5    first boolean := true;
  6  begin
  7    stmt := 'select * from emp where ename in (';
  8    file := utl_file.fopen ('MY_DIR','EMP.LST','r');
  9    loop
 10      utl_file.get_line (file, line, 50);
 11      if first then first := false;
 12      else stmt := stmt || ',';
 13      end if;
 14      stmt := stmt || '''' || line || '''';
 15    end loop;
 16  exception when no_data_found then
 17    utl_file.fclose (file);
 18    stmt := stmt || ')';
 19    dbms_output.put_line(stmt);
 20    open :c for stmt;
 21  end;
 22  /
select * from emp where ename in ('KING','SMITH','ALLEN','CLARK','SCOTT')

PL/SQL procedure successfully completed.

SQL> print c;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10

5 rows selected.


[Edit: replace regexp_replace by replace which works as well.]

[Updated on: Wed, 12 June 2019 04:31]

Report message to a moderator

Re: How to build a query that gets its values from a file [message #676479 is a reply to message #676477] Wed, 12 June 2019 00:36 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
May be You could create Dir_Emp_Rsed.txt as External Table and use it in the query.

[Updated on: Wed, 12 June 2019 00:38]

Report message to a moderator

Re: How to build a query that gets its values from a file [message #676480 is a reply to message #676479] Wed, 12 June 2019 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... but "-I can't create a new table".

Re: How to build a query that gets its values from a file [message #676481 is a reply to message #676480] Wed, 12 June 2019 00:43 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Oh I overlooked this Embarassed Hard to work without suitable tools...
Re: How to build a query that gets its values from a file [message #676482 is a reply to message #676481] Wed, 12 June 2019 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure.
Re: How to build a query that gets its values from a file [message #676489 is a reply to message #676482] Wed, 12 June 2019 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
To be fair a restriction against creating new tables doesn't necessarily prevent creating an external table (may well do, but depends on exact reason/nature of restriction).

For Michel's option 2 you don't actually need dynamic SQL, it's a varying in list
Re: How to build a query that gets its values from a file [message #676491 is a reply to message #676489] Wed, 12 June 2019 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In my second example, UTL_FILE can be replaced by DBMS_LOB:
SQL> var c refcursor
SQL> declare
  2    l_clob          clob;
  3    l_bfile         bfile := bfilename('MY_DIR', 'EMP.LST');
  4    l_amount        pls_integer;
  5    l_dest_offset   pls_integer := 1;
  6    l_source_offset pls_integer := 1;
  7    l_bfile_csid    pls_integer := dbms_lob.default_csid;
  8    l_lang_context  pls_integer := dbms_lob.default_lang_ctx;
  9    l_warning       pls_integer;
 10    stmt            varchar2(1000);
 11    work             varchar2(1000);
 12  begin
 13    stmt := 'select * from emp where ename in (';
 14    dbms_lob.createtemporary (l_clob, false);
 15    dbms_lob.open (l_bfile);
 16    l_amount := dbms_lob.getlength(l_bfile);
 17    dbms_lob.loadclobfromfile
 18      (l_clob, l_bfile, l_amount, l_dest_offset, l_source_offset,
 19       l_bfile_csid, l_lang_context, l_warning);
 20    dbms_lob.close (l_bfile);
 21    work := l_clob;
 22    dbms_lob.freetemporary (l_clob);
 23    work := ''''||replace(trim(both ',' from replace(work, chr(13)||chr(10), ',')),',',''',''')||'''';
 24    work := rtrim(work, ',');
 25    stmt := stmt || work|| ')';
 26    dbms_output.put_line(stmt);
 27    open :c for stmt;
 28  end;
 29  /
select * from emp where ename in ('KING','SMITH','ALLEN','CLARK','SCOTT')

PL/SQL procedure successfully completed.

SQL> print c;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981 00:00:00       1600        300         30
      7782 CLARK      MANAGER         7839 09/06/1981 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 19/04/1987 00:00:00       3000                    20
      7839 KING       PRESIDENT            17/11/1981 00:00:00       5000                    10

5 rows selected.
Re: How to build a query that gets its values from a file [message #676497 is a reply to message #676491] Wed, 12 June 2019 09:47 Go to previous messageGo to next message
hartyshow@yahoo.fr
Messages: 2
Registered: June 2019
Junior Member
Thanks Michel, this is the error that I'm getting, the file exist but somehow it can't find it.
Error starting at line : 2 in command -
declare
      file  utl_file.file_type;
      stmt  varchar2(1000);
      line  varchar2(50);
      first boolean := true;
    begin
      stmt := 'select * from emp where ename in 
	  (';file := utl_file.fopen ('C:\Users\Rimco_User\Desktop','test_file.txt','r');
      loop
       utl_file.get_line (file, line, 50);
       if first then first := false;
       else stmt := stmt || ',';
       end if;
       stmt := stmt || '''' || line || '''';
     end loop;
   exception when no_data_found then
     utl_file.fclose (file);
     stmt := stmt || ')';
     dbms_output.put_line(stmt);
     open :c for stmt;
   end;
Error report -
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 8
29280. 00000 -  "invalid directory path"
*Cause:    A corresponding directory object does not exist.
*Action:   Correct the directory object parameter, or create a corresponding
           directory object with the CREATE DIRECTORY command.

Re: How to build a query that gets its values from a file [message #676499 is a reply to message #676497] Wed, 12 June 2019 10:23 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
In more recent versions of oracle the first parameter to utl_file.fopen needs to be the name of an oracle directory object.
You need to create a directory object (or use an existing one) that points to the directory on the DB server (if it's not on the DB server you can't get at it).
Previous Topic: Backup
Next Topic: How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va
Goto Forum:
  


Current Time: Fri Mar 29 08:36:35 CDT 2024