|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: (Substitution Variables) not working in "SQL Developer" Environment !!!!!!! [message #473129 is a reply to message #473126] |
Thu, 26 August 2010 03:54   |
pablolee
Messages: 2882
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 |
Bear Knuckle
Messages: 5
Registered: August 2010
|
Junior Member |
|
|
pablolee wrote on Thu, 26 August 2010 03:54So 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
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 #473134 is a reply to message #473131] |
Thu, 26 August 2010 04:26 |
cookiemonster
Messages: 13959
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 #473141 is a reply to message #473134] |
Thu, 26 August 2010 04:33 |
Bear Knuckle
Messages: 5
Registered: August 2010
|
Junior Member |
|
|
Great, now others get involved and I feel even more ashamed... 
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 |
pablolee
Messages: 2882
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 #473147 is a reply to message #473145] |
Thu, 26 August 2010 04:52 |
Bear Knuckle
Messages: 5
Registered: August 2010
|
Junior Member |
|
|
pablolee wrote on Thu, 26 August 2010 04:38Apology 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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
) Good luck with your coding (once you get your arms outta the straightjacket. 