6 Replies Latest reply: Feb 21, 2013 4:33 AM by padders RSS

    insert record type

    RajeshKanna
      Hi ALL,

      I have Created a table emp_info with two columns. I am assigned two values to recordtype variable.
      while inserting the Record into the table an compilation error is to be there.
      Create table emp_info(empno number(5),ename varchar2(30));
      
      DECLARE
      
      l_rec emp_info%rowtype;
      
      BEGIN
      
      l_rec.empno := 101;
      l_rec.ename := 'KING';
      
      insert into emp_info(empno,ename)
      values(l_rec);
      
      commit;
      
      END;
      ERROR at line 11:
      ORA-06550: line 10, column 35:
      PL/SQL: ORA-00947: not enough values
      ORA-06550: line 10, column 1:
      PL/SQL: SQL Statement ignored


      Can I insert the Recordtype variables with out indicating the column names. Can any one help me??
        • 1. Re: insert record type
          Solomon Yakobson
          Do not use column list. Do not put parenthesis around record variable:
          SQL> DECLARE
            2      l_rec emp_info%rowtype;
            3  BEGIN
            4      l_rec.empno := 101;
            5      l_rec.ename := 'KING';
            6      insert
            7        into emp_info
            8        values l_rec;
            9       commit; 
           10  END;
           11  /
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SY.
          • 2. Re: insert record type
            RajeshKanna
            Hi Solomon,

            Thanks for the reply. If I want to pass partial values for example to insert only empno using a record type varaible, is there any method available.
            DECLARE
             
            type rec_emp is record (empno NUMBER);
            l_rec rec_emp;
             
            BEGIN
             
            l_rec.empno := 101;
            
             
            insert into emp_info(empno)
            values(l_rec);
             
            commit;
             
            END;
            • 3. Re: insert record type
              Solomon Yakobson
              RajeshKanna wrote:
              If I want to pass partial values
              Then you can't use
              VALUES record
              format. Syntax is:
              INSERT
                INTO tbl
                VALUES tbl_rowtype_record
              SY.
              • 4. Re: insert record type
                RajeshKanna
                hi.
                • 5. Re: insert record type
                  Sg049
                  No. If you want you can write like below..
                  DECLARE
                  type rec_emp is record (empno NUMBER);
                  l_rec rec_emp;
                  BEGIN
                  l_rec.empno := 101;
                  insert into emp(empno)
                  values(l_rec.empno);
                  END;
                  • 6. Re: insert record type
                    padders
                    Then you can't use VALUES record
                    Unless you INSERT into view or in-line view.
                    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    
                    SQL> DECLARE
                      2     TYPE rec_emp IS RECORD (empno NUMBER);
                      3
                      4     l_rec rec_emp;
                      5  BEGIN
                      6     l_rec.empno := 101;
                      7
                      8     INSERT INTO (SELECT empno FROM emp)
                      9     VALUES      l_rec;
                     10  END;
                     11  /
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL>