EXECUTE IMMEDIATE with multiple lines of columns to insert

Question

just want to get an idea if this is the correct way to do an EXECUTE IMMEDIATE with multiple columns and lines and assigning it to a variable? I tried looking at examples but am not sure if I am concatenating the lines correctly?

sql_stmt        VARCHAR2(200);

sql_stmt:='INSERT INTO (STORECODE, TILLID, TRANSACTIONNR, TRADINGDATE, TRANSTYPE, ' ||
          'OPERATORCODE TRAININGMODE, VOIDED, VALUEGROSS, VALUENETT, VALUEDUE) ' ||
          ‘VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)’;
          EXECUTE IMMEDIATE sql_stmt USING sSTORECODE………………………………………..fGROSS_AMOUNT,
      ‘0’;

score 11 · Answer 1 · answered Jan 18 '13 at 15:47

A string can be multi-line in Oracle. As such, you could simply write:

sql_stmt := 'INSERT INTO (STORECODE, TILLID, TRANSACTIONNR, TRADINGDATE, 
                          TRANSTYPE, OPERATORCODE TRAININGMODE, VOIDED, 
                          VALUEGROSS, VALUENETT, VALUEDUE) 
                  VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)';

EXECUTE IMMEDIATE sql_stmt USING p1, p2... p10;

Using concatenation (||) and several substrings would work as well of course.

score 2 · Answer 2 · answered Apr 02 '13 at 21:42

set serveroutput on size unlimited;

declare
str_   varchar(1000 char);
date_ date;


begin
  --str_ := 'select sysdate' || chr(10);
  --str_ := str_ || 'from dual';
  str_ := 'select sysdate /* ''comment'' */
           from dual';

  execute immediate str_
  into date_;

  dbms_output.put_line(date_);


end;

EXECUTE IMMEDIATE with multiple lines of columns to insert

2 Answers2