Caputo
asked on
How to pass RECORD as procedure argument
I am trying to pass a collection to a procedure. Cannot get the proc to compile, get error about input variable. We are not using packages so I tried to define locally but no go. Here it is,
CREATE OR REPLACE PROCEDURE LOG_ERROR(in_log) IS
PRAGMA AUTONOMOUS_TRANSACTION;
/*
TYPE in_log IS RECORD (
run_id batch_error_log.run_id%ROW TYPE,
step_id batch_error_log.step_id%RO WTYPE,
error_code batch_error_log.error_code %ROWTYPE,
error_msg batch_error_log.error_msg% ROWTYPE,
error_trace batch_error_log.error_trac e%ROWTYPE,
vars batch_error_log.vars%ROWTY PE,
comments batch_error_log.comments%R OWTYPE,
proc_name batch_error_log.proc_name% ROWTYPE,
batch_pgm_name batch_error_log.batch_pgm_ name%ROWTY PE
);
*/
BEGIN
INSERT INTO batch_error_log VALUES (
in_log.run_id,
in_log.step_id,
in_log.error_code,
in_log.error_msg,
in_log.error_trace,
in_log.vars,
in_log.comments,
in_log.proc_name,
in_log.batch_pgm_name);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (' run id = ' || in_log.run_id);
dbms_output.put_line (' step id = ' || in_log.step_id);
dbms_output.put_line (' error code = ' || SQLCODE);
dbms_output.put_line (' error message = ' || SQLERRM);
dbms_output.put_line (' stack trace = ' || DBMS_UTILITY.FORMAT_ERROR_ BACKTRACE) ;
dbms_output.put_line (' vars = ' || in_log.vars);
dbms_output.put_line (' comments = ' || in_log.comments);
dbms_output.put_line ('procedure name = ' || in_log.proc_name);
dbms_output.put_line ('batch pgm name = ' || in_log.batch_pgm_name);
END;
CREATE OR REPLACE PROCEDURE LOG_ERROR(in_log) IS
PRAGMA AUTONOMOUS_TRANSACTION;
/*
TYPE in_log IS RECORD (
run_id batch_error_log.run_id%ROW
step_id batch_error_log.step_id%RO
error_code batch_error_log.error_code
error_msg batch_error_log.error_msg%
error_trace batch_error_log.error_trac
vars batch_error_log.vars%ROWTY
comments batch_error_log.comments%R
proc_name batch_error_log.proc_name%
batch_pgm_name batch_error_log.batch_pgm_
);
*/
BEGIN
INSERT INTO batch_error_log VALUES (
in_log.run_id,
in_log.step_id,
in_log.error_code,
in_log.error_msg,
in_log.error_trace,
in_log.vars,
in_log.comments,
in_log.proc_name,
in_log.batch_pgm_name);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line (' run id = ' || in_log.run_id);
dbms_output.put_line (' step id = ' || in_log.step_id);
dbms_output.put_line (' error code = ' || SQLCODE);
dbms_output.put_line (' error message = ' || SQLERRM);
dbms_output.put_line (' stack trace = ' || DBMS_UTILITY.FORMAT_ERROR_
dbms_output.put_line (' vars = ' || in_log.vars);
dbms_output.put_line (' comments = ' || in_log.comments);
dbms_output.put_line ('procedure name = ' || in_log.proc_name);
dbms_output.put_line ('batch pgm name = ' || in_log.batch_pgm_name);
END;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.