Re: Using records in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 29 Jun 1999 15:12:38 GMT
Message-ID: <377ae1d0.11427341@newshost.us.oracle.com>

A copy of this was sent to Greg Akins <insomnia_at_a1usa.net> (if that email address didn't require changing) On Tue, 29 Jun 1999 07:01:20 -0800, you wrote:

>This is probably a simple problem, but I haven't found an
>answer in the docs yet, so...
>
>I have a PL/SQL program.
>
>I'm declaring a record (MASTER_REC) and a cusor
>
>They in a loop I fetch the cursor results into the record.
>For each record I do:
>
>INSERT INTO MASTER_TABLE VALUES (MASTER_REC) ;
>
>This gives me a PLS_00382 error (wrong type).
>
>Is there a way to do this without
>
>INSERT INTO MASTER_TABLE VALUES (MASTER_REC.field0) ;
>
>Any help would be appreciated.
>
>-greg
>
>

No, you cannot insert a record -- you must reference the elements of the record in the insert statement. A solution you might like is below. It uses dynamic sql to insert the record for you so if the number/names/types of the columns change over time -- you don't have to 'fix' it.

>
>
>**** Posted from RemarQ - http://www.remarq.com - Discussions Start Here (tm) ****

Not directly but with a little 'trick' and a global package variable we can achieve this. Run this example to see how it can work:  

drop table dept;
create table dept as select * from scott.dept;  

create or replace package demo_pkg
as

• all routines assume the following naming convention:
• if you send p_table = "ABC", there will be
• a package named "ABC_rowpkg"
• a global variable of type ABC%rowtype names "ABC_rowinstance"
• eg: for the dept table, see below

    procedure insert_row( p_owner in varchar2, p_table in varchar2 ); end;
/
show errors  

create or replace package body demo_pkg as  

procedure execute_immediate( sql_stmt in varchar2 ) as

    exec_cursor integer default dbms_sql.open_cursor;     rows_processed number default 0;
begin

    dbms_output.put_line( sql_stmt );
    dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
    rows_processed := dbms_sql.execute(exec_cursor);
    dbms_sql.close_cursor( exec_cursor );

end;  

procedure insert_row( p_owner in varchar2, p_table in varchar2 ) is

    l_string    varchar2(4096);
    l_sep       varchar2(1) := '';
    l_qual      varchar2(255);

begin

    for x in ( select '$' || column_name column_name

                 from all_tab_columns
                where owner = upper(p_owner)
                  and table_name = upper(p_table) )
    loop
        l_string := l_string || l_sep || x.column_name;
        l_sep    := ',';

    end loop;  

    l_qual := p_table || '_rowpkg.' || p_table || '_rowinstance.';  

    execute_immediate( 'begin insert into ' || p_owner || '.' || p_table ||

                       '( ' || replace( l_string, '$', ' ' ) || ') values ( ' ||
                       replace( l_string, '$', l_qual ) || ');
                       end;' );

end insert_row;  

end;
/
show errors  

create or replace package dept_rowpkg
as

    dept_rowinstance dept%rowtype;
end;
/
show errors      

declare

    l_deptrec dept%rowtype;
begin

    select * into l_deptrec from dept where rownum = 1;  

    l_deptrec.deptno := 11;  

    dept_rowpkg.dept_rowinstance := l_deptrec;     demo_pkg.insert_row( user, 'DEPT' ); end;
/  

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jun 29 1999 - 10:12:38 CDT