Home » SQL & PL/SQL » SQL & PL/SQL » Prompting user to input informatio (Oracle 10, sql developer)
Prompting user to input informatio [message #382475] Thu, 22 January 2009 08:30 Go to next message
da1uwant2meet
Messages: 13
Registered: October 2008
Location: texax
Junior Member
Hello,

We are migrating from Oracle 7 to version 10. I am currently testing our queries to see if they run in 10 and also to input some hints...

select /*+RULE*/ L.LOT_ID,
S.SOURCE_LABEL,
A.ALIAS_NAME
from LOT L,
SOURCES S,
ALIASES A,
NP_MATERIAL M
where L.RGSTRY_ID = M.MATERIAL_ID
and M.SOURCE_ID = S.SOURCE_ID
and S.SOURCE_ID = A.SOURCE_ID
and L.LOT_ID in(&LOT_ID)

this query works fine in 7 but does not work in 10..here is the error msg I get

java.sql.SQLException
ORA-01008: not all variable bound
SQL State 72000
Error Code 1008

I know that the problems lies with this part of the query and L.LOT_ID in(&LOT_ID)...My question is...Is there a new way to prompt users to enter information than it was in Oracle 7...Does Oracle 10 not recognize the (&) anymore?
Re: Prompting user to input informatio [message #382476 is a reply to message #382475] Thu, 22 January 2009 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Mon, 27 October 2008 20:20
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

...
Regards
Michel

Re: Prompting user to input informatio [message #382525 is a reply to message #382475] Thu, 22 January 2009 16:15 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I could not reproduce the error in SQL*Plus:
SQL> select banner from v$version;

BANNER
----------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 

SQL> SELECT /*rule*/ dname
  2  FROM DEPT
  3  WHERE deptno IN (&dno);
Enter value for dno: 10

DNAME
--------------
ACCOUNTING

SQL>
Obviously, "&" works as expected.

However, "java.sql.SQLException" might be a hint for someone who uses Java (I, unfortunately, do not). So: how did you run this SQL? Did you, perhaps, forget to set the parameter/variable before calling this SELECT statement?
Re: Prompting user to input informatio [message #382658 is a reply to message #382525] Fri, 23 January 2009 08:32 Go to previous message
da1uwant2meet
Messages: 13
Registered: October 2008
Location: texax
Junior Member
Littlefoot wrote on Thu, 22 January 2009 17:15
I could not reproduce the error in SQL*Plus:
SQL> select banner from v$version;

BANNER
----------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 

SQL> SELECT /*rule*/ dname
  2  FROM DEPT
  3  WHERE deptno IN (&dno);
Enter value for dno: 10

DNAME
--------------
ACCOUNTING

SQL>
Obviously, "&" works as expected.

However, "java.sql.SQLException" might be a hint for someone who uses Java (I, unfortunately, do not). So: how did you run this SQL? Did you, perhaps, forget to set the parameter/variable before calling this SELECT statement?



I figured out what the problem was. Apparently sql developer version 1.5.3 didnt like the parenthesis because when I took them off it worked..
Previous Topic: Getting :NEW BLOB Value using AFTER INSERT Triggers
Next Topic: query
Goto Forum:
  


Current Time: Sat Dec 10 07:13:01 CST 2016

Total time taken to generate the page: 0.08735 seconds