Home » SQL & PL/SQL » Client Tools » SQL PLUS problem passing variable
SQL PLUS problem passing variable [message #409707] Tue, 23 June 2009 10:05 Go to next message
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 #409708 is a reply to message #409707] Tue, 23 June 2009 10:09 Go to previous messageGo to next message
BlackSwan
Messages: 21967
Registered: January 2009
Senior Member
why are you alternating between SQL & PL/SQL?

why not do all processing within PL/SQL?

What problem are you really, really trying to solve?
Re: SQL PLUS problem passing variable [message #409713 is a reply to message #409708] Tue, 23 June 2009 10:21 Go to previous messageGo to next message
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 #409718 is a reply to message #409707] Tue, 23 June 2009 10:54 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just merge the two selects into one. Then you don't need to store intermediate results.
Re: SQL PLUS problem passing variable [message #409725 is a reply to message #409718] Tue, 23 June 2009 12:26 Go to previous message
andrew again
Messages: 2574
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>
Previous Topic: view problem
Next Topic: edit file is not working
Goto Forum:
  


Current Time: Wed Apr 23 17:37:09 CDT 2014

Total time taken to generate the page: 0.11658 seconds