Can a SQL Function be executed within WebFOCUS, not as an SQL passthru. We have an MFD created for an Oracle View; however the View requires a function to be executed: siscvt.fsu_sf_szvlfos_majr_term('200801') = '0'
Or, or and, can it be put right into the MFD with a variable for the term code, as like a DEFINE ?
Thanks

Rick,

I don't believe that there is any way to call an Oracle function from WebFocus (but the gurus will let me know if there is).

You can call an Oracle procedure however, that could in turn call the Oracle function. Here's how you call an Oracle procedure from within WF:

SQL SQLORA SET SERVER your_server_name

SQL SQLORA 
EX ora_proc_name
END

You can as you say create an equivalent DEFINE statement in WF to replace the functionality of the Oracle function.

Alternatively, can you not simply call the Oracle function from within your view?

Perhaps you can provide more details that will help me or others better answer your question.

Regards,
Sean

We tried going down the smiths route of creating a proc that called a function.

It was fine when we called the function from the default proc in sqlplus but I recall we hit problems when we tried to create a named procedure and call the function there because we came upon various Oracle security rules that we never got around.

Here is what our Oracle guru said about this function:
A bit of explanation:

Here is a little more detail on the implementation and usage of these “parameterized” views. We’ll use the SZVLFOS_MAJORS view as an example:

Objects associated with the view:

The database package FSU_SK_VIEW_PARAMS, and . . .
the function FSU_SF_SZVLFOS_MAJR_TERM(term IN VARCHAR2 DEFAULT NULL).

Necessity here was the mother of invention. The code for obtaining a student's primary major was rather cumbersome, producing the desire for a view. However, a student's primary major is based on term so that, while I can join this code to a select from STVTERM to display major records by term for a single student, I can't even begin to that for any significant population of students - hence the need for a parameterized query.

The term parameter for this query is the variable SZVLFOS_MAJR_TERM stored in the package FSU_SK_VIEW_PARAMS. This variable is both read and written by the function FSU_SF_SZVLFOS_MAJR_TERM. When a term is passed to the function, it sets the package variable to that term and returns '0'. When no parameter is passed to the function it will return the value of the variable SZVLFOS_MAJR_TERM.

So, when a call is made to the view, the function must also be called, thereby passing a value to the view. The view then calls the function again, reading and referencing the value of the package variable.

---
-- You can set and read the package variable like this:
--

SET SERVEROUTPUT ON;
BEGIN
SISCVT.FSU_SK_VIEW_PARAMS.szvlfos_majr_term := '200608';

DBMS_OUTPUT.PUT_LINE(FSU_SK_VIEW_PARAMS.szvlfos_majr_term);
END;



--
-- But, there is a better way:
--

SELECT FSU_SF_SZVLFOS_MAJR_TERM('200608') FROM DUAL;

SELECT FSU_SF_SZVLFOS_MAJR_TERM() FROM DUAL;

-- Watch what happens when we do this:

select FSU_SF_SZVLFOS_MAJR_TERM() from dual
where FSU_SF_SZVLFOS_MAJR_TERM('200608') = '0'
and FSU_SF_SZVLFOS_MAJR_TERM('200701') = '0'

-- . . . and then switch the lines around:

select FSU_SF_SZVLFOS_MAJR_TERM() from dual
where FSU_SF_SZVLFOS_MAJR_TERM('200701') = '0'
and FSU_SF_SZVLFOS_MAJR_TERM('200608') = '0'

-- And then do a select:

SELECT * FROM szvlfos_majors
WHERE sorlcur_pidm = 12345
--------------------------------------------
So i need to excute this 'within' the FOCUS session in order to set the package variable.
This works but I get 0 records because I suspect I'm in two different seeesions. It is like running the SQL above without the:
where FSU_SF_SZVLFOS_MAJR_TERM('200608') = '0'

So I need the syntax for running a package with in WebFOCUS, if that is possible. If not I'll just have to use SQL then format the report with WebFocus.


ENGINE SQLORA SET DEFAULT_CONNECTION GOLD
SQL SQLORA
SELECT FSU_SF_SZVLFOS_MAJR_TERM('200608') FROM SZVLFOS_MAJORS;
END
TABLE FILE SZVLFOS_MAJORS
PRINT *
IF RECORDLIMIT EQ 10
END

Rick,

I don't know of any way to run a package within WF.

The SQL passthru method that you have shown at the bottom should work fine. And as you likely know, you can replace the hard-coded value 200608 with an amper variable to generate the parameter dynamically from WF.

Perhaps someone else will add more info for you.

Sean

Here is a sample on how we called a func/proc in Oracle. Not sure if this is what you need. The sample calls the proc and populates a answer table. We then call another SQL to get the results.

-*Call the Oracle Stored Procedure
SET SQLENGINE=SQLORA
-RUN

SQL SET SERVER dbname
-RUN

SQL SQLORA EX schema1.getdieperwaferrptdata.getrptdata '&NEW_FROM_DATETIME', '&NEW_TO_DATETIME', '&NEW_TOKYODIV', '&NEW_SBU', '&NEW_COMPROD', '&NEW_PROJECT', '&NEW_DIVPROD', '&NEW_LOTNUM', '&PRODN', '&NEW_WFRSIZE';
END

SQL PREPARE TEMPHOLD FOR
SELECT
COMPRODUCTCODE,
PROJECT,
DIVISIONPRODUCT,
BASEPRODUCT,
BUDGETDIEPERWAFER,
STDDIEVALUE,
FINISHEDWAFERS,
FINISHEDDIE,
DIEYIELDLOSSGAIN,
BUDGETDIE,
ACUTALDIEPERWAFER,
DIEPERWAFERRATIO,
REPORTBY
FROM schema1.VM_DIEPERWAFERRPT;
END

TABLE FILE TEMPHOLD
PRINT *
WHERE RECORDLIMIT EQ 5
END

-EXIT

Yea that's pretty much what I thought.
Thanks everyone.

Rick

Nice example with that DUAL. It has given me an idea on how to update Oracle tables via API from wf.

example

TABLE FILE ...
PRINT PARAM1
PARAM2
ON TABLE HOLD FORMAT ORACLE
END

then

SQL SQLORA PREPARE SQLOUT AS
SELECT APIpackage.APIfunction(param1,param2) AS RETURNCODE
FROM WF_ORACLE_USER.HOLD ;
END
-RUN
TABLE FILE SQLOUT
PRINT *
IF RETURNCODE NE 0
END
-RUN
-IF &RECORDS EQ 0 THEN GOTO EXIT ;
-TYPE UPDATE FAILED
-EXIT

Anyone done this in reality?