CODEWITHSUNDEEP
objectplsqlprocedure
smart_cookie
smart_cookie

Reputation: 85

PL/SQL code error for using the function from an object type

I want to use the procedure I declare in type student_typ to print out all the student records using a PL/SQL table. But it does not seem to work.

Here is my codes:

CREATE TYPE student_typ AS OBJECT (
idno        NUMBER,
first_name  VARCHAR2(20),
last_name   VARCHAR2(25),
email       VARCHAR2(25),
phone       VARCHAR2(20),
MAP MEMBER FUNCTION get_idno RETURN NUMBER,
MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY student_typ ));

CREATE TYPE BODY student_typ AS
MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
BEGIN
   RETURN idno;
END;
MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY student_typ ) IS
BEGIN
   -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' ' || first_name || ' ' || last_name);
   DBMS_OUTPUT.PUT_LINE(email || ' ' || phone);
END;
END;
CREATE TABLE student_obj_table OF student_typ;

INSERT INTO student_obj_table VALUES (
student_typ (935, 'Julie', 'Brown', '[email protected]', '1-800-555-1313') ); 

INSERT INTO student_obj_table VALUES (
936, 'Julia', 'Blue', '[email protected]', '1-800-555-1314'); 

SELECT VALUE(s) FROM student_obj_table s
WHERE s.last_name = 'Brown';

The above codes are all correct.

Here is my PL/SQL block:

DECLARE
student student_typ;
cursor find is select * from student_obj_table;
BEGIN -- PL/SQL block for selecting-displaying a student
  for find_rec in find loop
     student:=find_rec.student_typ;
     student.display_details();
  end loop;
END;

I want to know what is wrong with my PL/SQL block that it gives me this error message and how to correct it:

Error report -
ORA-06550: line 6, column 24:
PLS-00302: component 'STUDENT_TYP' must be declared
ORA-06550: line 6, column 6:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Upvotes: 0

Views: 390

Answers (2)

Chocolim
Chocolim

Reputation: 56

I use this way, and we use a lot of code this way: Where detalleCuentas is a table of the type detalleCuenta

FOR idx IN detalleCuentas.resumenCuentas.FIRST .. detalleCuentas.resumenCuentas.LAST LOOP
BEGIN cuenta := detalleCuentas.resumenCuentas(idx); dbms_output.put_line(' CUENTA ' || idx); dbms_output.put_line(' NroCuenta: ' || cuenta.nroCuenta); dbms_output.put_line(' CodCuenta: ' || cuenta.codigoCuenta); dbms_output.put_line(' TipoCuenta: ' || cuenta.tipoCuenta); dbms_output.put_line(' DescTipoCuenta: ' || cuenta.descTipoCuenta); dbms_output.put_line(' Denominacion: ' || cuenta.denominacion); dbms_output.put_line(' Moneda: ' || cuenta.codMoneda); dbms_output.put_line(' Sald o Actual: ' || cuenta.saldoActual);
END; END LOOP;

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132710

Nobody uses table of object_type in real life, but I managed to get this working. You need to use the same "select value(s)" syntax that you used in your first script. Give that an alias (I used x) then you can reference it in the loop:

DECLARE
student student_typ;
cursor find is select value(s) x from student_obj_table s;
BEGIN -- PL/SQL block for selecting-displaying a student
  for find_rec in find loop
    student:=find_rec.x;
    student.display_details();
  end loop;
END;
/

Alternatively, you could still use "select *" but then you have to assign the elements individually:

DECLARE
  student student_typ;
  cursor find is select * from student_obj_table s;
BEGIN -- PL/SQL block for selecting-displaying a student
  for find_rec in find2 loop
    student:= student_typ(find_rec.idno, find_rec.first_name, find_rec.last_name, find_rec.email, find_rec.phone);
    student.display_details();
  end loop;
END;
/

This is because "select *" breaks out the attributes of the object:

SQL> select * from student_obj_table;

      IDNO FIRST_NAME  LAST_NAME        EMAIL                     PHONE
---------- ----------- ---------------- ------------------------- ----------------
       935 Julie       Brown            [email protected]        1-800-555-1313
       936 Julia       Blue             [email protected]         1-800-555-1314

But "select value(...)" returns the whole object:

SQL> select value(s) x from student_obj_table s;

X(IDNO, FIRST_NAME, LAST_NAME, EMAIL, PHONE)
--------------------------------------------------------------------------
STUDENT_TYP(935, 'Julie', 'Brown', '[email protected]', '1-800-555-1313')
STUDENT_TYP(936, 'Julia', 'Blue', '[email protected]', '1-800-555-1314')

Upvotes: 1

Related Questions