Cursor and updating same records inside cursor

Rated 3.97/5 based on 632 customer reviews

Here are the answers to the PL/SQL Challenge questions in last issue’s “Error Management” article: Answer 1: Choices (a), (c), and (d) all raise ORA-00001, also known as DUP_VAL_ON_INDEX inside PL/SQL code.

Answer 2: To make it possible for the plch_proc procedure to compile without error, change “AND” to “OR” in the WHEN clause of the exception section.

A REF CURSOR must only be returned under a specific condition (i.e. Execute(); If I know something is going to be returned? And how do I check at the ASP level whether the ref cursor will have been opened or not? Now, in your ASP code where you intend to use that ref cursor, you need to have an IF statement: if xxx then (use the ref cursor) end if Now, since your ASP program called the PL/SQL procedure in the first place, presumably it knows what value it passed for param2?

If so then the IF logic is if value_passed_as_param2 = 4 then (use the ref cursor) end if If somehow (I can't imagine how!

Hi everyone, I currently have a stored procedure that takes two input parameters and one REF CURSOR output parameter which I am calling from ASP.

Depending upon the values of the two input parameters a REF CURSOR can either be returned or nor returned. IF param1 = 2 /* do something */ ELSE IF param2 = 4 /* Open REF CURSOR for return parameter */ OPEN curs FOR SELECT * FROM temp; END IF; When a REF CURSOR is returned the ASP code is fine and I can display all the records it has within it.

Large updates become much easier to perform when you understand some of the basic principles of database design.

In addition, those objects must adhere to a set of design principles or the database will either work poorly or fail altogether.

The error: Ora OLEDB error '80004005' ORA-24338: statement handle not executed actually occurs at the exact following line: rset = cmd. I would have thought that even if no REF CURSOR was returned (i.e.

the "curs" REF CURSOR isn't initialised with the SELECT statement) the above line would still execute but this doesn't seem to be the case.

) your ASP program doesn't know what value it passed as param2, then you would have to add an extra OUT parameter to the PL/SQL procedure - e.g.

param X OUT NUMBER Then in the PL/SQL procedure you would say: IF param1 = 2 param X := 0; /* do something */ ELSE IF param2 = 4 /* Open REF CURSOR for return parameter */ OPEN curs FOR SELECT * FROM temp; param X := 1; END IF; And in the ASP: if Param X = 1 then (use the ref cursor) end if Thanks Tony, First of all I very much appreciate the help you're giving me.

Leave a Reply