Link to home
Start Free TrialLog in
Avatar of Caputo
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%ROWTYPE,
    step_id            batch_error_log.step_id%ROWTYPE,
    error_code         batch_error_log.error_code%ROWTYPE,
    error_msg          batch_error_log.error_msg%ROWTYPE,
    error_trace        batch_error_log.error_trace%ROWTYPE,
    vars               batch_error_log.vars%ROWTYPE,
    comments           batch_error_log.comments%ROWTYPE,
    proc_name          batch_error_log.proc_name%ROWTYPE,
    batch_pgm_name     batch_error_log.batch_pgm_name%ROWTYPE
    );
 */
 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;
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial