PL/SQL REF CURSOR

pts.
Tags:
SQL
Hi, In the ORACLE DATABASE 10G: DEVELOP PL/SQL PROGRAM UNITS Student Guide, Volume 1, (in case anyone has the book), the following code example is on page 6-12: CREATE FUNCTION annual_sal(emp_id NUMBER) RETURN NUMBER IS plsql varchar2(200) := 'DECLARE '|| ' emprec employees%ROWTYPE; '|| 'BEGIN '|| ' emprec := get_emp(:empid); ' || ' :res := emprec.salary * 12; ' || 'END;'; result NUMBER; BEGIN EXECUTE IMMEDIATE plsql USING IN emp_id, OUT result; RETURN result; END; / I'm not clear on how the PL/SQL engine knows it should return what is in :res. I see that it is a bind variable but the RETURN clause says "RETURN result" not "RES." Can someone please help clear this up for me? Thanks.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hello.
In this example, you are using “execute immediate” to run a dynamic command. In your program unit (function annual_sal) you have 2 variables: “plsql”, and “result”.
Since the variable is “result”, you have to return “result”, because “res” does not exist in your function.
“res” works as a parameter inside the dynamic command, to which you are passing “emp_id” and “result” values.
Because “result” is sent as an OUT parameter, it receives the value of “res” from the dynamic command, and that value is returned.
You have the same situation with “empid”. In your function, outside the dynamic command, “empid” does not exist.
For the function, “res” and “empid” are just words inside a string (“plsql”).

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following