OraFAQ Forum: SQL & PL/SQL » & problem in sql query....

Home » SQL & PL/SQL » SQL & PL/SQL » & problem in sql query....

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

& problem in sql query.... [message #20033]

Thu, 25 April 2002 12:33

shreya
Messages: 15
Registered: March 2001

Junior Member

Hi,

My query is like this.
SELECT a.* FROM &schema1.account@x1 a,
&schema2.account@x2 b;

The problem is as follows

Enter value for schema1: shreya1
old 3: FROM &schema1.account@x1 a,
new 3: FROM shreya1account@x1 a,
Enter value for schema2: shreya2
old 4: &schema2.account@x2 b
new 4: shreya2account@x2 b
shreya2account@x2 b
*
ERROR at line 4:
ORA-00942: table or view does not exist
ORA-02063: preceding line from x2

It doesn't include . like shreya2.account@x2.
Does anyone have any idea why this is happening.

Thanx,
Shashi.

Report message to a moderator

Re: & problem in sql query.... [message #20035 is a reply to message #20033]

Thu, 25 April 2002 13:09

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

anything after & without break is treated as a single placeHolder only.
so try something like (ie...just give a space after the &schema1 and &schema2 to indicate SQL that & placeHolder is terminated)

SELECT a.* FROM &schema1 .account@x1 a,
&schema2 .account@x2 b;

i tried this and works fine( ofcourse,,,its givin a cartesian product)..

1* select a.* from &sc1 .emp a, &sc2 .dept b
SQL> /
Enter value for sc1: mag
Enter value for sc2: mag
old 1: select a.* from &sc1 .emp a, &sc2 .dept b
new 1: select a.* from mag .emp a, mag .dept b

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

....
....
56 rows selected.

Report message to a moderator