| SQL PLUS problem passing variable [message #409707] |
Tue, 23 June 2009 10:05  |
solisdeveloper
Messages: 48
Registered: March 2008
Location: Mexico
|
Member |
|
|
Hi all:
I hope you guys can help me with this issue that I'm having (I already searched the forum and googled it).
I'm trying to store the result of a query into a variable in sql plus. And it seems that i can only do so with a REFCURSOR bind variable other wise i get this error: "item 'ID' is not a cursor"
This is would be my code:
VARIABLE id REFCURSOR
VARIABLE dep REFCURSOR
BEGIN
OPEN :id FOR SELECT emp_id FROM employees WHERE name = &name;
END;
/
PRINT id
So far so good, i'm prompted for a name and if i write JOHN, then I get the id=15 and the value is printed on my screen.
In the next step, I want to continue using that ID variable to perform diferent querys. So i do the following:
BEGIN
OPEN :dep FOR SELECT deptno, deptname, company FROM departments WHERE empid = :ID;
END;
/
And that's when i get the following error in the "WHERE" Clause:
"PL/SQL: ORA-00932: inconsistent datatypes: expected - got CURSER"
Is there another way to implement what I'm trying to do?
Thank you for your precious time!
|
|
|
|
|
|
| Re: SQL PLUS problem passing variable [message #409713 is a reply to message #409708] |
Tue, 23 June 2009 10:21  |
solisdeveloper
Messages: 48
Registered: March 2008
Location: Mexico
|
Member |
|
|
Hi:
I was asked to display results from diferent querys using the same employee id one Query at a time, rather than making a single Query with all the data needed.
Besides, I'm new to SQL Plus, since all this time I've been working with Pl SQL Developer. So what I'm trying to do here is something similar to what i would do in plsql developer, something like this:
DECLARE
id employees.emp_id%TYPE;
dept departments%ROWTYPE;
BEGIN
Select emp_id
INTO id
FROM employees
WHERE name = 'JOHN';
SELECT deptno, deptname, company
INTO dept
FROM departments
WHERE empid = :ID;
RETURN dept;
END;
|
|
|
|
|
|
| Re: SQL PLUS problem passing variable [message #409725 is a reply to message #409718] |
Tue, 23 June 2009 12:26 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
in sqlplus:
set verify off
set linesize 132
accept X prompt "Enter dept no : "
prompt Here are the emps:
select * from emp where deptno = '&X';
prompt Here are the depts:
select * from dept where deptno = '&X';
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Enter dept no : 10
Here are the emps:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Here are the depts:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
