Home » SQL & PL/SQL » Client Tools » (Substitution Variables) not working in "SQL Developer" Environment !!!!!!!
icon5.gif   (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298000] Mon, 04 February 2008 08:39 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

this photo illustrate how that (Substitution Variables) not working in "SQL Developer" Environment :-

http://www.imagehosting.com/show.php/1555180_ddddd.PNG.html

any solve for this problem Question Exclamation
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298001 is a reply to message #298000] Mon, 04 February 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59283
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use bind variable and not substitution variable (which is specific to SQL*Plus).

Regards
Michel
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298003 is a reply to message #298000] Mon, 04 February 2008 08:50 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
SET DEFINE &
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298056 is a reply to message #298000] Mon, 04 February 2008 18:20 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

I wana work in "SQL Developer" Environment , so how I Execute code like that :-

select &column from employees;


in "SQL Developer" Environment ??????
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298105 is a reply to message #298056] Tue, 05 February 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59283
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298133 is a reply to message #298105] Tue, 05 February 2008 02:03 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
Yes you can. I have already supplied the solution.
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298139 is a reply to message #298133] Tue, 05 February 2008 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59283
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it works fine, thanks for the correction.

Regards
Michel
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #298142 is a reply to message #298139] Tue, 05 February 2008 02:25 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
Thumbs Up
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473126 is a reply to message #298133] Thu, 26 August 2010 03:26 Go to previous messageGo to next message
Bear Knuckle
Messages: 5
Registered: August 2010
Junior Member
pablolee wrote on Tue, 05 February 2008 02:03
Yes you can. I have already supplied the solution.


No you didn't!

For me, who is having the same problem as the TO, this:

Quote:
SET DEFINE &


is no help at all.

It's like falling into a deep hole, then asking someone to help you out and the other one's replying: "Get a rope!".

What is "SET DEFINE &"?

PS: And yes, I only registered to let you know this!

[Updated on: Thu, 26 August 2010 03:27]

Report message to a moderator

Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473129 is a reply to message #473126] Thu, 26 August 2010 03:54 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
So let me get this right. You drag up a 2 year old post, so presumably you have an issue, have performed a forum search and found this thread. The solution posted is not detailed enough for you to be able to resolve your issue, so instead of asking for clarification, you decide to post what you did. Awesome attitude, well done. Bet loads of people enjoy helping you. I was considering adding an insult, but you're just not worth it.
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473131 is a reply to message #473129] Thu, 26 August 2010 04:11 Go to previous messageGo to next message
Bear Knuckle
Messages: 5
Registered: August 2010
Junior Member
pablolee wrote on Thu, 26 August 2010 03:54
So let me get this right. You drag up a 2 year old post, so presumably you have an issue, have performed a forum search and found this thread. The solution posted is not detailed enough for you to be able to resolve your issue, so instead of asking for clarification, you decide to post what you did. Awesome attitude, well done. Bet loads of people enjoy helping you. I was considering adding an insult, but you're just not worth it.


Wow, you are fast!

... and you are right, but the upper post is a direct reflection of my level of frustration.

Let me explain further: I came to this project, in which I am executing test cases. To verify the assumptions made I write SQL statements in which I compare the input whith the output and the expectations. As a Java developer I had some touch with SQL, but not at a deeper level than some stupid select/insert/update statements to get/update the information I presented on the web layer.

And now I am here and doing a fine job with complex selects and compares. I have to fullfill a "cases per day", so I am just pushing em out, but now and then I try to optimize my work and one HUGE step in optimizing is to have parameters in my statements, which sometimes repeat whith other tables or columns, so I tried to figure out now and then since 3 days how to define a variable and reuse it in my statements.

Here's what I got so far:

DEFINE num = 1;
SELECT &num FROM dual;


results in

ORA-01008: not all variables bound
01008. 00000 -  "not all variables bound"
*Cause:    
*Action:


then

VARIABLE num NUMBER;
EXEC :num := 1;
SELECT :num FROM dual;


Gives me some different errors and badest part is, the declaration and initialization is ignored and I am asked for the input of "num".

Then I tried

DECLARE
  num NUMBER := 1;
BEGIN
  SELECT num FROM dual;
END;


which leads to different errors, mostly:

PLS-00428: an INTO clause is expected in this SELECT statement


I tried variations of the above example (no semi-colons, more semicolons,...) variations of access (without :. with &, ...).

Then I found this post via google and it's a direct replication of my problem and all I got was

SET DEFINE &


which made me angry and then I started googling for "SET" and "SET DEFINE &" and so on and wasted another 30 minutes to figure out, what it could mean... without success.

Nothing works... in 3 days... in an example as simple as "Hello, world!".

Well, you must be right, I am not worth it!

PS: I am sorry for my stupid post. You are right, there is not much of a mature behavior visible.

[Updated on: Thu, 26 August 2010 04:17]

Report message to a moderator

Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473132 is a reply to message #473131] Thu, 26 August 2010 04:19 Go to previous messageGo to next message
ThomasG
Messages: 3100
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So have you even tried to JUST RUN

SET DEFINE &


in SQL Developer before your other stuff yet?

Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473134 is a reply to message #473131] Thu, 26 August 2010 04:26 Go to previous messageGo to next message
cookiemonster
Messages: 10987
Registered: September 2008
Location: Rainy Manchester
Senior Member
First obvious question - is there any reason why you can't use sqlplus?
Because those first two queries will work as is in that:
SQL> DEFINE num = 1;
SQL> SELECT &num FROM dual;
old   1: SELECT &num FROM dual
new   1: SELECT 1 FROM dual

         1
