Home » SQL & PL/SQL » Client Tools » Need assistance with passing variable and displaying Select data in TOAD (Oracle 10, WinXP Pro)
icon5.gif  Need assistance with passing variable and displaying Select data in TOAD [message #452572] Thu, 22 April 2010 14:53 Go to next message
bgoldstein14
Messages: 5
Registered: April 2010
Junior Member
I'm going nuts trying to do what I would think should be stupidly simple, but I guess I'm just a bit too stupid to do it (well at least not all of it)!

All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to help me return the result sets in three selects. This is all in TOAD. If I do not have a where clause, the code executes fine (allowing me to see the data in multiple Grid tabs)! But, I want to filter the rows.

I've tried doing a number of different things (finding all sorts of stuff on the Web) like declaring cursor variables and the like but after spinning on this for a couple of hours, I'm stuck.

Here's my attempt (the names have been changed to protect the innocent!):

declare
v1 Numeric;
BEGIN
DO_H_RUN('Me', v1);
--DBMS_OUTPUT.PUT_LINE(v1); --This line works okay!
select * from h_run where h_run_id=v1; --NO GO
select * from h_run_hd where h_run_id=v1; --NO GO
select * from h_run_pos where h_run_id=v1; --NO GO
END;

Help! Thanks in advance.

[Updated on: Thu, 22 April 2010 14:55]

Report message to a moderator

Re: Need assistance with passing variable and displaying Select data in TOAD [message #452573 is a reply to message #452572] Thu, 22 April 2010 15:05 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Please realize that we don't have your tables & we don't have your data.
Therefore we can't run, test or improve your posted SQL.

NO GO is not a valid Oracle response & means NOTHING definitive to me.

SELECT inside of PL/SQL must use INTO & return a single row.
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452574 is a reply to message #452572] Thu, 22 April 2010 15:12 Go to previous messageGo to next message
bgoldstein14
Messages: 5
Registered: April 2010
Junior Member
Okay...

First off...you're right NO GO was a bit flippant. The actual error is "PLS-00428: an INTO clause is expected in this SELECT statement".

Second...as I noted in my question, you're incorrect about only a single record returning. If I don't use the WHERE clause (with the v1 variable), I get everything back (as expected).

Third...it would be a bit much to provide all the tables and necessary data. I'm looking for generic variable assistance when it comes to TOAD. You can safely assume the procedure and select's work perfectly fine. I think this assumption should be adequate for those that understand what I'm asking.
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452575 is a reply to message #452574] Thu, 22 April 2010 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
>You can safely assume the procedure and select's work perfectly fine.

OK, have it your way.
Then no problem needs to be solve.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452576 is a reply to message #452572] Thu, 22 April 2010 15:23 Go to previous messageGo to next message
bgoldstein14
Messages: 5
Registered: April 2010
Junior Member
BlackSwan, your response is in poor taste and I reported it as such. You've deliberately taken my last response out of context so you wouldn't have to answer.

I don't think I'm asking an unreasonable question in regards to working with variables and returning recordsets in TOAD. I feel I've given adequate details for someone who's knowledgeable enough about TOAD to respond appropriately. If there's something else specific needed, I'm more than willing to respond with that detail. My basic situation and detail is pretty much as noted in my original post. Perhaps you're the wrong person to answer this question. Perhaps this is the wrong forum.

[Updated on: Thu, 22 April 2010 15:27]

Report message to a moderator

Re: Need assistance with passing variable and displaying Select data in TOAD [message #452578 is a reply to message #452576] Thu, 22 April 2010 15:40 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
both Oracle & I told you what was wrong.
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452579 is a reply to message #452572] Thu, 22 April 2010 15:46 Go to previous messageGo to next message
bgoldstein14
Messages: 5
Registered: April 2010
Junior Member
Not really.

This works (shows data in Toad grid tab):
select * from h_run;

This does NOT work (gives error shown above):
select * from h_run where h_run_id=v1;

WHY? The only difference is one is attempting to use the variable that I just filled (and I know contains an appropriate value) and the other uses no where clause.

When I attempted to use a "select into" for the above statement, into a cursor variable, nothing I tried worked properly (although I could OPEN the select into a cursor variable, I couldn't figure out a way to display it, such as "Print my_cursor" which didn't work).

Hence the reason why I came to this forum.

So, here's my question reformulated:

Can someone please show me how to take a variable that was just filled, and use it as part of a WHERE clause in a proceding SELECT statement and print the results to a Toad grid?
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452580 is a reply to message #452579] Thu, 22 April 2010 16:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
bgoldstein14 wrote on Thu, 22 April 2010 13:46


Can someone please show me how to take a variable that was just filled, and use it as part of a WHERE clause in a proceding SELECT statement ...?


SCOTT@orcl_11g> VARIABLE refcur1 REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v1 NUMERIC;
  3  BEGIN
  4    v1 := 10;
  5    OPEN :refcur1 FOR
  6    SELECT * FROM emp WHERE deptno = v1;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT refcur1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SCOTT@orcl_11g> 

Re: Need assistance with passing variable and displaying Select data in TOAD [message #452581 is a reply to message #452580] Thu, 22 April 2010 16:55 Go to previous messageGo to next message
bgoldstein14
Messages: 5
Registered: April 2010
Junior Member
YES! That worked...thank you Barbara!!!

The funny thing is that I was trying something very similar (about 15 different ways), but if you don't have the declaration just right with or w/o a colon, things don't work quite right.

Awesome!
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452610 is a reply to message #452581] Fri, 23 April 2010 00:28 Go to previous messageGo to next message
Littlefoot
Messages: 21493
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Although you probably solved a problem, perhaps just a remark.
bgoldstein14
All I want to do is run a procedure (DO_H_RUN) that returns a number into "v1" and then use that variable to help me return the result sets in three selects

Why didn't you create a function instead? It wouldn't make it easier to print results from PL/SQL, but you could use easily it in SQL. Here's an example, based on Scott's schema:
SQL> create or replace function do_h_run(par_ename in char)
  2    return number
  3  is
  4    l_deptno number;
  5  begin
  6    select deptno into l_deptno
  7      from emp
  8      where ename = par_ename;
  9    return (l_deptno);
 10  end;
 11  /

Function created.

SQL> select * from emp where deptno = do_h_run('SMITH');

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980       1600                    20
      7566 JONES      MANAGER         7839 02.04.1981       3775                    20
      7788 SCOTT      ANALYST         7566 09.12.1982       3800                    20
      7876 ADAMS      CLERK           7788 12.01.1983       1900                    20
      7902 FORD       ANALYST         7566 03.12.1981       3800                    20

SQL>
Re: Need assistance with passing variable and displaying Select data in TOAD [message #452748 is a reply to message #452610] Fri, 23 April 2010 14:44 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Ignoring the function vs procedure for a moment, TOAD supports SQLplus variables, so you can get a value from one query and use it in subsequent ones like...
SQL>
SQL> column DEPTNO new_value V1
SQL> select deptno from dept where dname='ACCOUNTING';

    DEPTNO
----------
        10

SQL> select ename, deptno from emp where deptno=&V1;
old   1: select ename, deptno from emp where deptno=&V1
new   1: select ename, deptno from emp where deptno=        10

ENAME          DEPTNO
---------- ----------
KING               10
CLARK              10
MILLER             10

SQL>


Run from Toad using SQLplus or "run as script" icon, which gives basically the same output.
SQL> column DEPTNO new_value V1
SQL> select deptno from dept where dname='ACCOUNTING'

    DEPTNO
----------
        10
1 row selected.
old: select ename, deptno from emp where deptno=&V1
new: select ename, deptno from emp where deptno=10
SQL> select ename, deptno from emp where deptno=10

ENAME          DEPTNO
---------- ----------
KING               10
CLARK              10
MILLER             10

3 rows selected.

[Updated on: Fri, 23 April 2010 14:44]

Report message to a moderator

Previous Topic: SP2-0317: expected symbol name is missing
Next Topic: How to create a table in another schema as in existing schema
Goto Forum:
  


Current Time: Wed Aug 21 23:34:23 CDT 2019