Display all object values in PLSQL

Question

I am trying to display all the values in an object but it give me 'must be declared' error.

I tried the code below but still giving me same error.

declare
      outN mytype;
begin
      outN:= get_data();
      dbms_output.put_line(outN.toString);
      -- tried this as well
      dbms_output.put_line(outN.ID);
      dbms_output.put_line(outN.G);
      dbms_output.put_line(outN.GES);
      dbms_output.put_line(outN.CC);
      dbms_output.put_line(outN.RR);

end;

And my object is:

 create or replace TYPE           "mytype"
    AS OBJECT
    (
        "ID" NUMBER(10),
        "G" NUMBER(10),
        "GES"  VARCHAR(100 BYTE),
        "CC" NUMBER(10),
        "RR" VARCHAR(100 BYTE)
    );

Errors:

Error report - ORA-06550: line 5, column 38: PLS-00302: component 'ID' must be declared ORA-06550: line 5, column 7: PL/SQL: Statement ignored ORA-06550: line 6, column 38: PLS-00302: component 'G' must be declared ORA-06550: line 6, column 7: PL/SQL: Statement ignored ORA-06550: line 7, column 38: PLS-00302: component 'GES' must be declared ORA-06550: line 7, column 7: PL/SQL: Statement ignored ORA-06550: line 8, column 38: PLS-00302: component 'CC' must be declared ORA-06550: line 8, column 7: PL/SQL: Statement ignored ORA-06550: line 9, column 38: PLS-00302: component 'RR' must be declared ORA-06550: line 9, column 7: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error.

Another one:

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

get_data is just a function that return the result of a select statement (returns db rows)

What is toString? I don't see an attribute (or method) of type mytype in its definition. In any case, the first error will be thrown at the name of the type. You declared it as "mytype". In Oracle, when you use double-quotes, you are saying that you want case-sensitive identifiers. By default Oracle is not case sensitive; it will automatically convert mytype in your code block to MYTYPE, which will not match "mytype". Get in the habit of not using double-quoted identifiers, they only bring trouble. The alternative is in the declarations section to use double quotes. — mathguy
What is get_data(), also? There is a call to it in your code, but you haven't shown us what it is. ALSO: If you need help, you will have to include the exact error message(s) in your posts. Right now, the type MYTYPE is not declared (lower-case "mytype" is, which is different), but you will likely get more errors - tell us exactly what they are. — mathguy
I am sorry, but I don't believe you. If you define the type "mytype" but you reference it without double-quotes, you will not get to the "undeclared components", you will get the error PLS-00201: identifier 'MYTYPE' must be declared first. — mathguy
Unless... did you also (perhaps in the past) create a type MYTYPE? Which may still exist in your schema (or a schema you have privileges to)? What do you get if you select * from ALL_TYPES where UPPER(type_name) = 'MYTYPE'? — mathguy

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-11-14T03:38:29

Not sure what get_data() and toString are in your code. But, this should explain how object values can be displayed.

create or replace TYPE  mytype -- removed double quotes
    AS OBJECT
    (
        "ID"   NUMBER(10),
        "G"    NUMBER(10),
        "GES"  VARCHAR2(100 BYTE), --Use VARCHAR2 instead of VARCHAR
        "CC"   NUMBER(10),
        "RR"   VARCHAR2(100 BYTE)
    );
    /
SET SERVEROUTPUT ON    
DECLARE
     outn   mytype := mytype(1,10,'GES1',20,'RR1'); --declaration and assignment
BEGIN
     dbms_output.put_line(outn.id);
     dbms_output.put_line(outn.g);
     dbms_output.put_line(outn.ges);
     dbms_output.put_line(outn.cc);
     dbms_output.put_line(outn.rr);
END;
/

Result

Type MYTYPE compiled

1
10
GES1
20
RR1


PL/SQL procedure successfully completed.

Display all object values in PLSQL

1 Answers