Solved

How to pass RECORD as procedure argument

Posted on 2009-04-14
1
1,349 Views
Last Modified: 2013-12-18
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;
0
Comment
Question by:Caputo
1 Comment
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 24142915
I think you are trying to pass a record, a collection is a table-structure consisting of  a number of records or a number of values.
The type of your parameter must be known externaly.
I assume there exists a table view batch_error_log.
If table or view batch_error_log consist complete of the colums you need in your record-argument
you couldt use

CREATE OR REPLACE PROCEDURE LOG_ERROR(in_log batch_error_log%ROWTYPE) IS

you then can omit the type definition in the procedure
If in an other place you have to refer to table.items use
run_id             batch_error_log.run_id%TYPE
instead of
run_id             batch_error_log.run_id%ROWTYPE
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now