Oracle Tip: Close your Ref Cursor in PL/SQL

Hello Everyone,

There is a way in Oracle that you can close your Ref Cursor in PL/SQL store procedure and still able to retain the value into .NET application. This way you don’t leave unclosed cursor on server side.
------------------------------
Sample Code -- Start
------------------------------

PROCEDURE test(crs_out_cursor out sys_refcursor) AS
crs_error_local sys_refcursor;
mydata sys_refcursor;
rr emp%rowtype;
BEGIN
open crs_error_local for
select * from emp_table;
crs_out_cursor := crs_error_local;

loop
fetch mydata into rr;
exit when mydata%notfound;
end loop;

close crs_error_local;
crs_out_cursor := mydata;
----------------------------------------------------------------------------
--EXCEPITION HANDLER
----------------------------------------------------------------------------
EXCEPTION
when others
then
END test;
-----------------------------
Sample Code -- End
-----------------------------

Enjoy the tip!

Creating XMLHTTPRequestObject - For all Browsers

funciton getXMLHTTPRequest(){
var request = false;

if (window.XMLHTTPRequest){
request = new XMLHTTPRequest();
}else {
if(window.ActiveXObject){
try{
request = new ActiveXObject("Msml2.XMLHTTP");
} catch(err1){
try{
request = [ic:ccc]new ActiveXObject("Microsoft.XMLHTTP");
}catch(err2){
request = false;
}
}
}
}
return request;

Just link my blog with Amazon :)

Hello Everyone...

Please don't forget to visit Amazon links on my blog. Thank You for visiting my blog. I will be posting more useful information.

Bye!

How to write oracle update query without using sub queries

update table3 t3
set (setvaluefield1,setvaluefield2,setvaluefield3) =
(select some_otherfield_from_t2, some2_otherfield_from_t2, '201004' from table2
where exists (select t2.pk_field1,t2.field2,t2.field3
from table1 t1,
table2 t2,
table3 t3
where t3.pk_field1 = t1.pk_field1
and t1.pk_field1 = t2.pk_field1
and t3.field2 = t1.field2
and t1.field2 = t2.field2
and (t3.start_date <= '200808' or t3.t1_init_date = '200808') and (t3.end_pricing_date > '200808'
and t3.t1_end_date >= '200808')
and t1.field3 = t2.field3
and t1.field3 not in ('00',' ')
and t1.field4 = '200808'
and t2.field4 = '200808'
and t2.field5 = 'Y'));

Simple Insert in Oracle with Case Statement

Below is a sample code for insert statement here I have used "case statement" in select and "not exists" in where clause..

insert into table1 t1 (field1,field2)
(select field1, case when field2 = ' ' then field2
else to_char(to_number(field2)
end
from table2 t2
where t2.field1 = t1. field1
and not exists (select 1
from table3 t3
where t3.field1 = t2.field1));

please don't forget to visit: http://www.lendmyspace.com

Select in Oracle vs Select in Sybase

In Oracle I came across situation where I have a select statement in SP which executes fine when data IS Found. But crash when there is data is NOT Found. So if you have any place where you need to continue with the sp even when data is not found then do the exception block in your code when you unit test.
If you don’t do this your sp will crash and it will go to the end of your exception handler block.

Example:

begin
select myfield1 into v_myfield1
from mytable1
where myfield1= value1;
exception
when no_data_found then
v_myfield1:=NULL;
end;

I think in Sybase the select query will works fine if data is not found. But in oracle world you do need to take care of it.

Hope this helps!

SQL/PL Update Statement and Sub Update Statement Findings

OK. I have done the update using sub inner update statement as shown below.

But when ever code (A) is executed it crash in store procedure because the conditions are not meet and sql execute update statement.

(A)

update (select t1.field1, t2.field2

from table2 t2

where t1.field2 = t2.field2) myupdatedtable t3

set myupdatedtable.field1 = myupdatedtable.field2;

so I have changed to (B) this way it runs fine without any problem and does not crash.

(B)

update table1 t1

set t1.field1 = (select t2.field1

from table2 t2

where t1.field2 = t2.field2)

where exists (select t2.field1

from table2 t2

where t1.field2 = t2.field2) ;

Hope this helps!