Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Store Procedure - Oracle "Nested Table"

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Store Procedure - Oracle "Nested Table"
 Login/Join
 
Platinum Member
posted
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,


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Expert
posted Hide Post
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


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Guru
posted Hide Post
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
Did you ever get a resolution to this? We are getting the same error
PLS-00103.....

Thanks


WF8
Windows
 
Posts: 117 | Registered: May 28, 2015Report This Post
Platinum Member
posted Hide Post
Trudy,

We decide to use a Web Service Call. Now we are using a FILEDEF to call a HTTP Web Service which runs a query in JAVA and sorts it then returns the data in XML. Then we use a XML adapter to convert the XML data into a temp Master Table which then we can TABLE FILE the data out.

Thanks,
prodrigu


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Guru
posted Hide Post
I still cannot create a master file to that nested oracle procedure, but I am able to access it.


SET SQLENGINE = SQLORA

SQL SET SERVER bzmkttst

SQL SQLORA

ex svstst.pack1.proc1 1;

TABLE FILE SQLOUT
PRINT *
END
  



http://forums.informationbuild...237099876#5237099876


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Store Procedure - Oracle "Nested Table"

Copyright © 1996-2020 Information Builders