1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Table variable Vs GTT

Discussion in 'SQL PL/SQL' started by sandip.senmajumder, Feb 15, 2011.

  1. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,
    Any body would like to explain Table variable Vs GTT OR any other way for storing data temporarily in a procedure for further processing which would be session specific and easier in respect of administration or(maintenance) and must be secure .........
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    There is nothing liker table variable in Oracle. I think it is in SQL Server.

    Anyway, packages are the most frequently and popularly used method for maintaining data in particular session.

    check here
     
  3. sandip.senmajumder

    sandip.senmajumder Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    you said package........ can you explain me in brief....by giving a example
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    SQL> CREATE OR REPLACE package SCOTT.testp AS
    A NUMBER:= 101;
     TYPE EmpTab IS TABLE OF EMP%Rowtype;
     empt EmpTab;
    END;
    /  2    3    4    5    6

    Package created.

    SQL> CREATE OR REPLACE PROCEDURE PUT_EMPTAB
    AS
    BEGIN

     SELECT * BULK COLLECT
     INTO TESTP.EMPT
     FROM EMP;

    EXCEPTION
    WHEN OTHERS THEN
        NULL;
    END;  2    3    4    5    6    7    8    9   10   11   12
     13  /

    PROCEDURE created.

    SQL> CREATE OR REPLACE PROCEDURE GET_EMPTAB
    AS
      2    3  BEGIN
      4
      5      FOR REC IN 1 .. TESTP.EMPT.COUNT
      6      LOOP
      7          DBMS_OUTPUT.PUT_LINE (TESTP.EMPT(REC).EMPNO||':'||TESTP.EMPT(REC).ENAME||':'||TESTP.EMPT(REC).SAL);
        END LOOP;
      8    9
     10  EXCEPTION
     11  WHEN OTHERS THEN
     12      NULL;
     13  END;
     14  /

    PROCEDURE created.

    SQL> EXEC PUT_EMPTAB;

    PL/SQL PROCEDURE successfully completed.

    SQL> EXEC GET_EMPTAB;
    7369:SMITH:800
    7499:ALLEN:1600
    7521:WARD:1250
    7566:JONES:2975
    7654:MARTIN:1250
    7698:BLAKE:2850
    7782:CLARK:2450
    7788:SCOTT:3000
    7839:KING:5000
    7844:TURNER:1500
    7876:ADAMS:1100
    7900:JAMES:950
    7902:FORD:3000
    7934:MILLER:1300

    PL/SQL PROCEDURE successfully completed.

    SQL>

     
    Here you can see that data populated into table type persists throughout the session.