This discussion is archived
3 Replies Latest reply: Dec 3, 2013 9:09 AM by 626bdf61-2e0e-4857-867f-e472c17614d8 RSS

Varray of Objects "Bind variable not declared" error.. I don't want a bind variable.

626bdf61-2e0e-4857-867f-e472c17614d8 Newbie
Currently Being Moderated

Hello.

 

This program is supposed to pull values from a table using a loop, and in the loop, put the values in objects in a varray.  I'm new to objects and am stumped trying to get this program to run.  When I attempt to run it in SQL*Plus  I get the following feedback:

"

Type created.

 

Type body created

 

SP2-0552: Bind variable "MY_VARRAY_EMP1" not declared.

"

 

I don't think I even need a bind variable.  Any feedback would be appreciated.  Here's the program:

 

 

 

-- Enable screen I/O

 

 

SET SERVEROUTPUT ON SIZE 1000000

SET VERIFY OFF

 

 

 

 

-- begin object spec

 

 

CREATE OR REPLACE TYPE employee3 AS OBJECT

  (

  ename CHAR (20 char),

  empno NUMBER (4),

  sal NUMBER (10),

 

 

  MEMBER FUNCTION get_ename RETURN CHAR, MEMBER PROCEDURE set_ename (SELF IN OUT NOCOPY employee3),

 

  MEMBER FUNCTION get_empno RETURN NUMBER, MEMBER PROCEDURE set_empno (SELF IN OUT NOCOPY employee3),

 

  MEMBER FUNCTION get_sal RETURN NUMBER, MEMBER PROCEDURE set_sal (SELF IN OUT NOCOPY employee3)

  );

/

 

 

-- begin object body

 

 

CREATE OR REPLACE TYPE BODY employee3 AS

 

 

  -- gets

 

  MEMBER FUNCTION get_ename RETURN CHAR IS

  BEGIN

  RETURN self.ename;

  END;

  MEMBER FUNCTION get_empno RETURN NUMBER IS

  BEGIN

  RETURN self.empno;

  END;

  MEMBER FUNCTION get_sal RETURN NUMBER IS

  BEGIN

  RETURN self.ename;

  END;

 

 

  -- sets

 

  MEMBER PROCEDURE set_ename(SELF IN OUT employee3) IS

  BEGIN

  self.ename := ename;

  END;

 

 

  MEMBER PROCEDURE set_empno(SELF IN OUT employee3) IS

  BEGIN

  self.empno := empno;

  END;

 

 

  MEMBER PROCEDURE set_sal(SELF IN OUT employee3) IS

  BEGIN

  self.sal := sal;

  END;

 

END;

/

 

 

