Question:
Can WebFocus call an Oracle stored procedure that returns an Oracle NESTED TABLE containing a collection of Oracle RECORDS?

Business Background:
We have a business requirement to order the results of a query in a way that is not possible using the ORDER BY clause in the query.
The ordering is based on evaluating several attributes of our business objects in a decision tree.
The decision tree is implemented as a “comparator” function, and our ordering implementation is based on a bubble sort that calls the “comparator” function.

  
--Here's a sample script you can run in an SQLNavigator editor to test the procedure.
--Be sure to turn the “Server Output” on. DECLARE
   my_inv_table wmgappl.inv_sort.inv_table_type; --The return type is an Oracle NESTED TABLE.
   temp_inv_rec wmgappl.inv_sort.inv_rec_type; --The RECORD type that is in the table.
   i NUMBER;
   tableSize NUMBER;
BEGIN
   --Calling the sorting procedure example implementation.
   --We need to be able to call this from inside a .fex. (And possibly hold in a HOLD TABLE?)
   wmgappl.inv_sort.sort_inv_by_query(
      place_holder1=>'x',
      v_inv_table=>my_inv_table
   );
   
   i:=0;
   tableSize:=my_inv_table.COUNT;
   WHILE i<tableSize LOOP
      i:=i+1;
      temp_inv_rec := my_inv_table(i);
      DBMS_OUTPUT.put_line('INV_TABLE('||i||'): hlPk='||temp_inv_rec.hl_pk
            ||', woPk='||temp_inv_rec.wo_pk
            ||', legNum='||temp_inv_rec.leg_n
            ||', frTo='||temp_inv_rec.fr_to_x
            ||', prevHlpk='||temp_inv_rec.prev_hl_pk
            ||', nextHlpk='||temp_inv_rec.next_hl_pk
            ||', cmpltDate='||temp_inv_rec.cmplt_t
            ||', tkId='||temp_inv_rec.tk_id
      );
   END LOOP;
END;

This message has been edited. Last edited by: Kerry,

Hi prodrigu,

This looks like a more involved issue, so the best channel for assistance would be to open a case with Customer Support Services. Please call at 1-800-736-6130, or access online at InfoAssist.

Cheers,

Kerry

prodrigu, as far as I now all WebFOCUS expects when querying an Oracle stored procedure is a REF CURSOR it can fetch from, so you'll have to handle this within the stored procedure itself.

One idea that comes to mind would be to assign your NESTED TABLE structure to the "returning" REF CURSOR in your stored procedure.

You'll have to check Oracle's documentation on that for particular details and examples but this is usually the way a collection object is assigned to a cursor in Oracle PL/SQL:

OPEN my_ref_cursor FOR
SELECT * FROM TABLE(CAST(my_collection AS my_collection_type));

Once your data is wrapped by the REF CURSOR, WebFOCUS will just "see" it.

Hope this helps.

Hmm, given your sample code I'll dare to say that this may work:

OPEN my_ref_cursor FOR
SELECT * FROM TABLE(CAST(my_inv_table AS wmgappl.inv_sort.inv_table_type));

Replace my_ref_cursor with the REF CURSOR output parameter in your stored procedure as required by WebFOCUS.

I am getting this error :

(FOC1400) SQLCODE IS 6550 (HEX: 00001996)
: ORA-06550: line 1, column 41:
: PLS-00103: Encountered the symbol "," when expecting one of the
: following:
: ( ) - + case mod new not null select
:
: table continue avg count current exists max min prior sql
: stddev sum variance execute multiset the both leading
: trailing forall merge year month day hour minute second
: timezone_hour timezone_minute timezone_region timezone_abbr
: time timestamp interval date
L (FOC1405) SQL PREPARE ERROR.



At procedure i have this ref cursors:

type HLHeader_type is ref cursor return recHLHeader;
type HLMain_type is ref cursor return recHLMain;

SET SQLENGINE = SQLORA

SQL SET SERVER bzmkttst

SQL SQLORA

ex svstst.pack1.proc1 1;

TABLE FILE SQLOUT
PRINT *
END