All of the documentation in WF for calling stored procedure/package members seems to be orientated around calling procedures which don't return a value compared to functions which do.

There seems to be 2 methods within wf to call procs.

1. Calling them using the engine.

Should work on all versions of Oracle but won't produce any output since the equivalent to enabling server message buffer can only be done in SQL Plus.

ENGINE SQLORA
EX SYS.DBMS_OUTPUT.PUT_LINE('HELLO');
END

2. Creating a synonym with fields corresponding to parameters. The result set can be queried by having the OUT parameters returned in SQLOUT and the IN parameters can be set via the wf WHERE clause.

Neither one of these seems to be suitable method for calling a function and retrieving values such as the call to ifsapp.Party_Type_API.Decode.

Here's how I got it to work in SQL plus (though I think you need to enable the dbms buffer to see the results)

-- this works in SQL plus. not in wf so far
variable   party_to_use_           VARCHAR2(100)
BEGIN
    :party_to_use_        := ifsapp.Party_Type_API.Decode( 'CUSTOMER' );
     DBMS_OUTPUT.PUT_LINE(:party_to_use_);
END;

The question is:

Is there a method in wf to execute a function and return a value to a wf variable?

Best regards

John

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

not sure about this...but could you set the variable equal to the result set from your function?

such as
variable x
x = call myschema.myfunc(myparm);

worth a shot?

the topic of your post is "calling function within a package" an Oracle package is a grouping of UDF's and SP's that is compiled as a single program unit, so you can call the package if you want the whole enchilada, or just single functions within the package (even though it's compiled)

That's an oracle thing, way outside webfocus. Judging by why was actually posted, I'm not seeing how the topic matches the post at all, but google your topic if you need that answer.

hammo1j's question is perfectly straightforward, if you don't know the answer why don't you not put anything on here.
If you don't understand the question why don't you ask.

hammoj, WebFOCUS only supports executing "procedures" (not functions) via Stored Procedures.

It sort of makes sense if one considers that the whole purpose of accessing a data source (via stored procedure or directly) is to be able to fetch records from a result set, and not discrete values. Therefore, if a result set is expected, it seems "easier" t implement a procedure reserving one of its parameters as an output cursor (result set) as not all database vendors would support returning CURSORs from a function.

That being said, if all you need is to return a discrete value such as ifsapp.Party_Type_API.Decode( 'CUSTOMER' ) in your sample code, you can just wrap it in a cursor and assign it to the output parameter of a packaged procedure which will be the one invoked from WebFOCUS.

This is what the package may look like (I did not test this, so bear with me on syntax errors):

create or replace package mypackage as
   type t_mydata is record (
      party_to_use_   varchar2(100);
   );

   type t_refcursor is ref cursor return t_mydata;
end mypackage;
/

create or replace package body mypackage as
   procedure get_party_type (c_out in out t_refcursor) as
   begin
      open c_out for
      select ifsapp.Party_Type_API.Decode( 'CUSTOMER' ) from dual;
   end get_party_type;
end mypackage;

Basically, I am defining a procedure called get_party_type with a ref cursor output parameter as required by WebFOCUS. I am then calling your function API and assigning its return value to the cursor.


Now, from WebFOCUS you should be able to do:

SQL SQLORA
EX mypackage.get_party_type
TABLE FILE SQLOUT
END

You would get a nice "report" with one row and a single column containing the party type "returned" from your procedure.

If you need to use that as an actual variable in your WF code for other purposes, then just HOLD the results as ALPHA and do a -READFILE to put the value in an &variable.

Hope this helps.

I'm sure John figured this out 4 1/2 years ago!
Otto seems to be testing the Kool-aid...

Tom, are you telling me that I was not careful enough to check the date of the original post and pretty wasted my time at this?

Oh well, it was good for me at least ... it served as a refresher