Double Pipe in a string problem

[PaulCoop]

In the atempt to build a dynamic sql string I have run into an interesting yet annoying problem. When I append to the end of a string the concatenation symbol '||' (double pipe) PL/SQL throws an error. A single pipe is ok. Append two single pipes also throws an error. So does the use of CHR(124) instead. Any ideas on how to get a double pipe into a string?

 RECORD_REPORT_RUN_SQL (Report_Code2 in Varchar2) return varchar2
as
query_str varchar2 (300);
begin
 query_str := 'INSERT INTO REPDEV.REPORT_USE_MONITOR (IDENTIFIER, REPORT_CODE, DATE_AND_TIMESTAMP, USER, PARAMETERS) VALUES (REPORT_USE_MONITOR_IDENTIFIER.NEXTVAL, ';
 query_str := query_str || chr(39) || Report_Code2 || chr(39) || ', ';
 query_str := query_str || 'sysdate, ' || chr(39) || user || chr(39) || ', ';
 for parameter_rec IN (SELECT * FROM REPDEV.REPORT_BIND_PARAMETERS WHERE REPDEV.REPORT_BIND_PARAMETERS.REPORT_CODE = Report_Code2)
 loop
  query_str := query_str || chr(39) || parameter_rec.parameter || ' :=' || chr(39) || ' ' || chr(124) || chr(124);
 end loop;
 query_str := query_str || chr(39) || chr(39) || ')';
 return query_str; 
end;

Paul Cooper

 

[Nick366]

It looks like you might be repeating info. If I am reading this correctly, you want something like this as a result:

insert into TABLE1 (A, B, C) values
    (1, 2, 3),
    (4, 5, 6),
    (7, 8, 9);

The way it is set up, it is putting it like:

insert into TABLE1 (A, B, C) values
    (1, 2, 3), insert into TABLE1 (A, B, C) values
    (1, 2, 3), (4, 5, 6) insert into TABLE1 (A, B, C) values
    (1, 2, 3), (4, 5, 6), (7, 8, 9);

You need to have multiple strings declared because you do not want to repeat the first section in your loop. Put 'insert into table1 (A, B, C) values' in one string, then loop into a new sting all the records for the values, then concat the begining, the values, and the end into the final string to return.

 

[Nick366]

Looking again, I see I mis-read that. Sorry, ignore my last post.

 

[sem]

Quoted double pipe has no special meaning. Just check your code (probably qotes number and positions) again.

Regards, Dima

http://seminihin.narod.ru