Issue while running a query within a procedure

Question

I have a procedure that expects a SELECT statement as a parameter. If I run the SELECT Statement out of the procedure, it runs fine, but when I run it within the procedure i get the error:

The procedure :

data_dump(query_in => 'select * from reservation where type not in ('H','PURGE','E') ',
                               file_in      => 'export_'||months_from||''||months_to||'.csv',
                              directory_in => 'C:\Users\Administrator\Desktop\test',
                              delimiter_in => '|' );

The query itself gives me the expected result.

select * from reservation where type not in ('H','PURGE','E', '|| other ||') 


The error is: encountered the symbol 'H'.

Can it be because of the single quotes around the IN condition?

getting error missing right parenthesis:

(trunc(a.update_date) between (select add_months(TRUNC(SYSDATE), -(]'|| months_from ||q'[') ) from dual) and (select add_months(TRUNC(SYSDATE), -(]'|| months_to ||q'[') from dual))]'

q-quotes should be like this: q'[text here]'. (You can use other delimiters, e.g. q'{text here}', q'#text here#'.) — William Robertson
– William Robertson, Commented May 24, 2018 at 16:05

Alex Poole · Accepted Answer · 2018-05-24 15:17:39Z

You're passing quoted arguments within a string. You need to either escape those internal quotes:

data_dump(query_in => 'select * from reservation where type not in (''H'',''PURGE'',''E'') ',
                               file_in      => 'export_'||months_from||''||months_to||'.csv',
                              directory_in => 'C:\Users\Administrator\Desktop\test',
                              delimiter_in => '|' );

Or use the alternative quoting mechanism:

data_dump(query_in => q'[select * from reservation where type not in ('H','PURGE','E') ]',
...

With your 'other' value you need to still include that in its own escape quotes:

data_dump(query_in => 'select * from reservation where type not in (''H'',''PURGE'',''E'','''|| other ||''')',
...

or (getting less readable now, perhaps):

data_dump(query_in => q'[select * from reservation where type not in ('H','PURGE','E',']' || other || q'[')]',
...

You can use dbms_output to debug this sort of thing, printing the generated argument and seeing if that matches what you've been running manually.

With your third partial code you've put closing single quotes after the (presumably numeric) variables:

(trunc(a.update_date) between (select add_months(TRUNC(SYSDATE), -(]'|| months_from ||q'[') ) from dual) and (select add_months(TRUNC(SYSDATE), -(]'|| months_to ||q'[') from dual))]'
                                                                                         ^                                                                            ^

and you are, as the message says, missing a closing parenthesis in the second add_month call; so it should be (where ... is the rest of the statement that you haven't shown):

q'[ ... (trunc(a.update_date) between (select add_months(TRUNC(SYSDATE), -(]'
  || months_from ||q'[) ) from dual) and (select add_months(TRUNC(SYSDATE), -(]'
  || months_to ||q'[) ) from dual))]'

Again, this is basic debugging you can do by printing the generated statement and inspecting it or trying to run it manually.

Also you don't need the inner select from dual stuff or all the parentheses; you can just do:

q'[ ... trunc(a.update_date) between add_months(TRUNC(SYSDATE), -]'
  || months_from ||q'[) and add_months(TRUNC(SYSDATE), -]'|| months_to ||q'[)]'

Thanks Alex, the problem with this is that i am also trying to pass a parameter, and it converts everything to string. It is my fault I didn't say it earlier. I'll update the question.
@A.D - updated to show how to wrap that in its own escaped quotes.
Thanks Alex, I tried the last method but I am getting a missing right parentheses. I updated the question and put only the condition of the select statement, can you help me to find where the mistake is? I am not sure I used the q'[]' correctly

Collectives™ on Stack Overflow

Issue while running a query within a procedure

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related