Solved

How to pass RECORD as procedure argument

Posted on 2009-04-14
1
1,355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select and Insert Query running slow 4 57
Database Design Dilemma 6 65
Row_number in SQL 6 45
populate value based on what is selected in lov 2 29
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

735 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