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.
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).
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.