DECLARE

 

 

  TYPE emp_varray IS VARRAY(10) OF EMPLOYEE3;

  my_varray_emp1 EMP_VARRAY;

 

 

  -- List of EMPNO's in order of appearance in EMP table (for cross-referencing, single-line retrieval)

  TYPE MYCREF_VARRAY IS VARRAY(10) OF NUMBER(4);

  varray_mycref MYCREF_VARRAY := MYCREF_VARRAY(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

 

 

  this_object EMPLOYEE3;

 

  -- make a variable to store one empno

  thisno NUMBER(4);

 

  -- make a counter

  counter INT;

 

  -- query variables for the set calls

  q_ename CHAR(20 CHAR);

  q_empno NUMBER(4);

  q_sal NUMBER(10);

 

 

  my_result INT;

 

 

BEGIN

  --my_varray_emp1 := EMP_VARRAY(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

 

 

  -- Put the first 10 EMPNO's in my cref array

  SELECT empno BULK COLLECT INTO varray_mycref FROM emp WHERE ROWNUM < 11;

 

 

  -- Use a loop to retrieve the first 10 objects in the "emp" table and put them in the varray of objects

 

 

  q_ename := NULL;

  q_empno := NULL;

  q_sal := NULL;

  my_result := NULL;

 

  this_object := NULL;

 

 

 

  counter := 1;

  FOR counter IN 1..10 LOOP

 

 

  thisno := varray_mycref(counter);

 

  this_object := my_varray_emp1(counter);

 

  SELECT ename INTO q_ename FROM emp WHERE empno = thisno;

  my_result := this_object.set_ename(q_ename, NULL);

 

  SELECT empno INTO q_empno FROM emp WHERE empno = thisno;

  my_result := this_object.set_empno(q_empno, NULL);

 

 

  SELECT sal INTO q_sal FROM emp WHERE empno = thisno;

  my_result := this_object.set_sal(q_sal, NULL);

 

 

  END LOOP;

 

 

  -- Use another loop to display the information in the reverse order.

  FOR counter in REVERSE 1..10 LOOP

 

 

  this_object =: my_varray_emp1(counter);

 

 

  dbms_output.put_line((this_object.get_ename()) || CHR(9) || (this_object.get_empno()) || CHR(9) || (this_object.get_sal()));

 

 

  END LOOP;

 

END;

/

  • 1. Re: Varray of Objects "Bind variable not declared" error.. I don't want a bind variable.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    The =: in the following line should be := instead.

     

    this_object =: my_varray_emp1(counter);

     

    That resolves your first error, as Oracle evaluates them from the bottom up, but there are others.  For example, I don't know why you commented out your collection initialization after the begin.

  • 2. Re: Varray of Objects "Bind variable not declared" error.. I don't want a bind variable.
    gaverill Journeyer
    Currently Being Moderated

    Cleaning up your code for errors and eliminating unnecessary complexity...

     

    Add a user-defined constructor which takes all attributes and calls the "setter" procedures in one trip:

     

    -- Enable screen I/O
    set SERVEROUTPUT on size 1000000
    set VERIFY off
    -- begin object spec
    create or replace type employee3 as object
      (
      ename CHAR (20 char),
      empno NUMBER (4),
      sal NUMBER (10),
     constructor function employee3(
        self    in out nocopy    employee3,
        aEname    in        char,
        aEmpNo    in        integer,
        aSal    in        number
      )
      return self as result,
      member function get_ename return CHAR, member procedure set_ename (SELF in out nocopy employee3, ename in char),
      member function get_empno return NUMBER, member procedure set_empno (SELF in out nocopy employee3, empno in integer),
      member function get_sal return NUMBER, member procedure set_sal (SELF in out nocopy employee3, sal in integer)
      );
    /
    -- begin object body
    create or replace type body employee3 as
      constructor function employee3(
        self    in out nocopy    employee3,
        aEname    in        char,
        aEmpNo    in        integer,
        aSal    in        number
      )
      return self as result
      is
      begin
        self.set_ename(aEname);
        self.set_empno(aEmpNo);
        self.set_sal(aSal);
        return;
      end;
      -- gets
      member function get_ename return CHAR is
      begin
      return self.ename;
      end;
      member function get_empno return NUMBER is
      begin
      return self.empno;
      end;
      member function get_sal return NUMBER is
      begin
      return self.sal;
      end;
      -- sets
      member procedure set_ename(SELF in out employee3, ename in char) is
      begin
      self.ename := ename;
      end;
      member procedure set_empno(SELF in out employee3, empno in integer) is
      begin
      self.empno := empno;
      end;
      member procedure set_sal(SELF in out employee3, sal in integer) is
      begin
      self.sal := sal;
      end;
    end;
    /
    

     

    (Since I don't have EMP handy at the moment, create a simple view instead)

     

    create or replace view emp
    as
    select    'EMP' || to_char(level) ename
    ,    level + 100 empno
    ,    DBMS_RANDOM.VALUE(25000,75000) sal
    from    DUAL
    connect by
        level <= 20
    /
    

     

    Get rid of your loop and individual SELECTs, and replace it with a single SELECT BULK COLLECT INTO...

     

    declare
      type emp_varray is varray(10) of EMPLOYEE3;
      my_varray_emp1 EMP_VARRAY;
      this_object EMPLOYEE3;
    begin
      --
      -- No need for a loop. Use SELECT BULK COLLECT INTO, together with a user-defined constructor call (since the
      -- user-defined constructor overrides the default constructor we need to call it using named-parameter notation):
      --
      select    new employee3(
                aEname    => e.ename,
                aEmpNo    => e.empno,
                aSal    => e.sal
                )
      bulk collect into
            my_varray_emp1
      from        emp e
      where        rownum <= 10;
      -- Use another loop to display the information in the reverse order.
      for counter in reverse 1..10 loop
      this_object := my_varray_emp1(counter);
      dbms_output.put_line((this_object.get_ename()) || chr(9) || to_char(this_object.get_empno()) || chr(9) || to_char(this_object.get_sal()));
      end loop;
    end;
    /
    

     

    EMP10         110    60110
    EMP9          109    67485
    EMP8          108    58242
    EMP7          107    47597
    EMP6          106    58995
    EMP5          105    49098
    EMP4          104    47406
    EMP3          103    67574
    EMP2          102    59663
    EMP1          101    52929

     

    PL/SQL procedure successfully completed.

     

    Gerard

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points