?
Solved

Is it possible to return insert error through ref cursor?

Posted on 2009-02-13
9
Medium Priority
?
304 Views
Last Modified: 2013-12-07
I have a piece of code (below) that inserts rows in the table. My question is: is it possible to return insert error through ref cursor? If so, how should the syntax be in context of my code, i.e. using the pOutputString (commented out in the code)?

TYPE cPAN_CURSOR_Typ IS REF CURSOR;
Oracle version: 9i
PROCEDURE PanRptlogInsert ( pInputString IN VARCHAR2
        ) AS
--        , pOutputString OUT cPAN_CURSOR_Typ) AS
        nStringPntr     NUMBER := 1;
        pRPT_N          NUMBER;
        pRPT_RUN_D      VARCHAR2(30);
...
...
        vsMessage               VARCHAR2(100);
 
    BEGIN
        nStringPntr := string_parse (pInputString, nStringPntr, pRPT_N);
        nStringPntr := string_parse (pInputString, nStringPntr, pRPT_RUN_D);
...
...
        insert into report_log (
 RPT_N
,RPT_RUN_D
...
...
) values (
 pRPT_N
,to_date(pRPT_RUN_D,'Month DD YYYY, hh12:mi AM')
...
...
);
        EXCEPTION
 
        WHEN OTHERS THEN
            vsMessage := 'SQLCODE: ' || SQLCODE || ':' || SQLERRM ;
            APT_Utils.SysLog ('E', 'PAN_Procs.PanRptlogInsert', vsMessage);
 
end PanRptlogInsert;

Open in new window

0
Comment
Question by:laoqi
  • 4
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23670867
eventually, that is possible.
however, I would not do that, instead define a second output parameter to put in the error message...
0
 

Author Comment

by:laoqi
ID: 23670930
Thank you.

How do you catch the insert error? What's the syntax?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 23671044
the EXCEPTION block you have should do the work.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:laoqi
ID: 23671112
I see.  Thank you.

The exception block in my code did not show the error. Is there a more general way?

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23672051
>The exception block in my code did not show the error.

can you clarify?

>Is there a more general way?
no, EXCEPTION block with WHEN OTHERS is the general error handler.

if you did not get into the function code because of invalid parameter values, that's out of control of the function code itself.
0
 

Author Comment

by:laoqi
ID: 23672086
ok. Thank you very much.
0
 

Author Closing Comment

by:laoqi
ID: 31546629
Still don't know how to return the error message through the ref cursor. But I'll settle with the exception block for now.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

864 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