CODEWITHSUNDEEP
stored-proceduresplsqloracle12crecord
Eric Sousa
Eric Sousa

Reputation: 5

PLSQL - Procedure with multiple records

Create a procedure called prerequisities. Create a user-defined record with four fields: course_no, description, cost, and prerequisite_rec. The last field, prerequisite_rec, should be a user-defined record with three fields: prereq_no, prereq_desc, and prereq_cost. For all the courses that have a prerequisite course, populate the user-defined record with all the corresponding data, and display its information on the screen.

I'm trying to figure out this question and the wording from it is just confusing. From what I'm understanding is that a procedure is to be created along with a record with 4 user defined fields, but 1 of those 4 fields is also a record itself with 3 fields in it.

Is this correct?

Here is what I've got going so far.

CREATE OR REPLACE PROCEDURE prerequisites (course_no IN NUMBER, 
description OUT VARCHAR2, cost OUT NUMBER, prereq_no OUT NUMBER
,prereq_desc OUT VARCHAR2, prereq_cost OUT NUMBER)
IS
v_course course.course_no%TYPE;

BEGIN
v_course := course_no;
SELECT description, cost, prerequisite
  INTO description, cost, prereq_no
  FROM course
 WHERE course_no = v_course;

SELECT description, cost
  INTO prereq_desc, prereq_cost
  FROM course
 WHERE course_no = prereq_no;


END;
/

DECLARE
TYPE prereqs IS RECORD (prereq_no NUMBER, prereq_desc VARCHAR2(50), prereq_cost NUMBER);
TYPE courses IS RECORD (course_no NUMBER, description VARCHAR2(50), cost NUMBER,
    prerequisite_rec prereqs);

BEGIN
courses.course_no := 25;

prerequisites(courses.course_no, courses.description, courses.cost, prereqs.prereq_no
    ,prereqs.prereq_desc, prereqs.cost);

DBMS_OUTPUT.PUT_LINE(courses.course_no || ' and ' || prereqs.prereq_no);

END;
/

The procedure is fine but I'm getting the ORA-06550 error when I execute the main block.

ORA-06550: line 7, column 5:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 7, column 5:
PL/SQL: Statement ignored
ORA-06550: line 9, column 19:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored
ORA-06550: line 12, column 26:
PLS-00330: invalid use of type name or subtype name
ORA-06550: line 12, column 5:
PL/SQL: Statement ignored

Any ideas?

Upvotes: 0

Views: 210

Answers (1)

Shaun Peterson
Shaun Peterson

Reputation: 1790

Think you are right about the requirements... you will need to declare the sub type before you declare the type. For example (untested as I do not have an environment available)

Declare

 TYPE prerequisite IS RECORD (prereq_no NUMBER, 
    prereq_desc VARCHAR2(50), prereq_cost NUMBER)

 TYPE courses IS RECORD (course_no NUMBER, description VARCHAR2(50), 
    cost NUMBER, prerequisite_rec prerequisite);

v_blah courses;

begin
   do stuff
end;
/

Upvotes: 1

Related Questions