OraFAQ Forum: Reports & Discoverer » Read WHERE clause from TXT file. Possible?

Home » Developer & Programmer » Reports & Discoverer » Read WHERE clause from TXT file. Possible? ( 10.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Read WHERE clause from TXT file. Possible? [message #608675]

Sat, 22 February 2014 00:40

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

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608678 is a reply to message #608677]

Sat, 22 February 2014 01:00

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

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608679 is a reply to message #608678]

Sat, 22 February 2014 01:01

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I suspect the file is not in your SQLPATH.

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608680 is a reply to message #608679]

Sat, 22 February 2014 01:16

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?

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608681 is a reply to message #608680]

Sat, 22 February 2014 01:22

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just give the full path of the file to test.

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608682 is a reply to message #608681]

Sat, 22 February 2014 01:27

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

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608684 is a reply to message #608682]

Sat, 22 February 2014 01:34

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback and summary.

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608685 is a reply to message #608684]

Sat, 22 February 2014 01:54

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????

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608687 is a reply to message #608685]

Sat, 22 February 2014 02:19

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is SQL*Plus stuff.

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608688 is a reply to message #608687]

Sat, 22 February 2014 02:59

bluetooth420
Messages: 146
Registered: November 2011

Senior Member

yup.... so any luck to implement such idea in reports ?

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608689 is a reply to message #608688]

Sat, 22 February 2014 03:08

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Read where Cluase from TXT file. Possible? [message #608698 is a reply to message #608689]

Sat, 22 February 2014 08:39

Littlefoot
Messages: 21823
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);

Report message to a moderator

Previous Topic:	Rank calculation in Reports
Next Topic:	Reports 10g Web layout

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 11 17:23:04 CST 2025