amaach amaach - 5 months ago 21
SQL Question

APEX to a STORED PROCEDURE

could someone please help convert this apex query to oracle stored procedure? The part I don't understand is the if statement it doesnt return any values. Thanks

DECLARE
P_RESULT VARCHAR2(4000) ;
BEGIN

P_RESULT := 'SELECT /*+ RESULT_CACHE */ D.ORGANIZATION_NAME as ORG_NAME,
UDD.FS_STATEMENT_NUM FS_STATEMENT_NUM,(select DOC_DATE_TIME FROM U_DOCUMENT UD WHERE
UD.FS_STATEMENT_NUM = UUI.FS_STATEMENT_NUM AND UD.MISCELLANEOUS_NUM = ''0'') AS FILE_DATE,

FROM U_DET D,
U_DOCUMENT_DET UDD,

where D.DET_TYPE=''O'' AND UDD.DET_NUM=D.DET_NUM
AND UDD.FS_STATEMENT_NUM=UUI.FS_STATEMENT_NUM
AND (UUI.MAT_DATE > ''' || to_date(local.pkg_bs_common.CHECK_FS_STATUS(:P5_DS_STATUS),'dd-mon-yyyy') || ''' OR UUI.MATURITY_DATE IS NULL)';

if :P5_ORGN_NAME is not null then
P_RESULT := P_RESULT || ' AND (D.ORGANIZATION_NAME LIKE ''' || upper(:p5_ORGN_NAME) || '%'' OR D.FORMATTED_ORGANIZATION_NAME like
''' || local.pkg_bs_common.orgname_format(upper(:p5_ORGN_NAME)) || '%'')';
END IF;


--HTP.P(P_RESULT);
RETURN P_RESULT ;
END;

Answer

In the line if :P5_ORGN_NAME is not null then, the argument :P5_ORGN_NAME is a bind variable for the page five item P5_ORGN_NAME session value. Which, running from apex, you'd pass as a parameter in the block definition.

If you need to call the procedure in a session context you can replace that line with either

if (V('P5_ORGN_NAME') is not null) then

or

if (APEX_UTIL.GET_SESSION_STATE('P5_ORGN_NAME')) is not null then

The same goes to every :P5_ORGN_NAME or :P5_DS_STATUS call in your code.

But before this you should wrap it all with:

if(APEX_CUSTOM_AUTH.IS_SESSION_VALID) then
    /*your code here*/
end if;

To ensure that there's always a valid session.

APEX_CUSTOM_AUTH.IS_SESSION_VALID

APEX_UTIL.GET_SESSION_STATE