[sqr-users] PeopleSoft SQR, Calling PLSQL dynamically

[Joe Massian <jmassian@yahoo.com>]

My question is, do you know how I can call a PL SQL
package dynamically from a PeopleSoft run control
table?

I am entering the following into the RC table:
LOAD_FS_ZZ_JRNL_LN3_TO_EPM.LOAD_JRNL_LN3
These are my return variables: ($ERROR, $MESSAGE,
#SOURCE_COUNT, #TARGET_BEFORE_LOAD_COUNT,
#TARGET_AFTER_LOAD_COUNT)

My dynamic section looks like this so far..

Begin-SQL
!
Begin
!
  [$EXECUTE_PACKAGE] ;;
!
End;;
!
End-SQL

I build the $EXECUTE_PACKAGE using the following:

Begin-Procedure Get-Input-Filename
!
!  #ifdef debug
  Display 'Entered Get-Input-Filename Procedure.'
!  #endif
!
Begin-Select
!
REPORT_NAME                     &REPORT_NAME
FIELD2                            &FIELD2
!
  Let $Source_File = LTRIM(RTRIM(&REPORT_NAME, ' '),'
')
!
  Let $Source_File = LTRIM(RTRIM(&FIELD2, ' '),' ')
  Show '- Oracle PL/SQL package requested: ' &FIELD2
!
! Build PL/SQL package call for Oracle...
  Let $EXECUTE_PACKAGE =  &FIELD2 || '($ERROR,
$MESSAGE, #SOURCE_COUNT, #TARGET_BEFORE_LOAD_COUNT,
#TARGET_AFTER_LOAD_COUNT)'
  Show '- Oracle PL/SQL Package built: '
$EXECUTE_PACKAGE
!
FROM PS_ZZ_PLSQL_RC_TBL
!
WHERE OPRID        = $PRCS_OPRID
  AND RUN_CNTL_ID = $PRCS_RUN_CNTL_ID
!
End-Select
!
!  #ifdef debug
  Display 'Exiting Get-Input-Filename Procedure.'
!  endif
!
End-Procedure Get-Input-Filename



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users