oracle call a procedure from another procedure parm as object type

User_PGR1X

HI ,

I have a procedure which does the insert part and when the error occures (Exception when other) I need to insert the error details by calling error-log SP.

All the details needed are put in to a list of object variable may i know how can I pass it and get a response back also if there is any error in the error log insert.

example:

 PROCEDURE INSERT_ACCOUNT (

     sql-response-obj OUT  SQL_RESPONSE_OBJ,

     error_log_obj    OUTE ERROR_LOG_OBJ

  ) AS

  BEGIN

-----

-----

exception

when other

-- passing all the required value to below L_% fields

          SELECT 

              ERROR_LOG_OBJ(l_schema_name        

                            ,l_package_name    

                            ,l_procedure_name   

                            ,l_error_severity_cd  

                            l_sqlmsg       

                            ,l_error_type_cd    

                            ,l_table_name     

                            ,l_error_row_id    

                            ,l_parameter_value_txt

                            ,l_create_user_id

                            ,l_update_user_id)

              INTO  error_log_obj    

              FROM DUAL;

     schema-name..LOG_FULL_ERROR(

        ERROR_LOG_OBJ,

        SQL_RESPONSE_OBJ)

when I call like above, i'm getting below error but when I remove erroor log and its respective codes it working fine with same input.

also please correct me, i'm calling both the objects as 'out' parameter.

06502. 00000 - "PL/SQL: numeric or value error%s"

*Cause:  An arithmetic, numeric, string, conversion, or constraint error

      occurred. For example, this error occurs if an attempt is made to

      assign the value NULL to a variable declared NOT NULL, or if an

      attempt is made to assign an integer larger than 99 to a variable

      declared NUMBER(2).

Billy Verreynne

No need to use the SQL engine to construct an object by a select against dual. Use PL/SQL - do not create unnecessary context switches.

E.g.

-- call object's class constructor directly
errLogObject := new Error_Log_Object( l_schema_name, .... );

As for your error - you posted insufficient details and no error stack trace.

User_PGR1X

Thank you Billy, I have one more question

create or replace TYPE           INP_OBJ AS OBJECT 

(  NAME VARCHAR2(9 CHAR)

  ,ADDRESS1 VARCHAR2(50 CHAR)

  ,ADDRESS2 VARCHAR2(50 CHAR)

  ,CITY VARCHAR2(5 CHAR) );

create or replace TYPE INP-TAB AS TABLE OF INP_OBJ ;

I have 3 rows in the inp-tab and I want to move 2nd row of inp-tab to a blob field (say L-blob-field ).

I dont want to contactenate field by field and move it to l-blob-field. is there a way move the whole record (all column) in to a blob field ?

Billy Verreynne

No, there is no C like memcpy() command that copies memory contents pointed to by one pointer, to another variable's memory pointed by another pointer. It is a bit of an extreme and dangerous approach in a high level application-based language like PL/SQL.

You can however implement a method in type class INP_OBJ that converts the property values of the current instantiated objects to raw, and write this to a BLOB.

Type class methods defined as member functions, are callable from the SQL engine too - assuming it supports SQL data types (no Booleans for example as parameters or return value).

Question though is why? What/who is going to consume that object's property values as binary (BLOB)? And why binary specifically that is dangerously complex and needs to consider issues like word boundaries, endianess, and so on?