Calling Oracle DB Function from PS Query Manager

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

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.

 

Read More About  PS Query Manager