REF and DEREF (newbie's question)

From: Philip S Zhan <szhan_at_calum.csclub.uwaterloo.ca>
Date: 29 Jul 1999 21:53:57 GMT
Message-ID: <7nqihl$k9c$1@watserv3.uwaterloo.ca>

CREATE TYPE product_t AS OBJECT ( ... ); CREATE TYPE bulkProdRef AS VARRAY(20) of REF product_t;

CREATE TABLE products OF product_t;

CREATE or REPLACE PROCEDURE bulkRefOSP ( arrOfRef IN OUT bulkProdRef) AS

    prod product_t;
...

BEGIN
  FOR i IN 1..idx LOOP
    SELECT DEREF(arrOfRef(i)) INTO prod FROM DUAL; -- CAN DO
...

  END LOOP;
END;

• test the stored procedure:

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 Received on Thu Jul 29 1999 - 16:53:57 CDT