Sql Server -Dynamic Cursor

Posted by Sksamantaray under Sql Server on 12/16/2011 | Points: 10 | Views : 2052 | Status : [Member] | Replies : 1
What is Dynamic Cursor, can somebody explain it with an real example

Thanks,
Sanjay



Responses

Posted by: Gow.net on: 12/17/2011 [Member] Starter | Points: 25

Up
0
Down
i get this example from net
create or replace package dynamic_cursor is


type t_crs is ref cursor;

procedure dyn_sel (
tab_name in varchar2,
field_name in varchar2,
val in varchar2,
crs in out t_crs);

procedure openCursor;

end dynamic_cursor;
/

create or replace package body dynamic_cursor as
procedure dyn_sel (
tab_name in varchar2,
field_name in varchar2,
val in varchar2,
crs in out t_crs)

is
stmt varchar2(100);
begin
stmt := 'select * from ' || tab_name || ' where ' || field_name || ' = :1 ';

open crs for stmt using val;
end dyn_sel;

procedure openCursor is
tc t_crs;
f1 varchar2(50);
f2 varchar2(50);
begin
dyn_sel('test_for_cursor','a','two',tc);
loop
fetch tc into f1,f2;
exit when tc%notfound;
dbms_output.put_line(f2);
end loop;
end openCursor;

end dynamic_cursor;
/

begin
dynamic_cursor.openCursor;
end;
/


gowthaman8870226416

Sksamantaray, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response