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. — user5683823
– user5683823, Commented Nov 14, 2018 at 1:03
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. — user5683823
– user5683823, Commented Nov 14, 2018 at 1:07
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. — user5683823
– user5683823, Commented Nov 14, 2018 at 1:45
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'? — user5683823
– user5683823, Commented Nov 14, 2018 at 3:04

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

2

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.

answered Nov 14, 2018 at 3:38

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SMH Over a year ago

thanks @Kaushik, I am not sure why did you remove double quotes? My code is working fine with the double quotes but and returning the data fine with no error but the problem is to display the data of the object.

SMH Over a year ago

When I run your code I get : Error report - ORA-06550: line 6, column 38: PLS-00302: component 'ID' must be declared ORA-06550: line 6, column 7: PL/SQL: Statement ignored ORA-06550: line 7, column 38: PLS-00302: component 'G' must be declared ORA-06550: line 7, column 7: PL/SQL: Statement ignored ORA-06550: line 8, column 38: PLS-00302: component 'GES' must be declared

SMH Over a year ago

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

Kaushik Nayak Over a year ago

@SMH : your error message indicates the the object has not been declared correctly. As you can see it works for me fine. Either there's something wrong with your get_data() function or you aren't showing us everything.

Collectives™ on Stack Overflow

Display all object values in PLSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related