This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 18, 2013 7:01 AM by 6363 RSS

Inserting data into a table variable

719640 Newbie
Currently Being Moderated
Why does the following PL/SQL code say that the table or view doesn't exist:

DECLARE
type rectyp is record(CategId INT, intPath VARCHAR2(4000), CPropID INT, Property VARCHAR2(50), PropVal VARCHAR2(2000));
type tmplUpgradeTbl is table of rectyp;

tempTable tmplUpgradeTbl;

dgenTmplPropID INT;
BEGIN
INSERT INTO tempTable
select 23, '3,4,5', 41, 'Test', 'Test' from dual;
END;
  • 1. Re: Inserting data into a table variable
    6363 Guru
    Currently Being Moderated
    Oldman0215 wrote:
    Why does the following PL/SQL code say that the table or view doesn't exist:
    Because tempTable is not a table.
  • 2. Re: Inserting data into a table variable
    719640 Newbie
    Currently Being Moderated
    So how do you achieve what I'm trying to do?
  • 3. Re: Inserting data into a table variable
    Karthick_Arp Guru
    Currently Being Moderated
    Oldman0215 wrote:
    Why does the following PL/SQL code say that the table or view doesn't exist:

    DECLARE
    type rectyp is record(CategId INT, intPath VARCHAR2(4000), CPropID INT, Property VARCHAR2(50), PropVal VARCHAR2(2000));
    type tmplUpgradeTbl is table of rectyp;

    tempTable tmplUpgradeTbl;

    dgenTmplPropID INT;
    BEGIN
    INSERT INTO tempTable
    select 23, '3,4,5', 41, 'Test', 'Test' from dual;
    END;
    tempTable is a nested table pl/sql collection type. so you cannot perform DML operation on it. You need to do it like this
    DECLARE
       type rectyp is record
       (
           CategId INT
         , intPath VARCHAR2(4000)
         , CPropID INT
         , Property VARCHAR2(50)
         , PropVal VARCHAR2(2000)
       );
    
       type tmplUpgradeTbl is table of rectyp;
    
       tempTable tmplUpgradeTbl;
    
       dgenTmplPropID INT;
    BEGIN
       tempTable(1).CategId  := 23;
       tempTable(1).intPath  := '3,4,5';
       tempTable(1).CPropID  := 41;
       tempTable(1).Property := 'Test';
       tempTable(1).PropVal  := 'Test';
    END;
    /
  • 4. Re: Inserting data into a table variable
    6363 Guru
    Currently Being Moderated
    It depends on what you are trying to do, you haven't said.
  • 5. Re: Inserting data into a table variable
    ascheffer Expert
    Currently Being Moderated
    This ?
    DECLARE
      type rectyp is record(CategId INT, intPath VARCHAR2(4000), CPropID INT, Property VARCHAR2(50), PropVal VARCHAR2(2000));
      type tmplUpgradeTbl is table of rectyp;
      tempTable tmplUpgradeTbl;
    BEGIN
      select 23, '3,4,5', 41, 'Test', 'Test' bulk collect into tempTable
      from dual;
    END;
  • 6. Re: Inserting data into a table variable
    Karthick_Arp Guru
    Currently Being Moderated
    Oldman0215 wrote:
    So how do you achieve what I'm trying to do?
    I would suggest you read the oracle document on [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABFACFA]Understanding PL/SQL Collections
  • 7. Re: Inserting data into a table variable
    719640 Newbie
    Currently Being Moderated
    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.
  • 8. Re: Inserting data into a table variable
    Karthick_Arp Guru
    Currently Being Moderated
    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.

    Edited by: Karthick_Arp on Jan 18, 2013 5:20 AM
  • 9. Re: Inserting data into a table variable
    6363 Guru
    Currently Being Moderated
    Oldman0215 wrote:
    I wanted to load the table variable from another table.
    Why?
    There has to be otherwise these "table variables" are pretty useless.
    Moving data out of a table into a variable for no good reason also seems pretty useless.
  • 10. Re: Inserting data into a table variable
    719640 Newbie
    Currently Being Moderated
    I think I found what I need to do in that article.
    Using BULK COLLECT INTO should do what I want correct?
  • 11. Re: Inserting data into a table variable
    EdStevens Guru
    Currently Being Moderated
    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 ....

    Have you looked at the INTO option of the SELECT statement?
  • 12. Re: Inserting data into a table variable
    719640 Newbie
    Currently Being Moderated
    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.
  • 13. Re: Inserting data into a table variable
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.
  • 14. Re: Inserting data into a table variable
    6363 Guru
    Currently Being Moderated
    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.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points