This content has been marked as final. Show 30 replies
Can you post your get_price procedure/function?
Try using anonymous pl/sql block simple example,
CREATE OR REPLACE FUNCTION getpmnorgrow (empid emp.id%TYPE) RETURN emp%ROWTYPE -- IS result emp%ROWTYPE; BEGIN SELECT * INTO result FROM emp WHERE id = empid; RETURN result; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error (-20100, 'error'); RETURN NULL; END; / DECLARE retval emp%ROWTYPE; empid NUMBER; BEGIN empid := 1; retval := getpmnorgrow (empid); DBMS_OUTPUT.put_line ('employee name=' || retval.name); COMMIT; END;
variable my_cursor refcursor;
exec :my_cursor := get_price('0123','p1,p2,p3');
Thanks for your response.
Edited by: user6773 on Jan 5, 2009 6:05 AM
I get an error msg when I try this. Not sure ..
Whats the error you are getting?
When I try to test it:
select get_price('0123','p1,p2,p3') from dual;
Error at line 1:
ORA-00902: invalid datatype
select get_price('0123','p1,p2,p3') from dual;Why do you have p1,p2,p3 in quote marks?
Is your function expecting a character string here? Or should they be separate parameters?
Yes, it is expecting a string of values for the second parameter (set of products).
I have no idea what your function does, but try this:
SQL> var results refcursor
SQL> exec :results := get_price('0123','p1,p2,p3');
SQL> print results
Thanks for your response. That works. But, I am not sure if this is useful when this function is called by a java application.
But, I am not sure if this is useful when this function is called by a java application.Of course not. You asked why this didn't work in SQL Plus, so this is a SQL Plus solution.
If you're using Java and having trouble, you can post another question.
It would be really, really helpful if you could post your function, as has already been requested!
I could ask you "My car doesn't work start, unless I'm parked on a hill, why not?" but I bet you'd have trouble pinpointing the exact problem that my car has got...
I have plenty of questions.
Why don't you post your actual script here?
a. Your Company Restrict You.
b. You don't want to post.
c. You don't understand what our volunteers have repeatedly ask
d. None of the above.
Solution - In case of ->
a. Then post one dummy solution which also facing the same error prone scenario and ask your requirement clearly.
b. Then hard luck. Our help will be up to this due to your response.
c. Our volunteers are asking repeatedly to post the actual code here. And, again as i've already explained in point (a) - if you have any problem to post then follow solution a.
d. Then try to read oracle documentation and specifically read how collection and ref cursor works and then try to implement that by yourself.
I hope i've covered almost all the points for you.