8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Misc » Here

Comments for Using the TABLE Operator with Locally Defined Types in PL/SQL


Anil said...

Is ForAll better or table operator now for updates?

Tim... said...

Hi.

You can't update against a table function. Perhaps you mean INSERT INTO ... SELECT, in which case you could use this for the SELECT, for use an INSERT in a FORALL. They both have different pros and cons, so there is not a "better" one. It depends.

Cheers

Tim...

Tim Thompson said...

Hi,
Just noticed a complication that took an hour or so to figure out:

If one of the columns in the rowtype is a float then casting the array to a table will work, but you will be unable to reference the columns in it. To solve this create a record of the same structure but substituted a number for the float and use the record type to define the associative array rather than a row type.

Tim

Vidhan Jadhav said...

Hi,
Error: PLS-00642: local collection types not allowed in SQL statements
Line: 9
Text: For Cur_rec in (SELECT * FROM table(m_tab)) loop

Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Line: 9
Text: For Cur_rec in (SELECT * FROM table(m_tab)) loop

Tim... said...

Hi.

The first sentence says, "In Oracle 12c, the TABLE operator...". It appears you are not using Oracle 12c or above.

Cheers

Tim...

Vijay Kasturi said...

In Oracle 12c, this feature of using TABLE operating with local defined types only works with type index by binary_integer ONLY but not for those index by varchar2.

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!

If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.