A few weeks back a developer posed a question“Can I use an Oracle Function in PS Query Manager?”
Yes, you can and here are the steps.

1.Create your function in Oracle DB

CREATE OR REPLACE FUNCTION TEMP_FUNC(VAR1 IN NUMBER)

RETURN VARCHAR2

IS VAR2 VARCHAR2(48);

BEGIN

SELECT NVL(PROGRAM,’NULL’)

INTO VAR2

FROM V$SESSION

WHERE AUDSID=USERENV(‘SESSIONID’);

RETURN(VAR2);

END;

2.Create a view in PeopleSoft

Oracle_func_ps_qry_1

CREATE VIEW PS_TEMP_VW AS SELECT TEMP_FUNC(10) VARIABLE_NAME FROM DUAL;

3.That’s it! Use your view in Query Manager as shown below.

Oracle_func_ps_qry_2

Oracle_func_ps_qry_3

Posted by Nitin Pai
Comments (0)
November 2nd, 2007

Comments (0)