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     Execute a SQL function within WebFOCUS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Execute a SQL function within WebFOCUS
 Login/Join
 
Platinum Member
posted
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


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Platinum Member
posted Hide Post
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


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Master
posted Hide Post
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.



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Platinum Member
posted Hide Post
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


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Platinum Member
posted Hide Post
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


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report This Post
Platinum Member
posted Hide Post
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


Dev: WebFOCUS 7.6.10, Data Migrator 7.6.10
QA: WebFOCUS 7.6.10, Data Migrator 7.6.10
Prod: WebFOCUS 7.6.2, Data Migrator 7.6.8
Windows 2K3, Tomcat 5.5.17, IIS 6
Usage: HTML, PDF, Excel, Self-serve, BID and MRE
 
Posts: 197 | Location: Roseville, CA | Registered: January 24, 2005Report This Post
Platinum Member
posted Hide Post
Yea that's pretty much what I thought.
Thanks everyone.


Reporting Server 7.6.10
Dev. Studio 7.6.8
Windows NT
Excel, HTML, PDF
 
Posts: 204 | Registered: March 31, 2008Report This Post
Master
posted Hide Post
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?



Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 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     Execute a SQL function within WebFOCUS

Copyright © 1996-2020 Information Builders