If I run this procedure from TOAD suppl_load_main MY package is executed,
I need to be able to handle this to the user, so they can run it from our application, I am using a shl script to call this precedure, I done this before and it works, I never done using the main precedure from a package where the main call other precedures, I know I am doing the right stuff in the application registering the
jobs, creating the links for the shl etc...
I guess the question is, it is possible to call the Main precedure(that call other precedures) from the shl script, or what I am doing wrong
Thank you
PROCEDURE suppl_load_main (
p_one_up_number IN NUMBER,
p_process_name IN VARCHAR2,
p_audit_or_update IN VARCHAR2,
p_user IN VARCHAR2 DEFAULT NULL
)
IS
/**********************************************************************************
Modification History
08/11/2009 Creator:
This is the precedure that will execute the all package, there are not parameters,
the package just need to be executed from this precedure.
Changes:
.
**************************************************************************************/
p_applicant_main_err_code VARCHAR2 (2000);
p_main_err_code NUMBER;
p_ora_err_msg VARCHAR2 (2000);
p_insert_err_code VARCHAR2 (2000);
p_ora_err_code VARCHAR2 (2000);
p_srtpers_err_code VARCHAR2 (2000);
p_gurmail_err_code VARCHAR2 (2000);
v_out_path VARCHAR2 (2000) := 'MIDD_LOG';
v_out_file VARCHAR2 (2000)
:= 'common_app_supl_load_pkg'
|| '_'
|| TO_CHAR (SYSDATE, 'YYYYMMDDHH');
v_file_handle UTL_FILE.file_type;
p_refresh_err_code VARCHAR2 (20000);
v_email saturn_midd.mail.email_con;
v_mailto saturn_midd.mail.email_address;
v_mailfrom saturn_midd.mail.email_address;
v_mailsubject VARCHAR2 (65) := '';
v_mailmessage VARCHAR2 (32000) := '';
v_database_name VARCHAR2 (10);
BEGIN
SELECT ora_database_name
INTO v_database_name
FROM DUAL;
v_database_name :=
SUBSTR (ora_database_name, 1, INSTR (ora_database_name, '.') - 1);
-- isolate instance name
v_file_handle := UTL_FILE.fopen (v_out_path, v_out_file, 'w');
UTL_FILE.put_line (v_file_handle, v_database_name);
UTL_FILE.put_line (v_file_handle,
' ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MI:SS')
);
UTL_FILE.put_line (v_file_handle, 'Starting the stuu_MISS_LOAD_main...');
srtiden_insert (p_insert_err_code, p_ora_err_code, p_ora_err_msg);
srtaddr_insert (p_insert_err_code, p_ora_err_code, p_ora_err_msg);
srtpers_insert (p_insert_err_code, p_ora_err_code, p_ora_err_msg);
upd_srtpers_day_mon_yr (p_srtpers_err_code,
p_ora_err_code,
p_ora_err_msg
);
srttele_home (p_insert_err_code, p_ora_err_code, p_ora_err_msg);
appl_gurmail_insert (p_gurmail_err_code, p_ora_err_code, p_ora_err_msg);
p_main_err_code := '0';
UTL_FILE.put_line (v_file_handle,
CHR (10) || 'All procedures ended successfully.'
);
IF p_applicant_main_err_code = 0
THEN
UTL_FILE.fremove (v_out_path, v_out_file);
END IF;
v_mailto.email := 'xxxx@xxxxx.edu';
v_mailfrom.email := 'banner@jet.xxxxxxx.edu';
v_mailsubject :=
v_database_name || ': common_app_supl_load_pkg,testing email_1 ';
saturn_midd.mail.MESSAGE
(v_email,
v_mailto,
v_mailfrom,
v_mailsubject,
v_database_name
|| ': *common_app_supl_load_pkg testing email* '
|| CHR (100)
|| 'All procedures ended successfully: '
|| v_out_path
|| '/'
|| v_out_file
);
EXCEPTION
WHEN OTHERS
THEN
p_applicant_main_err_code := '1';
p_ora_err_msg := SUBSTR (SQLERRM, 1, 2000);
p_ora_err_code := SQLCODE;
v_mailto.email := 'xxxxx@middlebury.edu';
v_mailfrom.email := 'banner@jet.xxxxxx.edu';
v_mailsubject := v_database_name || ': commonapp_supl_load_pkg';
saturn_midd.mail.MESSAGE
(v_email,
v_mailto,
v_mailfrom,
v_mailsubject,
v_database_name
|| ': *commonapp_supl_load_pkg ended testing email_2* '
|| CHR (100)
|| 'Output log is on the server MIDD_LOG: '
|| v_out_path
|| '/'
|| v_out_file
);
saturn_midd.mail.message_end (v_email);
END suppl_load_main;
END commonapp_supl_load_pkg;
here is the shl
v_audit_or_update VARCHAR (1) := 'A';
The application needs this parameter...
/* Formatted on 2009/02/06 15:17 (Formatter Plus v4.8.8) */
-- Author:
-- Date Written: August 2009
--
-- Purpose: This script calls the PROCEDURE saturn_midd. suppl_load_main
--
-- Version Control:
--------------------------------------------------------------------------------
SET serveroutput ON SIZE 1000000
DECLARE
v_audit_or_update VARCHAR (1) := 'A';
job_num NUMBER := NULL;
process_name VARCHAR2 (8) := NULL;
v_user VARCHAR2 (30) := NULL;
BEGIN
job_num := TO_NUMBER (&1);
process_name := '&2';
v_user := UPPER ('&3');
-- Retrieve the parameters for this job submission, so they can be passed to
-- the stored procedure.
SELECT UPPER (gjbprun_value)
INTO v_audit_or_update
FROM general.gjbprun
WHERE gjbprun_job = UPPER (process_name)
AND gjbprun_one_up_no = TO_NUMBER (job_num)
AND gjbprun_number = '01';
-- Now call the stored procedure to do the work.
saturn_midd.commonapp_supl_load_pkg.suppl_load_main( job_num,
process_name,
v_audit_or_update,
v_user);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- Put a message in the job log
DBMS_OUTPUT.put_line
('No parameters in Process Run Parameter Table (GJBPRUN)');
END;
/
EXIT;
/