Re: PL/SQL question/puzzle

In article <carl.pedersen-100792141726_at_kip-sn-93.dartmouth.edu> carl.pedersen_at_dartmouth.edu (L. Carl Pedersen) writes:
>In article <1992Jul8.205544.5008_at_stortek.com>, v045100_at_otis1.stortek.com
>(Jennifer Farnham) wrote:
>>
>>
>> This works in Sql*Plus:
>>
>> select '#BETWEEN ' || '''' || :control.date1 ||
>> '''' || ' and ' || '''' || :control.date2 || ''''
>> into :control.long_field
>> from dual
>>
>> The above is the original text from the form and
>> it worked.
>>
>> However when i put it into PL/SQL:
>>
>> Begin
>> select (same as above)
>> End;
>>
>> I get the following error: upon compiling, not running:
>>
>> PL/SQL error 306 at line 21, column 30:
>> wrong number or types of arguments in call to '||'
>> Sql statement ignored....
>>
>> So I looked it up (the error) and it made no sense to me.
>>
>> Why does the above work in 2.3 yet when the same text is in
>> 3.0, it doesn't work?
>
>I don't know *why*, but I think you need to wrap to_char() around the date
>field references.

Well, you *can* do this in 3.0, just not in a PL/SQL trigger. This statement will work as it did in 2.3 if it is contained in a v2 style trigger. PL/SQL is a little pickier about type coercions and you must therefore explicitly type cast from date to char (using to_char) when doing this in PL/SQL. Another observation is that there is no reason in Forms 3.0 to hit the database to contruct the value for a field as one had to in Forms 2.3.

So rather than:

Begin
  select '#BETWEEN ' || '''' || to_char(:control.date1)

         || '''' || ' and ' || '''' || to_char(:control.date2) || ''''   into :control.long_field
  from dual;
End;

One could just as easily use:

Begin
  :control.long_field := '#BETWEEN ' || '''' || to_char(:control.date1)

         || '''' || ' and ' || '''' || to_char(:control.date2) || ''''; End;

This has the obvious advantage that it doesn't hit the db unnecessarily and (for Forms 4.0) will work against non-Oracle datasources that don't support the to_char function because this is resolved in the client-side PL/SQL engine.

One last point should be made if you must issue select's from dual or other tables where you are either sure that only one record is going to be retrieved or don't care if there is more than one row that meets the query criteria. It is more efficient to use an explicitly declared cursor as opposed to one that is implicitly declared as was done above. When using an implicit cursor, two fetches must be performed to make sure that there is only one record matches the query criteria because otherwise an exception must be raised (as per ANSI).

Hope this helps,

james

James Markarian
SQL*Forms Development, Oracle Corporation Received on Tue Jul 14 1992 - 20:49:19 CEST