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!

Help Need in PLSQL Records

Discussion in 'SQL PL/SQL' started by venkateshguru, Jun 1, 2014.

  1. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    Why the following program inserting NULL Record in Table? Can somebody help.

    DECLARE
    TRANS_TYPE TEST%ROWTYPE;
    TRANS_TYPE1 TEST%ROWTYPE;
    BEGIN
    SELECT *
    INTO TRANS_TYPE1
    FROM TEST
    WHERE TRANS_ID= 40023;

    TRANS_TYPE1 := TRANS_TYPE;

    INSERT INTO TEST1
    VALUES TRANS_TYPE1;

    COMMIT;

    INSERT INTO RPRO_ARR_TRANSACTIONS_TEST
    VALUES TRANS_TYPE;


    COMMIT;

    END;
     
  2. neha13infy

    neha13infy Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Hi Venkatesh,

    I think its because of the code line
    TRANS_TYPE1 := TRANS_TYPE;

    You are assigning the value of TRANS_TYPE to TRANS_TYPE1 when TRANS_TYPE is null.

    I am not sure what is your requirement but it should be other way around:
    TRANS_TYPE:= TRANS_TYPE1

    Let me know if this helps :)
     
    venkateshguru likes this.
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Can to you it is necessary there was here such option :

    Code (SQL):


    DECLARE
        TRANS_TYPE    TEST%ROWTYPE;
        TRANS_TYPE1   TEST%ROWTYPE;
    BEGIN
       SELECT *
         INTO TRANS_TYPE
         FROM TEST
        WHERE TRANS_ID= 40023;
     
       TRANS_TYPE1 := TRANS_TYPE;
     
       INSERT INTO TEST1
            VALUES TRANS_TYPE1;
     
    INSERT INTO RPRO_ARR_TRANSACTIONS_TEST
            VALUES TRANS_TYPE;
     
     
       COMMIT;
     
    END;                   




     
    Additional links :
    http://www.tutorialspoint.com/plsql/plsql_records.htm

    http://www.oracle.com/technetwork/issue-archive/2012/12-may/o32plsql-1578019.html

    http://www.oracle-base.com/articles/8i/collections-8i.php
     
  4. venkateshguru

    venkateshguru Active Member

    Messages:
    30
    Likes Received:
    1
    Trophy Points:
    110
    My bad.. I didn't see properly. Thanks for your help. :) Please provide the some more interesting things in collection and records.
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    You can even remove the line:

    and use the following:


    Code (SQL):
    DECLARE
        TRANS_TYPE    TEST%ROWTYPE;
    BEGIN
       SELECT *
         INTO TRANS_TYPE
         FROM TEST
        WHERE TRANS_ID= 40023;
     
       INSERT INTO TEST1
            VALUES TRANS_TYPE;
     
    INSERT INTO RPRO_ARR_TRANSACTIONS_TEST
            VALUES TRANS_TYPE;
     
     
       COMMIT;
     
    END;
     
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO