Cannot use procedure in the select statement?

Question

Below is my code to create types and tables. It's all correct:

CREATE TYPE shape_typ AS OBJECT (
l   INTEGER, -- length
w   INTEGER, -- width
h   INTEGER, -- height
MEMBER FUNCTION area RETURN INTEGER,
MEMBER FUNCTION volume RETURN INTEGER,
MEMBER PROCEDURE display (SELF IN OUT NOCOPY shape_typ) );

CREATE TYPE BODY shape_typ AS 
MEMBER FUNCTION volume RETURN INTEGER IS
BEGIN
   RETURN l * w * h;
-- same as previous line RETURN SELF.l * SELF.w * SELF.h; 
END;
MEMBER FUNCTION area RETURN INTEGER IS
BEGIN -- not necessary to include SELF in following
    RETURN 2 * (l * w + l * h + w * h);
END;
MEMBER PROCEDURE display (SELF IN OUT NOCOPY shape_typ) IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE('Length: ' || l || ' - ' || 
                        'Width: ' || w || ' - ' ||
                        'Height: ' || h );
   DBMS_OUTPUT.PUT_LINE('Volume: ' || volume || ' - ' || 
                        'Area: ' || area );
  END;
END;

CREATE TABLE shapes (
shape       shape_typ,
create_date DATE );

INSERT INTO shapes VALUES (
shape_typ (3,3,3), '17-MAR-2008' );

INSERT INTO shapes VALUES (
shape_typ (1,8,2), '17-FEB-2008' );

INSERT INTO shapes VALUES (
shape_typ (1,1,1), '27-MAR-2008' );

SELECT s.shape.l,s.shape.w,s.shape.h,s.shape.area() FROM shapes s;

DECLARE
shap shapes%rowtype;
BEGIN -- PL/SQL block for selecting-displaying a student
  SELECT * INTO shap
    FROM shapes s 
    WHERE s.shape.l=1 and s.shape.w=1 and s.shape.h=1;
  shap.shape.display;
END;

However, when I try to execute the procedure in a the following statement, it gives me an error message.

SELECT s.shape.l,s.shape.w,s.shape.h,s.shape.display() FROM shapes s;

The error message is that:

ORA-06553: PLS-222: no function with name 'DISPLAY' exists in this scope 06553. 00000 - "PLS-%s: %s" *Cause:
*Action: Error at Line: 49 Column: 38

I am wondering whether it is because I cannot use the procedure in a select statement?

Answer 1

No, you can't use procedures in SQL-Statements.

The procedure DISPLAY can only be used in a PL/SQL context.

See Oracle Documentation : Coding PL/SQL Subprograms and Packages