Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

%ROWTYPE Attribute and Invisible Columns (Bad documentation)?

jaramillAug 19 2019 — edited Aug 20 2019

As I study for my OCP - PL/SQL, I came across the section on records under section "Collections and Records" subtitled "%ROWTYPE Attribute and Invisible Columns"

Here's the link --> https://docs.oracle.com/database/121/LNPLS/composites.htm#GUID-058B062C-C429-422B-AD64-B2CFF63075FC

Now based on their example, a record variable based on a cursor (which is based on a potential partial row of a query), should fail before ....AND after making an invisible column...visible, unlike declaring a record variable based on a rowtype of a table/view.

But here's their example modified with a record variable based on a cursor that does NOT fail after making the invisible column...visible.

drop table t;

create table t (a int, b int, c int invisible);

insert into t (a, b, c) values (1, 2, 3);

commit;

declare

   cursor myCursor is

      select *

        from t;

   myRec myCursor%rowtype;

begin

  open myCursor;

  fetch myCursor

   into myRec;

  dbms_output.put_line('c = ' || to_char(myRec.c));

  close myCursor;

end;

/

alter table t modify (c visible);

declare

   cursor myCursor is

      select *

        from t;

   myRec myCursor%rowtype;

begin

  open myCursor;

  fetch myCursor

   into myRec;

  dbms_output.put_line('c = ' || to_char(myRec.c));   -- IT SHOULD FAIL HERE but does not

  close myCursor;

end;

/

OUTPUT

Time Start: 2019-08-19 18:43:07

Table dropped.

Table created.

1 row created.

Commit complete.

>> declare

   cursor myCursor is

      select *

        from t;

   myRec myCursor%rowtype;

begin

  open myCursor;

  fetch myCursor

   into myRec;

  dbms_output.put_line('c = ' || to_char(myRec.c));

  close myCursor;

end;

Error at line 6

ORA-06550: line 13, column 48:

PLS-00302: component 'C' must be declared

ORA-06550: line 13, column 3:

PL/SQL: Statement ignored

Table altered.

PL/SQL procedure successfully completed.

Time End: 2019-08-19 18:43:11

Elapsed Time for Script Execution: 4 secs


Am I missing something?  Bad documentation?

Oracle version is "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production"

This post has been answered by Frank Kulash on Aug 19 2019
Jump to Answer
Comments
Post Details
Added on Aug 19 2019
15 comments
1,211 views