I am un-sure as to your total design, but I will attempt
to help the best way I can.
You have a couple of options:
1- Write the output of those functions into a temporary table
and open the cursor on the temporary table.
2- You could also try selecting the function results from dual.
(I do not think this will work with your cursor variables)
I really think it will be easier to write the function output
to a results table and open the cursor on the results table.
Let me know if you need further assistance.
David Miller
>
> I have created a package as follows:
>
> CREATE OR REPLACE=20
> PACKAGE server_pkg
> IS
> TYPE curvar_type IS REF CURSOR RETURN server%ROWTYPE;
> END;
>
> and a procedure as follows:
>
> CREATE OR REPLACE=20
> PROCEDURE test_server(curvar_out IN OUT
server_pkg.curvar_type)
> IS
> BEGIN
> open curvar_out FOR SELECT serverid,
>
dblink,
>
servertype,
>
priority=20
> FROM server
> END;
>
> And I am calling and displaying the results as follows.
>
> declare
> keith_cursor server_pkg.curvar_type;
> keith_rec server%ROWTYPE;
> begin
> DBMS_OUTPUT.ENABLE;
> test_server(keith_cursor);
> LOOP
> FETCH keith_cursor INTO keith_rec;
> EXIT WHEN keith_cursor%NOTFOUND;
> DBMS_OUTPUT.PUT_LINE(keith_rec.serverid);
> DBMS_OUTPUT.PUT_LINE(keith_rec.dblink);
> DBMS_OUTPUT.PUT_LINE(keith_rec.servertype);
> DBMS_OUTPUT.PUT_LINE(keith_rec.priority);
> DBMS_OUTPUT.PUT_LINE('Hello Keith');
> END LOOP;
> end;
>
> Everything here works okay.
> What I want to do is to have the cursor defined as=20
> follows:
>
> open curvar_out FOR SELECT a_function(serverid),
>
=
> b_function(serverid),
>
priority=20
> FROM server
>
> ORDER BY priority;
>
> The functions return varchar2 values. serverid is a numeric
column.
> I am using Oracle version 7.3.4 on NT.
>
> Can someone please tell me how this can be achieved. I need
to be able =
> to return the output of these functions in a result set.
>
>
>
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=6617&cus_id=USENET&qtn_id=3986
Received on Mon Nov 01 1999 - 08:45:33 CST
