PL/SQL: Applying Functions within Ref Cursors.

From: Keith Jamieson <jamiesonk_at_phoenix.ie>
Date: Mon, 4 Oct 1999 12:11:40 +0100
Message-ID: <7ta22c$qe2$1@ezekiel.eunet.ie>

I have created a package as follows:

CREATE OR REPLACE
PACKAGE server_pkg
IS
TYPE curvar_type IS REF CURSOR RETURN server%ROWTYPE; END; and a procedure as follows:

CREATE OR REPLACE
PROCEDURE test_server(curvar_out IN OUT server_pkg.curvar_type) IS
BEGIN
    open curvar_out FOR SELECT serverid,

                                                          dblink,
                                                          servertype,
                                                          priority 
                                            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 follows:

    open curvar_out FOR SELECT a_function(serverid),

                                                               =
b_function(serverid),
                                                              priority 
                                                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. Received on Mon Oct 04 1999 - 06:11:40 CDT