4 Replies Latest reply: Apr 2, 2014 9:25 AM by Stew Ashton RSS

    Inserting Dyanmically built statement to get type value to be inserted in forall...pls help..urgent..

    user13794235

      Hi Guys..

       

       

      I am trying to achieve somthing like mention in below code...but the problem is it insers the string created in clob variable rather than getting the values...

       

      for i in (select * from dwtb_fcubs_extract where extract_name='FCUBS_LENDING_ACCOUNT' ORDER BY SNO) LOOP

       

       

      IF I.FACTORY_SHIPPED='Y' THEN

       

       

      l_stmt_value := l_stmt_value||'l_rec_account_details(i)'||'.'||i.DWH_COLUMN_NAME||'||'||chr(39)||'||'||chr(39)||'||'||chr(10);

       

       

      ELSE

       

       

      l_stmt_value := l_stmt_value||'l_rec_account_dtls_addnl(i)'||'.'||i.dwh_column_name||'||'||chr(39)||'||'||chr(39)||'||'||chr(10);

       

       

      end if;

       

       

      end loop;

      l_stmt_value := substr(l_stmt_value,

                                     1,

                                     length(l_stmt_value) - 9);         

       

       

      Statement built:

       

      l_rec_account_details(i).BUSS_DATE||'||'||

      l_rec_account_details(i).ACCOUNT_NUMBER||'||'||

      l_rec_account_details(i).OLD_ACCT_NO||'||'||

      l_rec_account_details(i).CONTRACT_DATE||'||'||

      l_rec_account_details(i).MAIN_FACILITY_ID||'||'||

      l_rec_account_details(i).SUB_FACILITY_ID||'||'||

      l_rec_account_details(i).APPL_ID||'||'||

      l_rec_account_details(i).ACCOUNT_BRANCH_CODE||'||'||

      l_rec_account_details(i).CIF_NO||'||'||

      l_rec_account_details(i).ACCT_NAME||'||'||

      l_rec_account_details(i).INTEREST_RATE

       

      but the same gets inserted into clob table, rather than it's values...

       

       

       

      l_stmt := l_stmt||l_stmt_value||');'||chr(10);

       

       

      --l_stmt := l_stmt||'end;';

       

       

      dbg('insert statement is :' || l_stmt);

       

       

      for i in 1 .. l_account_number.count loop

       

       

      l_stmt_value1(i) := l_stmt_value;

       

       

      end loop;

       

       

      /*execute immediate l_stmt;*/

       

       

      forall i in 1 .. l_account_number.count loop

       

       

      execute immediate 'INSERT INTO dwtb_account_details_clob VALUES (:1)  ' using l_stmt_value1(i);

       

       

      /*insert into dwtb_account_details_clob values (l_stmt_value);*/

      /*end loop;*/

      commit;