CODEWITHSUNDEEP
oracle-databasesqlplusquotes
John O
John O

Reputation: 5433

How can I assign a multi-line value to a bind variable?

I'm declaring and initializing the variable like this:

var test CLOB
exec :test := q'<
many many lines
many many lines
>'

I suppose I didn't really expect this syntax of string-quoting to work outside of PL-SQL blocks, but if there a SQLPlus equivalent? In particular, is there a syntax that doesn't require mangling every line in between the quotes (putting an escape before every newline in the file)? I expect this SQLPlus script to be generated by a shell script, with the value of the clob just catted in from another file.

Upvotes: 1

Views: 2988

Answers (1)

Nicolas
Nicolas

Reputation: 933

execute actually creates and run a PLSQL anonymous block but it must fit in a single line. So you could instead explicitly write the the PLSQL block like this :

begin 
:test :=  q'<
many many lines
many many lines
>' ;
end; 
/

If it's convenient enough.

Upvotes: 3

Related Questions