# Bind variable when executing as script

**URL:** <https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987>\
**Category:** Toad for Oracle & DevOps Toolkit\
**Created:** [September 19, 2014, 3:20am UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987 "2014-09-19T03:20:59Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nik16](https://avatars.discourse-cdn.com/v4/letter/n/94ad74/32.png) [@Nik16](https://forums.toadworld.com/u/Nik16)\
**Post date:** [September 19, 2014, 3:20am UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/1 "2014-09-19T03:20:59Z")

</div>

Hi All,

I have one query.

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

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [September 19, 2014, 4:32am UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/2 "2014-09-19T04:32:40Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Nik16](https://avatars.discourse-cdn.com/v4/letter/n/94ad74/32.png) [@Nik16](https://forums.toadworld.com/u/Nik16)\
**Post date:** [September 21, 2014, 11:00pm UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/3 "2014-09-21T23:00:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Nik16](https://avatars.discourse-cdn.com/v4/letter/n/94ad74/32.png) [@Nik16](https://forums.toadworld.com/u/Nik16)\
**Post date:** [September 21, 2014, 11:01pm UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/4 "2014-09-21T23:01:58Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [September 21, 2014, 11:59pm UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/5 "2014-09-21T23:59:03Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Nik16](https://avatars.discourse-cdn.com/v4/letter/n/94ad74/32.png) [@Nik16](https://forums.toadworld.com/u/Nik16)\
**Post date:** [September 22, 2014, 6:11am UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/6 "2014-09-22T06:11:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Dennis\_Paulus](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/dennis_paulus/32/138_2.png) [@Dennis\_Paulus](https://forums.toadworld.com/u/Dennis_Paulus)\
**Post date:** [September 22, 2014, 6:21am UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/7 "2014-09-22T06:21:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\
**Post date:** [September 22, 2014, 8:35am UTC](https://forums.toadworld.com/t/bind-variable-when-executing-as-script/30987/8 "2014-09-22T08:35:43Z")

</div>

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**?”

🙂