This discussion is archived
6 Replies Latest reply: Feb 21, 2013 2:33 AM by padders RSS

insert record type

RajeshKanna Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    hi.
  • 5. Re: insert record type
    Sg049 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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>

Legend

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