----------
         1

SQL> VARIABLE num NUMBER;
SQL> EXEC :num := 1;

PL/SQL procedure successfully completed.

SQL> SELECT :num FROM dual;

      :NUM
----------
         1

SQL> 


As for the third - as soon as you start using DECLARE/BEGIN you're writing PL/SQL rather than SQL. SQL will return the result of the query to whatever called it. In this case though that's the PL/SQL block, so you need to tell that what you want it to do with the result.
Here's a simple example:
SQL> set serveroutput on
SQL> DECLARE
  2    l_num    NUMBER := 1;
  3    l_RESULT NUMBER;
  4  BEGIN
  5    SELECT l_num INTO l_result FROM dual;
  6    dbms_output.put_line(l_result);
  7  END;
  8  /
1

PL/SQL procedure successfully completed.

SQL> 

The above will work in sqldeveloper as is - just remove the set serveroutput on line.
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473139 is a reply to message #473132] Thu, 26 August 2010 04:32 Go to previous messageGo to next message
cookiemonster
Messages: 10987
Registered: September 2008
Location: Rainy Manchester
Senior Member
ThomasG wrote on Thu, 26 August 2010 10:19
So have you even tried to JUST RUN

SET DEFINE &


in SQL Developer before your other stuff yet?



Have you?
I know pabolee says it works but I get ORA-922 myself and the only references I can find in the help talk about sqlplus.
I hardly use SQLdeveloper myself so may be I'm missing something obvious.
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473141 is a reply to message #473134] Thu, 26 August 2010 04:33 Go to previous messageGo to next message
Bear Knuckle
Messages: 5
Registered: August 2010
Junior Member
Great, now others get involved and I feel even more ashamed... Embarassed

But thanks for the help!

Yes, I tried to execute "DEFINE SET &" before my stuff. Did not change a thing. (I even did it BEFORE my "anger-post"!)

I just don't want to use sqlplus, I want to do it in SQLDeveloper for comfort reasons and now it became something personal! It HAS to work some way! (You may recognize, I am somehow emotional while programming...)

The PL/SQL script executes but the only output is "anonymous block finished".

Why is it such a magic to define a variable or constant and access it during execution in SQL?

[Updated on: Thu, 26 August 2010 04:34]

Report message to a moderator

Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473145 is a reply to message #473134] Thu, 26 August 2010 04:38 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
Apology accepted, I have been at your level of frustration before. When you are using substitution variables in SQL Developer, you must run the code by "Running as a script" i.e. either hit the F5 key or click on the run as script tool on the toolbar (or use the menu choice, or...). Note that running the code as a script will run all code that is in the code window (or all of the code that you have selected).
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473146 is a reply to message #473145] Thu, 26 August 2010 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 10987
Registered: September 2008
Location: Rainy Manchester
Senior Member
I should have realised you needed to run as a script.
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473147 is a reply to message #473145] Thu, 26 August 2010 04:52 Go to previous messageGo to next message
Bear Knuckle
Messages: 5
Registered: August 2010
Junior Member
pablolee wrote on Thu, 26 August 2010 04:38
Apology accepted, I have been at your level of frustration before. When you are using substitution variables in SQL Developer, you must run the code by "Running as a script" i.e. either hit the F5 key or click on the run as script tool on the toolbar (or use the menu choice, or...). Note that running the code as a script will run all code that is in the code window (or all of the code that you have selected).


Now I have a huge, HUGE, really HUGE headache from bonking my head on the table for the last 5 minutes. People started starring at me and ask if everything's all right. Before the guys with the tight white shirts come and take me for vacation I just would like to say thank you, THANK YOU, THANK YOU... oh, here they are, so, sorry again for my offense thanks to you all for the hel.. hey, let me just finish this post... no I am not making trouble I just have to... what is this injection... take your hands of me... I have to finish this po... ... ...

[Updated on: Thu, 26 August 2010 04:53]

Report message to a moderator

Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473148 is a reply to message #473147] Thu, 26 August 2010 04:58 Go to previous messageGo to next message
pablolee
Messages: 2634
Registered: May 2007
Location: Scotland
Senior Member
Gaaa, just spat my coffee all over the screen. Glad you got it working Bear (and very glad I didn't insult you now, well, not too much Very Happy ) Good luck with your coding (once you get your arms outta the straightjacket.
Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473149 is a reply to message #473148] Thu, 26 August 2010 05:04 Go to previous message
Bear Knuckle
Messages: 5
Registered: August 2010
Junior Member
pablolee wrote on Thu, 26 August 2010 04:58
Gaaa, just spat my coffee all over the screen. Glad you got it working Bear (and very glad I didn't insult you now, well, not too much Very Happy ) Good luck with your coding (once you get your arms outta the straightjacket.


I am glad to make you smile, after our bad start. Smile
Previous Topic: Help with accepting input from the user after executing SQL stmts in PL/SQL procedure
Next Topic: How to Debug and View the Values of DBMS_XMLDOM objects in TOAD v9.0
Goto Forum:
  


Current Time: Wed Oct 01 04:16:42 CDT 2014

Total time taken to generate the page: 0.08985 seconds