Home » Developer & Programmer » Reports & Discoverer » Read WHERE clause from TXT file. Possible? ( 10.2.0)
Read WHERE clause from TXT file. Possible? [message #608675] Sat, 22 February 2014 00:40 Go to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Hi

Is it possible to read where clause from TXT file.

http://www.orafaq.com/forum/m/544066/?srch=external+query#msg_544066

I got guidence form the above link but my query is giving error

select *
from emp
where empno in (@empno.txt)


where emp.txt contains empno 7369 and 7499 in different lines.

The error is
where empno in (@empno.txt)
                *
ERROR at line 3:
ORA-00936: missing expression


Any further thoughts?

[EDITED by LF: fixed topic title typo; was "cluase"]

[Updated on: Sat, 22 February 2014 08:34] by Moderator

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608677 is a reply to message #608675] Sat, 22 February 2014 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The topic you mentioned explain in details how to do it: the @file must be on a separate line.
SQL> select *
  2  from emp
  3  where empno in (@empno.txt)
  4  /
where empno in (@empno.txt)
                *
ERROR at line 3:
ORA-00936: missing expression


SQL> select *
  2  from emp
  3  where empno in (
  4  @empno.txt
  5  )
  6  /
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30

2 rows selected.



Re: Read where Cluase from TXT file. Possible? [message #608678 is a reply to message #608677] Sat, 22 February 2014 01:00 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Sir, but i am still getting the error
Quote:

1 select *
2 from emp
3 where empno in
4 (
5 @empno.txt
6* )
SQL> /
@empno.txt
*
ERROR at line 5:
ORA-00936: missing expression
Re: Read where Cluase from TXT file. Possible? [message #608679 is a reply to message #608678] Sat, 22 February 2014 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I suspect the file is not in your SQLPATH.

Re: Read where Cluase from TXT file. Possible? [message #608680 is a reply to message #608679] Sat, 22 February 2014 01:16 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Quote:
SQL> get empno.txt
1 7369
2* 7499


The simple get command is showing values.

Doesn't this mean that my SQLPATH is set?

DO i have to check regedit in order to see SQLPATH?
Re: Read where Cluase from TXT file. Possible? [message #608681 is a reply to message #608680] Sat, 22 February 2014 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just give the full path of the file to test.

Re: Read where Cluase from TXT file. Possible? [message #608682 is a reply to message #608681] Sat, 22 February 2014 01:27 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
Thank you so much for your precious time.

I did it.

My learning points are
1) @ command MUST BE ON SEPARATE LINE in SQL command
2) There SHOULD be space between "@" and "empno.txt"
3) The direct command is not working, i have to save it in some file e.g. save abc and then later on the query was running by @abc
4) The values in empno.txt must have comma between them (as it will be placed as it is in brackets of where clause)
5) Path do matters.

Thanks
Re: Read where Cluase from TXT file. Possible? [message #608684 is a reply to message #608682] Sat, 22 February 2014 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback and summary.

Re: Read where Cluase from TXT file. Possible? [message #608685 is a reply to message #608684] Sat, 22 February 2014 01:54 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
select *
from emp
where empno in
(
@ abc
)

The above command is not working in oracle reports Smile
so????
Re: Read where Cluase from TXT file. Possible? [message #608687 is a reply to message #608685] Sat, 22 February 2014 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is SQL*Plus stuff.

Re: Read where Cluase from TXT file. Possible? [message #608688 is a reply to message #608687] Sat, 22 February 2014 02:59 Go to previous messageGo to next message
bluetooth420
Messages: 146
Registered: November 2011
Senior Member
yup.... so any luck to implement such idea in reports ?
Re: Read where Cluase from TXT file. Possible? [message #608689 is a reply to message #608688] Sat, 22 February 2014 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know Reports but I can move the topic to this forum.

Re: Read where Cluase from TXT file. Possible? [message #608698 is a reply to message #608689] Sat, 22 February 2014 08:39 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I know, Reports can't do it that way. Use a workaround Barbara described in the same discussion (you posted a link in your first message here) - an external table. Query would then evaluate to
select *
from emp
where empno in
(select empno from abc_as_external_table);
Previous Topic: Rank calculation in Reports
Next Topic: Reports 10g Web layout
Goto Forum:
  


Current Time: Fri Mar 29 01:18:39 CDT 2024