Home » SQL & PL/SQL » SQL & PL/SQL » Can i pass collection into a ref cursor?
Can i pass collection into a ref cursor? [message #277435] Tue, 30 October 2007 02:11 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi All

I have a varray of values. now i want to pass all the values into a ref cursor. Please anyone help me. my code looks like

declare
type name_rec is record
(first_name varchar2(20),
last_name varchar2(20));
n name_Rec;
type vrec is varray(20) of name_rec;
vrec1 vrec:=vrec();
begin
vrec1.extend; -- allocation of a new element
vrec1(1).first_name:=('smith');
vrec1(1).last_name:=('paul');
vrec1(2).first_name:=('smith2');
end;


Thanks in Advance
Natesh
Re: Can i pass collection into a ref cursor? [message #277438 is a reply to message #277435] Tue, 30 October 2007 02:23 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Absolutely NOT with PL/SQL collections. REF CURSORS reference SQL statements, which cannot see PL/SQL collections.

If it were a database type (CREATE OR REPLACE TYPE...) then you might have a better shot.

Once you have loaded up the database type, you can SELECT from it using a TABLE() function - see the SQL Reference manual. You should then be able to place that SELECT in a REF CURSOR.

Ross Leishman
Re: Can i pass collection into a ref cursor? [message #277439 is a reply to message #277435] Tue, 30 October 2007 02:23 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
First step: format your code.
DECLARE
   TYPE name_rec IS RECORD(
      first_name   VARCHAR2(20)
    , last_name    VARCHAR2(20)
   );

   n       name_Rec;

   TYPE vrec IS VARRAY(20) OF name_rec;

   vrec1   vrec     := vrec();
BEGIN
   vrec1.EXTEND;                                 -- allocation of a new element
   vrec1(1).first_name    :=('smith');
   vrec1(1).last_name     :=('paul');
   vrec1(2).first_name    :=('smith2');
END;


Second step: verify code correctness:
SQL> DECLARE
  2     TYPE name_rec IS RECORD(
  3        first_name   VARCHAR2(20)
  4      , last_name    VARCHAR2(20)
  5     );
  6
  7     n       name_Rec;
  8
  9     TYPE vrec IS VARRAY(20) OF name_rec;
 10
 11     vrec1   vrec     := vrec();
 12  BEGIN
 13     vrec1.EXTEND;                                 -- allocation of a new el
ment
 14     vrec1(1).first_name    :=('smith');
 15     vrec1(1).last_name     :=('paul');
 16     vrec1(2).first_name    :=('smith2');
 17  END;
 18  /
DECLARE
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 16


MHE
Previous Topic: function based index using new_time function
Next Topic: How to alter index
Goto Forum:
  


Current Time: Sat Dec 10 20:51:37 CST 2016

Total time taken to generate the page: 0.23875 seconds