Re: REF and DEREF (newbie's question)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 30 Jul 1999 11:27:02 GMT
Message-ID: <37b08bc9.174425670@newshost.us.oracle.com>

A copy of this was sent to szhan_at_calum.csclub.uwaterloo.ca (Philip S Zhan) (if that email address didn't require changing) On 29 Jul 1999 21:53:57 GMT, you wrote:

>
>

[snip]

>
>DECLARE
> p1 product_t := product_t(...);
> r1 REF product_t;
> ...
> testobj bulkProdRef;
>BEGIN
> SELECT REF(p1) INTO r1 FROM DUAL; -- CAN NOT DO
> ...
> testobj := bulkProdRef(r1, ...);
> bulkRefOSP(testobj);
>END;
>
>
>The first "SELECT ... FROM DUAL" is OK, but the second one (reversed) is
>not OK.
>
>How can I create an instance (array of REFs) to test the proc?
>
>Philip

In Order to have a REF you must have a row in an OBJECT table. REFs only exist for persistently stored objects in an object table. The example could look like this:

SQL> CREATE TYPE product_t AS OBJECT ( prod_name varchar2(25), cost int );   2 /

Type created.

SQL>
SQL> CREATE TYPE bulkProdRef AS VARRAY(20) of REF product_t;   2 /

Type created.

SQL>
SQL> CREATE TABLE products OF product_t;

Table created.

SQL>
SQL> insert into products values ( product_t('Prod1', 100 ) );

1 row created.

SQL> insert into products values ( product_t('Prod2', 200 ) );

1 row created.

SQL> insert into products values ( product_t('Prod3', 300 ) );

1 row created.

SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> CREATE or REPLACE PROCEDURE bulkRefOSP ( arrOfRef IN OUT bulkProdRef)
  2  AS
  3      prod product_t;

  4 BEGIN
  5 FOR i IN 1..arrOfRef.count LOOP

  6      SELECT DEREF(arrOfRef(i)) INTO prod FROM DUAL;
  7          dbms_output.put_line( prod.prod_name );

  8 END LOOP;
  9 END;
 10 /

Procedure created.

SQL>
SQL> DECLARE
  2 r1 REF product_t;
  3 testobj bulkProdRef := bulkProdRef();   4 BEGIN

  5          for x in ( SELECT REF(p1) r FROM products p1 )
  6          loop
  7                  testobj.extend;
  8                  testobj(testobj.count) := x.r;
  9          end loop;
 10          bulkRefOSP(testobj);

 11 END;
 12 /
Prod1
Prod2
Prod3

PL/SQL procedure successfully completed.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jul 30 1999 - 06:27:02 CDT