Home » SQL & PL/SQL » SQL & PL/SQL » Oracle PL/SQL transferring value from a cursor to a variable
Oracle PL/SQL transferring value from a cursor to a variable [message #421959] Sat, 12 September 2009 07:18 Go to next message
namb
Messages: 35
Registered: September 2009
Member
Hi all,

I am facing the following problem.

I am trying to fetch first the value of a cursor into a variable whose data type is same as that of the value that i am trying to import.

My query is of the type

cursor C1 is select model from table1;
a table1.model%TYPE;
begin
open C1;
loop
fetch c1 into a;
dbms_output.put_line(a);
EXIT WHEN C1%NOTFOUND;
end loop;
declare
b table2.variable2%TYPE;
cursor C2 is select distinct variable2 from table2 where model = a;
begin
open C2;
loop
fetch C2 into b;
dbms_output.put_line(b);
EXIT WHEN C2%NOTFOUND;
end loop;
close c2;
close c1;


Now when i try to fetch the distinct value of variable 2 by equating the field of the table equal to the variable a which i fetched from the cursor earlier it is providing the wrong result. Can you please provide me the solution for the same.
IS the syntax correct can i use the where clause by equating the value of the variable i fetched with earlier cursor

Thanks in advance
Re: Oracle PL/SQL transferring value from a cursor to a variable [message #421960 is a reply to message #421959] Sat, 12 September 2009 07:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

No, syntax is NOT correct.

Your answers are just a few clicks beyond the URL below.
http://tahiti.oracle.com
Let us know when you have found your answer.

Why do this in multi-line PL/SQL when it can be done as a single line SQL statement?

[Updated on: Sat, 12 September 2009 07:40]

Report message to a moderator

Re: Oracle PL/SQL transferring value from a cursor to a variable [message #421961 is a reply to message #421959] Sat, 12 September 2009 09:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There is insufficient infomration to help you. Please do the following:

1) post an actual test case and results. Use SQLPLUS and make sure to take advanatage of the formatting capabilities of this website.

2) be a bit more specific in your problem description. "I get the wrong answer" does not really help us. We do not see any data, we do not see any result, we do not have any description of why the result is wrong for the data.

When you post a question you should ask yourself a simple question: What would I need to solve the problem myself. We are going to need the same stuff and you have not provided any of it.

Provide what I ask and formatt your material, and you will get an answer I am sure of it.

Good luck, Kevin
Re: Oracle PL/SQL transferring value from a cursor to a variable [message #421962 is a reply to message #421959] Sat, 12 September 2009 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
select distinct variable2 from table2 where model in (select model from table1);
Re: Oracle PL/SQL transferring value from a cursor to a variable [message #422070 is a reply to message #421959] Mon, 14 September 2009 03:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your Cursor has no ORDER BY clause to it, and you are looping through the cursor and fetching all the values in the table. The value that gets used in the 2nd part of your code is the value that happened to be the last value fetched by the cursor.
Previous Topic: Chained transaction retrieval query
Next Topic: View Created with Compilation Errors
Goto Forum:
  


Current Time: Sun Dec 11 04:25:39 CST 2016

Total time taken to generate the page: 0.12592 seconds