Home » SQL & PL/SQL » SQL & PL/SQL » Refcursor question
Refcursor question [message #573386] Thu, 27 December 2012 12:22 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
I have a simple program that takes data from testq and stores in a record
I have to populate myind in such a way that the first and last values of the
output (1 and 4) should be assigned with 'Y' and remaining ones (2,3) with 'N'
Is there any ref cursor attribute with wich I can achieve this ?

create table testq(x number);

insert into testq values (1);
insert into testq values (2);
insert into testq values (3);
insert into testq values (4);

commit;

create or replace procedure p_testq1
is

myind varchar2(1);

type t_trc is ref cursor;
trc t_trc;

v_sql varchar2(2000);

-- record to which data goes into
type t_prec is record(x testq.x %type);
prec t_prec;


-- flow of data, is from v_sql --> plsql record 

begin
v_sql := 'select x from testq order by x';



open trc for v_sql; -- SQL STMT TO ref cursor


LOOP
FETCH trc INTO prec ; -- ref cursor to plsql record
exit when trc%notfound;


dbms_output.put_line('prec.x: '||prec.x);
END LOOP;

end;




I have to do this using plsql as my original program is bigger and the above example is a part of that

Thanks
Re: Refcursor question [message #573387 is a reply to message #573386] Thu, 27 December 2012 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59502
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not clear what you want in the end.
If your test case is not representative of your actual problem then it us just a waste of time for bith of us to try to solve the test case.

SQL> with 
  2    data as (
  3      select x, 
  4             row_number() over (order by x) rn1,
  5             row_number() over (order by x desc) rn2
  6      from testq
  7    )
  8  select x, 
  9         case when rn1=1 or rn2=1 then 'Y' else 'N' end v
 10  from data
 11  order by x
 12  /
         X V
---------- -
         1 Y
         2 N
         3 N
         4 Y

Regards
Michel
Re: Refcursor question [message #573388 is a reply to message #573387] Thu, 27 December 2012 12:47 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
Apologize for not being clear, I need the value of myind to be determined so I have to
call another procedure with its value.

As this is a program and I am processing row by row using ref cursor, not sure how I can
achieve this.
create or replace procedure p_testq1
is

myind varchar2(1);

type t_trc is ref cursor;
trc t_trc;

v_sql varchar2(2000);

-- record to which data goes into
type t_prec is record(x testq.x %type);
prec t_prec;


-- flow of data, is from v_sql --> plsql record 

begin
v_sql := 'select x from testq order by x';



open trc for v_sql; -- SQL STMT TO ref cursor


LOOP
FETCH trc INTO prec ; -- ref cursor to plsql record
exit when trc%notfound;


dbms_output.put_line('prec.x: '||prec.x);

 [b]call_procedure2(myind);[/b]END LOOP;

end;

Re: Refcursor question [message #573389 is a reply to message #573388] Thu, 27 December 2012 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59502
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As this is a program and I am processing row by row using ref cursor


Are you sure you can't do other way: embedding the procedure call or even the procedure source into the query?

Regards
Michel
Re: Refcursor question [message #573390 is a reply to message #573389] Thu, 27 December 2012 13:09 Go to previous message
Michel Cadot
Messages: 59502
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Note that this does not change anything to my previous answer: just replace your query by mine.
And as your query is static you don't need any ref cursor, just a cursor loop.

Regards
Michel

Previous Topic: DBMS_CRYPTO hash a BLOB in a union over dblink
Next Topic: Passing input parameter to a like variable in procedure
Goto Forum:
  


Current Time: Fri Oct 31 02:08:45 CDT 2014

Total time taken to generate the page: 0.21708 seconds