By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

Missing Parenthese Error: ORA-00907

P: n/a
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;

Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 ?

Jul 19 '05 #2

P: n/a
Terry Coccoli <re*****@ifneeded.com> wrote in message news:<Kr**********************@news.easynews.com>. ..
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
Jul 19 '05 #3

P: n/a
sy******@yahoo.com wrote:
Terry Coccoli <re*****@ifneeded.com> wrote in message news:<Kr**********************@news.easynews.com>. ..
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.

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.