Thanks Karthick! There must be another way to load the data from another table. My example just shows putting "static" values in but I showed it like that to demonstrate that I wanted to load the table variable from another table.
Is there a way to do that? There has to be otherwise these "table variables" are pretty useless.

Oldman0215 wrote:
Thanks Karthick! There must be another way to load the data from another table. My example just shows putting "static" values in but I showed it like that to demonstrate that I wanted to load the table variable from another table.
Is there a way to do that?

In that case have a look into using BULK COLLECT.

There has to be otherwise these "table variables" are pretty useless.

Each feature in Any technology has its own use. Being unaware of a tool does not make its useless, Its just your ignorance.

Oldman0215 wrote:
Thanks Karthick! There must be another way to load the data from another table. My example just shows putting "static" values in but I showed it like that to demonstrate that I wanted to load the table variable from another table.
Is there a way to do that? There has to be otherwise these "table variables" are pretty useless.

If they were "pretty useless" they wouldn't be so widely used. It depends on what you want to use them for, which you still haven't said. All we know is that you want to put values into it, and those value apparently originate from a database table. That, in itself, is a "pretty useless" objective. Nobody puts values in any kind of a structure (table, memory variable, notepad in shirt pocket) without some idea of how they are going to use those values ....

Does everyone really need to know why I want to do it? Does it really add value to explain the whole case. The fact that I need to use a CTE, the fact that I need to store it so that I can further manipulate it to get the data into a different table in another format. There really is no reason to cloud the straightforward question with the entire problem I'm trying to solve. When you have a type that holds a collection of records it only makes perfect functional sense that you can load data into it "like" a table, since it is holding a collection of "records". Thanks Kathick and aschefer for steering me in the correct direction. I knew there was something like that but I couldn't find it.

Please check your SQL-Server/Sybase preconceptions in at the door. The vast majority of which does not apply to Oracle.

Secondly, apologies for the poor naming conventions and terminology used by Oracle in their PL/SQL documentation and examples.

That "table" structure is in fact a normal program array - or a dim (dimension) as it is called in Basic. It is not a table. It is nothing like a table. It should not be used as a table.

Also a fundamental concept that you need to get your head around. There are two distinct and different languages in Oracle.

The SQL language. Implemented as per (most of the) ANSI SQL standards and sporting a few Oracle SQL extensions (like the DECODE() function for example).

The PL (Programming Logic) procedural language, with some basic o-o features, based on Ada (part of the Pascal family of languages). SQL is tightly integrated with this - the result of which is called PL/SQL.

Where PL/SQL is clever enough to allow you to code and mix PL source and SQL source code, and it will figure out when to use the PL/SQL engine and when to use the SQL engine, and how to glue PL calls to SQL and SQL calls to PL, together.

A PL/SQL array resides in private process memory of that PL/SQL code unit. It can pass that array's contents to the SQL engine for use in SQL code (via a process called variable binding). It can write SQL engine data into a PL/SQL array (via a bulk fetch).

However, the SQL engine cannot directly read/write PL/SQL variables. Just like SQL cannot read/write a Java array in some Java process. That Java code needs to pass its array contents to SQL (via binding), or write SQL contents into its array. PL/SQL is conceptually the same - the same basic concepts of client (PL code or Java code) and server (SQL code) applies.

Oldman0215 wrote:
Does everyone really need to know why I want to do it?

If you are going to state something is useless, and then post useless code that does nothing, then yes, why becomes a valid question.

Does it really add value to explain the whole case. The fact that I need to use a CTE, the fact that I need to store it so that I can further manipulate it to get the data into a different table in another format.

Which PL/SQL functions or operators are you using in this further manipulation that are not available in SQL?

Because there are other facts that you need to be aware of, like the fact that this will consume more memory, use more redo and undo, create more locking and contention and be orders of magnitude slower and require more code than if you did not first move the data from the database before performing this further manipulation.

So, which PL/SQL functions or operators are you using in this further manipulation that are not available in SQL that justify this extra time, effort, expense and cost?

Otherwise the example you showed, unlike table variables, is not useless, it is worse than useless.