Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

passing a token with a single quote

Highlighted
Erik Cole
Honored Contributor

passing a token with a single quote

Has anyone run across this problem and found a good solution?

 

Have a request type command that runs a sql script. One of the paramenters passed is a request text field. If the field contains any quotes, such as in the phrase John's project then sqlplus gets confused.

 

sqlplus scott/tiger @my_script.sql '[REQD.VP.MY_TOKEN]'

 

If [REQD.VP.MY_TOKEN] = the phrase John's project then PPM generates the command

 

sqlplus scott/tiger @my_script.sql 'John's project'

 

which fails because it has three quotes and doesn't read it as a single parameter.

7 REPLIES
dirkf
Honored Contributor

Re: passing a token with a single quote

Hi Eric,

 

With the release of 10g, Oracle introduced a ‘q’-escape char documented nicely with examples here:
http://askanantha.blogspot.com/2007/12/q-quote-operator-introduced-in-oracle.html

With the Release 10g onwards, a new quoting mechanism is provided in the form of "q". This new quote operator allows us to choose our own quotation mark delimiter.
It could look something like this:

select (select CUS_FIRST_TOKEN(q'+[REQD.VP.ORIG_FIELD]+') from dual),(select CUS_FIRST_TOKEN(q'+[REQD.VP.ORIG_FIELD]+') from dual) from dual

This will escape the quote by adding another quote.


The rule will then work.


The fairly bad news is that this operator escapes the issue but if you have rules copying field values from source to destination fields,  it will also add the quote to the destination field value, meaning that if you have
Source-Field value ‘D’Amacourt’, you’ll have
Destination-Field value ‘D’’Amacourt’.


Thus, if you plan or actually already DO use this field as a source for yet another field, then there might be issues with THAT field. It is therefore recommended to test this operator excessively on any test-environment prior to going live with it.

 

Hope this helps.

 

Best regards,

Dirk

AlexSavencu
Honored Contributor

Re: passing a token with a single quote

Hi, Eric,

 

in UNIX you should enclose the parameters in double quotes " in order to tell the OS not to parse the string.

 

@Dirk: Eric's problem is not related to the database...

 

cheers

alex


--remember to kudos people who helped solve your problem
dirkf
Honored Contributor

Re: passing a token with a single quote

Jo Alex,

 

you are correct. The problem is that SQL believes that the text that it's parsing, when including an additional quote, needs to be truncated as the parameter closes the text. The problem is that the text values are 'interpreted instead of just read. The q-Operator has helped in the past.  Just an idea :-)

 

An alternative would be to use 'replace' in the UI-rule

 

Best regards,

Dirk

AlexSavencu
Honored Contributor

Re: passing a token with a single quote

Hey, Dirk,

 

again, the problem is not related to a sql query, it comes from how UNIX passes arguments to programs, in this case sqlplus.

 

cheers

alex


--remember to kudos people who helped solve your problem
Erik Cole
Honored Contributor

Re: passing a token with a single quote

Alex, double-quotes don't seem to make any difference. Wonder if this is a bash thing...

AlexSavencu
Honored Contributor

Re: passing a token with a single quote

Hi, Erik,

 

tell me the following:

- what is the error you got in the first place?

- what is the output of the command when executed manually (with double quotes) in bash?

- if the parameter is passed correctly from bash to sqlplus, then there might be a problem in your script - can you post the script here to see .

 

If the latter, here is a sample functional script:

set serveroutput on
set echo on

define myparameter = &1

exec dbms_output.put_line(q'[&myparameter]');

exit

 

let us know if this helps.

 

cheers

alex


--remember to kudos people who helped solve your problem
Erik Cole
Honored Contributor

Re: passing a token with a single quote

All, thanks for the input. For reference, I ended up solving this by creating a hidden field and using a field change rule to populate it with the request's description, minus any single quotes.

select
replace(q'^[REQ.DESCRIPTION]^','''','')
,
replace(q'^[REQ.DESCRIPTION]^','''','')
from dual

I then reference this field in my command text instead of the [REQ.DESCRIPTION] directly. No more single quotes for bash/sqlplus to trip over.

//Add this to "OnDomLoad" event