| quoting string in multiple lines [message #648254] |
Fri, 19 February 2016 06:54  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
I would like to assign a query to a bind variable using query string. Query spawn across multiple rows (100s of lines having union all satements).
The below sample code works alright from a script file
variable c clob;
exec :c:= q'[select * from dual where 'bb'='bb']'
But error is thrown when the query is split across multiple lines.
variable c clob;
exec :c:= q'[select * from dual
where 'bb'='bb']'
exec :c:= q'[select * from dual
*
ERROR at line 1:
ORA-00900: invalid SQL statement
Appending '-' at the end of the line will solve the problem but is there any alternative approach as this leads to appends 100s of line with '-'
Thank you in advance for your time.
Regards,
Pointers
|
|
|
|
|
|
| Re: quoting string in multiple lines [message #648259 is a reply to message #648255] |
Fri, 19 February 2016 08:02  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Thank you for the details.
I could declare all the queries by assigning to PL/SQL variables inside an anonymous block.
But I would like to have this in a separate script (say "first.sql") and refer these variables in different script (say "second.sql").
In the second script, all these variables are passed as arguments to a function which executes the query and generates CSV file.
How could this be achieved.
Thank you in advance.
Regards,
Pointers
|
|
|
|
| Re: quoting string in multiple lines [message #648261 is a reply to message #648259] |
Fri, 19 February 2016 08:21 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Is it not the same question you asked in the previous topic (http://www.orafaq.com/forum/t/200064/)?
According to EXECUTE vs. PL/SQL block - as you used two EXECUTE statements, just use (and execute) two PL/SQL blocks. The manipulation with the bind variables will stay unchanged.
I am not sure what "I could declare all the queries by assigning to PL/SQL variables inside an anonymous block." exactly means.
But, if you mean declaring the variables inside the PL/SQL blocks, you do not have to do it (change the current code), so just do not do it. Any problem?
|
|
|
|
| Re: quoting string in multiple lines [message #648266 is a reply to message #648254] |
Fri, 19 February 2016 08:47 |
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SQL*PLus command EXECUTE requires single line. You can use dash which is line continuation character (when used as last character):
SQL> variable c clob;
SQL> exec :c:= q'[select * from dual -
> where 'bb'='bb']'
PL/SQL procedure successfully completed.
SQL>
However technically it is not the same:
SQL> variable c clob;
SQL> exec :c:= q'[select * from dual -
> where 'bb'='bb']'
PL/SQL procedure successfully completed.
SQL> print c
C
-----------------------------------------------
select * from dual where 'bb'='bb'
SQL> begin
2 :c := q'[select * from dual
3 where 'bb'='bb']';
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> print c
C
-----------------------------------------------
select * from dual
where 'bb'='bb'
SQL>
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
