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     Oracle stored procedure

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Oracle stored procedure
 Login/Join
 
<kvn>
posted
I know this has been talked about
before in previous topics but I'm
having trouble with it still.
I've also tried using the example
in the iWay PDF, "iWay Data Adapter Administration for UNIX,Windows,
OpenVMS, OS/400, OS/390 and z/OS
Version 5 Release 2.3."
I'm trying to access a procedure
in a Oracle package from WF but I
keep recieving this error message
I haven't been able to fix.
Below is the error message,
WF code, and Oracle package.
We are using WF 532.
>This set of code was quickly
made up to just get WF to call a procedure. Yes I know I can add in WF Smiler
>Error message:<br /> (FOC1380)
STORED PROCEDURE CAN'T BE SUPPORTED -
: Wrong parameter mode
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) (FOC1405) SQL PREPARE ERROR.
WF code:<br /> 
SQL SQLORA
EX WF_FUNCTIONS.ADD_NUM 1,2;
TABLE ON TABLE PCHOLD
END
Oracle package:
 create or replace package 
WF_Functions is
 procedure add_num(num1 in number, 
num2 in number, sum_num out number);
end WF_Functions;
create or replace package 
body WF_Functions i procedure add_num(num1 in number, num2 in number, sum_num out number) is
beginsum_num := num1 + num2;  end;
end WF_Functions;

This message has been edited. Last edited by: <Mabel>,
 
Report This Post
gypsy
Member
posted Hide Post
My understanding is that WF only 'likes' ref cursors. I've returned query results using ref cursor successfully. They may have changed this, I was on v436 when I was testing it.

CREATE OR REPLACE PROCEDURE TestIt (REFCURSOR OUT <REFCUR_TYPE>Wink AS

BEGIN
OPEN REFCURSOR FOR
'SELECT * from employee';
END TestIt;
/

(Course you'll have to define refcur_type somewhere)
 
Posts: 23 | Location: Daytona Beach, FL | Registered: February 07, 2005Report This Post
Cyril Joy
Platinum Member
posted Hide Post
I guess your issue is with the OUT paramater in the Oracle procedure. Try using IN-OUT parameter instead of OUT.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
<kvn>
posted
Ok, I tried using IN OUT but
still no luck. I still recieve the same error
message. I must've tried
everything I could possibly think of.
Error
(FOC1380) STORED PROCEDURE
CAN'T BE SUPPORTED - :
Wrong parameter mode
(FOC1400) SQLCODE IS -1
(HEX: FFFFFFFF)
(FOC1405) SQL PREPARE ERROR.
Fex<br /> 
 
SET SQLENGINE=SQLORA
SQL EX ISRVEDM.WF_FUNCTIONS.ADD_NUM 1,2,3;
TABLE FILE SQLOUT
PRINT *
>ON TABLE HOLD AS MPHOLD
END
Package
 
create or replace package 
WF_Functions is
procedure add_num(num1 in number, 
num2 in number, sum_num in out number);
end WF_Functions;
create or replace package body 
WF_Functions is
 procedure add_num(num1 in number, 
num2 in number, sum_num in out number) 
is  begin
sum_num := num1 + num2;
end;<br />end WF_Functions;

This message has been edited. Last edited by: <Mabel>,
 
Report This Post
Cyril Joy
Platinum Member
posted Hide Post
The return type must be IN OUT and a cursor type. See the modified program:

SET SQLENGINE=SQLORA
SQL EX WF_FUNCTIONS.ADD_NUM 1,2;
TABLE FILE SQLOUT
PRINT *
END

CREATE OR REPLACE PACKAGE wf_functions AS
TYPE ref_cur IS REF CURSOR;
PROCEDURE add_num(num1 IN NUMBER, num2 IN NUMBER, rfc IN OUT ref_cur);
END wf_functions;
/
CREATE OR REPLACE PACKAGE BODY wf_functions IS
PROCEDURE add_num(num1 IN NUMBER, num2 IN NUMBER, rfc IN OUT ref_cur) IS
BEGIN
OPEN rfc FOR SELECT num1 + num2 AS "result" FROM dual;
END;
END wf_functions;
/
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
<mohith>
posted
I have a doubt in this. Does WF only allow ref cursors to be used?? What happens if i have get columns from multiple tables and build some logic in that also, where i cannot use joins alone but need to do some sort of processing?
 
Report This Post
<kvn>
posted
Thanks Cyril Joy & gypsy. It worked. I get it now. It needed both IN OUT and REFCURSOR.
 
Report This Post
Cyril Joy
Platinum Member
posted Hide Post
Mohith,

To my knowledge WebFOCUS will only allow ref cursors as output from SQL procedure to be used in the fex procedure. You need to bring all ur output as cursor type from the Oracle procedure.
 
Posts: 143 | Location: Rochester,NY. | Registered: August 20, 2004Report This Post
<mohith>
posted
Thanks Cyril for the info
 
Report 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     Oracle stored procedure

Copyright © 1996-2020 Information Builders