Solved

How to pass RECORD as procedure argument

Posted on 2009-04-14
1
1,352 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

770 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