Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    1

    Unanswered: Insert more than one rows in dynamic Sql

    Hi,
    I've problem of insert more than one rows into the table of my below package.

    I appreciate if anyone can give me an idea what solution that I can insert more than one rows of my dynamic sql.


    CREATE OR REPLACE PACKAGE BODY Rpt AS

    rec colocation.t_icsc_Cap%rowtype
    table_name VARCHAR2(100);
    v_Sql VARCHAR2(1000);
    pv_CaseNo VARCHAR2(20);
    pv_DoUpdateCase VARCHAR2(5);
    pv_NewCaseNo VARCHAR2(20);
    pv_TableExt VARCHAR2(20);

    PROCEDURE main(p_CaseNo in varchar2,p_DoUpdateCaseNo in varchar2,p_NewCaseNo in varchar2,p_table_Ext in varchar2);
    Begin
    /* Assign values to public variables */
    pv_CaseNo := p_CaseNo;
    pv_DoUpdateCase := p_DoUpdateCaseNo;
    pv_NewCaseNo := p_NewCaseNo;
    pv_TableExt := p_table_Ext;

    /* Start execute the following procedures */
    Testing;

    END main;

    PROCEDURE Testing1 IS
    rec colocation.t_icsc_Cap%rowtype;
    table_name VARCHAR2(100);
    v_Sql VARCHAR2(1000);
    BEGIN
    v_Sql := 'Select * FROM colocation.' || table_name || ' WHERE Case_No = ' || '''' || pv_CaseNo || '''';
    execute immediate v_Sql into rec;
    rec.case_no := pv_NewCaseNo;
    insert into colocation.t_icsc

    values (REC.ACNA,
    REC.APP_CANCLD_DUE_TO_REQUOTE_DATE,
    REC.APPL_RECVD_DATE,
    REC.APPL_RECVD_TIME,
    REC.BAN_NO,
    REC.BILL_DATE,
    REC.CALIFORNIA_OR_NEVADA,
    REC.CANCELLATION_RECVD_DATE );
    Commit;
    dbms_output.put_line('Insert successful');
    Exception
    WHEN OTHERS THEN
    dbms_output.put_line('ERROR! Insert NOT successful');
    dbms_output.put_line('ORACLE Error = ' || SQLCODE || SQLERRM);
    Rollback;
    END Testing1;


    END Rpt;


    Thanks,

    shin

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Insert more than one rows in dynamic Sql

    You can do it like this:

    Code:
    procedure testing is
      v_sql varchar2(1000);
    begin
      v_sql := 'insert into collocation.t_icsc (col1, col2, col3)'
        || ' select cola, colb, colc from collocation.' || table_name
        || ' where case_no = :case_no'
    
      execute immediate v_sql using pv_caseno;
    end;
    (I don't know what this line was for, so omitted it:
    rec.case_no := pv_NewCaseNo;
    )

    Note:

    1) use of bind variables in dynamic SQL. You should never concatenate values into the v_sql string, only names of tables, columns etc.

    2) It is bad form to commit within procedures. It is up to the calling program to decide whether to commit or rollback.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •