Can I use the result of a query as a var in an executable script?

Toad for Oracle
1

Hi,
My first question on this forum!
I have two scripts that I want to combine
Query 1 returns a single numeric value which I cut/paste into the second query
Query 2 consists of about 70 queries, each sub query has the same variable :pdid
the variable is what I paste in once and each sub query uses it
I execute this script and it runs fine

So in my combined script I would like to
run a query using a variable that I enter that gets a single numeric result and somehow put that result into a var
var pqid VARCHAR2(10); ---this is how i define the first variable
exec :pqid := 'Q00426248'; -- this is where I enter the variable

var pdid number(12); --this is the second variable the one re-used in the sub queries

so what I'm trying to do is something like this:
select distinct table.column INTO :pdid;
from
table
where
something = :pqid;-
( trying to insert the result of the first query into the variable)

then I have my first sub query
ttitle Polic
select.....

when I try this I get a syntax error or ORA-01036: illegal variable name/number

2

I just ran this, it worked for me. Seems about like what you are tying to do.

VAR PQID varchar2(10);
BEGIN
:PQID := '1';
END;
/

VAR PDID NUMBER;

BEGIN
SELECT min(deptno) into :PDID
from scott.dept
where dname <> :PQID;
END;
/

SELECT *
FROM emp
where deptno = :PDID ;

3

You can do that using 'column..new_value' like this:

var mynum number
column mynum new_value mynum
select count(*) mynum from sys.user_objects;

PROMPT &mynum

The PROMPT as an example above just outputs the result of the query that fed mynum--in this case, just the count of rows in SYS.USER_OBJECTS.

Any query that outputs a value of the same type as your VAR can be captured into your variable. So, your first query is simply the SQL that feeds your variable. Put the first query at the top of the script to capture the value you want, and then refer to it in subsequent queries. No copy/pasting necessary.

Cheers,
Russ

4

You can also use a single query to feed more than one variable even of different types:

var obj_id number
var obj_type varchar2(23)
column obj_id new_value obj_id
column obj_type new_value obj_type
select object_id obj_id, object_type obj_type from sys.user_objects where object_name = 'USER_USERS';

PROMPT &obj_id
PROMPT &obj_type

Cheers,
Russ

5

Thank you! all responders.
I tried the following amended query and executed but got the following
SP2-0552: Bind variable "pdid" not declared

var pdid number(12)
column pd.policy_detail_id new_value pdid
select distinct pd.policy_detail_id
from
policy po, policy_trans pt, policy_detail pd
where
..
and po.QUOTE_NBR = 'Q00178012';

ttitle Policy
selec
pd.policy_detail_id as POL_DETAIL_ID,
pt.policy_trans_id as POL_TXN_ID
from
...
where
...
and pd.policy_detail_id = :pdid;

6

You are using a :bind_variable when you should be using a &&define_variable.

Try this:

-- Not required.
--var pdid number(12)

-- This defines pdid as a define variable.
column pd.policy_detail_id new_value pdid

-- Now get a value for pdid.
-- BEWARE. If there are more than one row returned, pdid will take the value
-- of the last record in the output.
select distinct pd.policy_detail_id
from
policy po, policy_trans pt, policy_detail pd
where
..
and po.QUOTE_NBR = 'Q00178012';


ttitle Policy
selec
pd.policy_detail_id as POL_DETAIL_ID,
pt.policy_trans_id as POL_TXN_ID
from
...
where
...
-- This line is wrong:
and pd.policy_detail_id = :pdid;
-- This is correct:
and pd.policy_detail_id = &&pdid;

Hope that helped.

Cheers,
Norm. [TeamT]

7

Guys, thanks for your help!!