|
|
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 |
|
Michel Cadot
Messages: 68658 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 #676491 is a reply to message #676489] |
Wed, 12 June 2019 04:38 |
|
Michel Cadot
Messages: 68658 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 |
|
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 |
cookiemonster
Messages: 13925 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).
|
|
|