[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

How to pass RECORD as procedure argument

Posted on 2009-04-14
1
Medium Priority
?
1,376 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 2000 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

650 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