Bind variable when executing as script

Toad for Oracle
1

Hi All,

I have one query.

Is there a way to bind variables when executing block as script?

2

When toad run SQL as script, it behave like pure Oracle SQL*Plus.

So, what is needed for SQL*Plus it is needed for Toad script.

Example:

SET DEFINE ON;
DEFINE ITEM_NR=04060845;

select &ITEM_NR AS ITEM FROM DUAL;
old 1: select &ITEM_NR AS ITEM FROM DUAL
new 1: select 04060845 AS ITEM FROM DUAL

  ITEM

4060845
In Toad (same code, F5):

old: select &ITEM_NR AS ITEM FROM DUAL
new: select 04060845 AS ITEM FROM DUAL

  ITEM

4060845
1 row selected.

Variables may be even object names:

SQL> DEFINE ATABLE=DUAL
SQL> select 1 FROM &ATABLE;
old 1: select 1 FROM &ATABLE
new 1: select 1 FROM DUAL

    1

    1

SQL>

Hope this helps…

3

Hi Damir. Thanks for the reply. But let me explain the scenario what exactly i want to achieve-

I have an anonymous block of code where i want to display the output of cursor in datagrid.

If i use this block and run as execute statement:

begin

open :cur for select * from table1;

end;

This returns me data in grid when variable type selected as cursor.

But if i execute same block as script in TOAD then it throws error.

I dont want to print the data in DBMS output window. :frowning:

4

Also if i use - &cur as variable it just asks for substituted value, not the variable type i want it to be.

Is there any way to view output in data grid from anonymous block?

5

Maybe I was not clear enough, so I’ll repeat the same sentence:

When toad run SQL as script, it behave like pure Oracle SQL*Plus.

When you run in Toad (F5) you’ll get result like on picture.

VARIABLE employee_info REFCURSOR

BEGIN
** OPEN :employee_info FOR SELECT EMPNO, ENAME FROM EMP WHERE JOB=‘CLERK’ ;**
END;
/

PRINT employee_info;

Hope this helps. Cheers!

6

Thanks Damir. So we cant see data in datagrid. Is there a way around?

7

When you run as a script (F5) it will put the results in the output tab, but next to the output tab is the Grid tab (see Damir’s print screen above).

8

As Denis say yes!

But please read your initial post question. Let me citate you:
“Is there a way to bind variables when executing block as script?”

:slight_smile: