From: request@ifneeded.com   
      
   sybrandb@yahoo.com wrote:   
   > Terry Coccoli  wrote in message news:...   
   >   
   >>Terry Coccoli wrote:   
   >>   
   >>   
   >>>Anyone see anything wrong with this:   
   >>>   
   >>>   
   >>>create or replace function HourParser(DayTimeValue In Integer) return   
   >>>varchar2 is   
   >>>  Result varchar2(20);   
   >>>begin   
   >>>     Execute Immediate   
   >>>     'SELECT CASE (WHEN :x > 1) THEN "YES" END   
   >>>      FROM DUAL' Into Result Using DayTimeValue;   
   >>>   
   >>>Return(result);   
   >>>end HourParser;   
   >>>   
   >>   
   >>   
   >>I fixed this one.  The double quotes were replaced by consecutive single   
   >>quotes, and for a reason unknown to me, I had to remove the parentheses.   
   >>   
   >>But I have another question now.  I expanded on the above CASE statement   
   >>so that I now reference :x 10 times.  It seems that the USING clause has   
   >>to look something like   
   >>   
   >>USING DayTimeValue, DayTimeValue, DayTimeValue....,DayTimeValue (10th   
   >>iteration) or else I get an 'ORA-01008:not all bind variables bound' error.   
   >>   
   >>Is there any way to simplify the Using clause for a situation where I   
   >>need to continually reference the bind variable ?   
   >   
   >   
   > Sure, you can, but it would require you to stop misusing dynamic sql,   
   > and *Learn* Pl/sql, instead of just hacking away.   
   > Actually you don't need it all for this statement, and an ordinary   
   > select into will just do fine. In that case you can refer directly to   
   > DayTimeValue.   
   > Note: EXECUTE IMMEDIATE statements are always parsed. Using dynamic   
   > sql everywhere is the safest method to get awful performance. Seems   
   > you, without extensive reading and understanding the PL/SQL   
   > documentation, you are heading directly into a disaster.   
   >   
   > Sybrand Bakker   
   > Senior Oracle DBA   
      
      
      
   Sy, it's always interesting to read your posts first thing in the   
   morning.  I would definitely like to learn PL/SQL and I'm hoping that   
   you or someone else can point me in the right direction in this case.   
      
   I'm using dynamic SQL in this example because I'm on an 8.1.7 environ   
   and the query makes use of the CASE statement, which the PL/SQL parser   
   doesn't recognize.   
      
   How would you suggest that I do this in PL/ SQL ?   
      
   Thanks.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)