This content has been marked as final. Show 8 replies
Don't confuse tools with the platform, it's Oracle vs SQL Server, but a few things:
You could run something like this:
SELECT * FROM TABLE WHERE FIELD= :VALUE_ID;
VALUE_ID gets created as a bind variable automatically - when you execute, SQL Developer will prompt you for the value.
Thanks for the help.
I had tried that approach earlier. That prompts me to enter the value in another input box. I want value to be defined in a variable in the same script. The question is tool oriented since the data shown in grid format is tool specific. I want to see the data in tabular form but I also want to use a variable.
Hi,1 person found this helpful
A couple more hints:
1. Read about limitations on bind variables in the Help -> ...Concepts and Usage -> Using the Worksheet -> Script Runner
2. An earlier thread on this topic: Re: Oracle Sql Developer prblem with variable format
So, for your specific case, something like this should work for scripted processing (no automatic bind variable prompting):
Note the VARCHAR2 rather than VARCHAR. The documentation states that only VARCHAR2, NUMBER, and DATE are supported.
VARIABLE VALUE_ID VARCHAR2; EXEC :VALUE_ID := '000000000079246'; SELECT * FROM TABLE WHERE FIELD = :VALUE_ID;
Also, note the bug mentioned in the thread above does not apply to the current production release (3.2.20.09.87).
SQL Developer Team
Thanks Gary. Indeed it works with varchar2 for Run Script.
But the script output is not tabular like Run Statement. Is there any way to show the tabular output?
The script output shows field length according to field size instead of actual data length. Even if it's not tabular, I want it to be easily readable. Most of the fields are more than 256 chars due to the field size.
Well, Run Statement places the query result in the grid (tabular output) but automatically prompts for any bind variables. That is just the way it works. If you create a User Defined Report (in the Reports view under the All Reports node) with a bind variable, it will also automatically prompt for the bind value (although in the report definition you can assign a default value).1 person found this helpful
The only alternative I can think of, possibly not very desirable, would be to create a user defined report with any variables hard-coded in the report's SQL statement, not as bind variables. All these user defined reports are saved in UserReports.xml, found somewhere like (on Windows 7) C:\Users\<youruserid>\AppData\Roaming\SQL Developer\). Instead of editing the script to change a variable's value, you would need to change UserReports.xml. Like, I said, not very desirable.
On the other hand, scripts executed using Run Script can include column formatting statement similar to what SQL*Plus offers. Possibly that might suffice for your case.
Thank you very much. Appreciate the help.
that code ran in SQL*Plus would have the same issue - you can't run a SELECT in an anonymous block w/o selecting the resultset into a variable/container of some sort.1 person found this helpful
Using DECLARE or a BEGIN puts you into PL/SQL mode...hence the error re: the 'INTO'
Thanks.. Good to know that.