What is equivalent in Oracle SQL Developer for following MS SQL Server SQL written in SQL Server Management Studio?
DECLARE @VALUE_ID VARCHAR(15);
SELECT * FROM TABLE WHERE FIELD=@VALUE_ID;
I tried various combination but SELECT * FROM TABLE WHERE FIELD=@VALUE_ID doesn't seem to be working. It asks for INTO clause.
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.
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):
VARIABLE VALUE_ID VARCHAR2;
EXEC :VALUE_ID := '000000000079246';
SELECT * FROM TABLE WHERE FIELD = :VALUE_ID;
Note the VARCHAR2 rather than VARCHAR. The documentation states that only VARCHAR2, NUMBER, and DATE are supported.
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).
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.
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.
Using DECLARE or a BEGIN puts you into PL/SQL mode...hence the error re: the 'INTO'