Basic PL/SQL Help

[Saint Nick]

I am new to Oracle Forms/Reports. Given this line of code:

        :WHERE_CLAUSE:=' AND TO_DATE(T.TRANS_DATE,'||''''||'MM/DD/YYYY'||''''||') BETWEEN '||''''||:P_FROM_DATE||''''||' AND '||''''||:P_THRU_DATE||'''';

What is up with all of the ticks? I don't understand why that is necessary.

 

[Markbnj]

If you want to embed a single quote in a query it has to be escaped... i.e. '' == '

 

[Saint Nick]

So, for example, if I wanted to insert a tick/single quote, It would be three ticks, like:

'''

 

[Markbnj]

So, for example, if I wanted to insert a tick/single quote, It would be three ticks, like:

'''

That would be one embedded single quote, and one interpreted as part of the query. In other words, if I wanted to insert the value 'ABCDE' into a varchar field (retaining the single quotes as part of the inserted value), it would look like this: '''ABCDE'''.

 

[ForumMaster]

I am new to Oracle Forms/Reports. Given this line of code:

        :WHERE_CLAUSE:=' AND TO_DATE(T.TRANS_DATE,'||''''||'MM/DD/YYYY'||''''||') BETWEEN '||''''||:P_FROM_DATE||''''||' AND '||''''||:P_THRU_DATE||'''';

What is up with all of the ticks? I don't understand why that is necessary.

as explained in this link, in oracle, a single quote ( ' ) is used to define a string literal.

double quotes are used to define a single quote when you need to input a string with a quote character.

for example, '''' as input is equivalent to ' as far oracle is concerned. the double pipe character is used as the concatenation character.
basically, the statement above will equal the following:

:where_clause := and to_date(t.trans_date,'MM/DD/YYYY') 
BETWEEN  ':P_FROM_DATE' AND ':P_THRU_DATE';

now, this is plsql code.
the :where_clause, _from_date and _thru_date are bind_variables.
but basically, you can see how all the quotes are translated into single quotes. It's not the most pleasant solution and because of that, oracle have introduced an alternative solution for inputing strings with quotes.

Instead of:
select a''''b from dual;

you get:
select q'(a'b bla bla ' ' ' ' ' ' ' ' ' ' ' )' from dual;

the second will print whatever is in the parenthesis. easier to read.

 

[Seero]

select '''' from dual;
'

select '''''' from dual;
''
select ' '' '' ' from dual;
' '

select '''a''' from dual;
'a'

select '''' || 'a' || '''' from dual;
'a'

 

[KIAman]

If you want extra credit, write a function that takes an input sql string and adds escape characters and execute it.

 

[KentState]

I hate dynamic SQL. Would be more curious why it was necessary.

 

[Saint Nick]

I hate dynamic SQL. Would be more curious why it was necessary.

We're using it in the organization so managers can create more customized reports. It's really easy to do in ColdFusion, but we're using Oracle Reports and Forms (which IMO suck ass). PL/SQL really isn't very intuitive.