Skip to Main Content

Oracle Developer Tools for VS Code

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

oracle call a procedure from another procedure parm as object type

User_PGR1XJul 15 2021

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).

Comments

Post Details

Added on Jul 15 2021
3 comments
82 views