 Need assistance with passing variable and displaying Select data in TOAD [message #452572] |
Thu, 22 April 2010 14:53  |
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 #452574 is a reply to message #452572] |
Thu, 22 April 2010 15:12  |
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 #452576 is a reply to message #452572] |
Thu, 22 April 2010 15:23 |
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 #452579 is a reply to message #452572] |
Thu, 22 April 2010 15:46 |
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 #452748 is a reply to message #452610] |
Fri, 23 April 2010 14:44 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
