OraFAQ Forum: SQL & PL/SQL » Where Clause from table

Home » SQL & PL/SQL » SQL & PL/SQL » Where Clause from table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Where Clause from table [message #245801]

Mon, 18 June 2007 18:05

tj_orafaq
Messages: 5
Registered: June 2007

Junior Member

I have a requirement in which I need to generate a where clause for query stored in table.

Table EMP
Table:FilterStore (filter varchar2(1000))

The table filterstore has value as ename-'KING' and job='PRESIDENT'

Now I need to generate a query in whcih the where clause comes from other table..like this

select * from emp where (select filter from filterstore)

(f I run the aboe query it gives an error missing expression

Has anyone gone thru this?

Thanks for the help.

Report message to a moderator

Re: Where Clause from table [message #245802 is a reply to message #245801]

Mon, 18 June 2007 18:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read & FOLLOW posting guidelines as enumerated in the STICKY post at top of this forum.

search this forum for the many, many examples of "EXECUTE IMMEDIATE".

[Updated on: Mon, 18 June 2007 18:12] by Moderator

Report message to a moderator

Re: Where Clause from table [message #245807 is a reply to message #245801]

Mon, 18 June 2007 18:53

tj_orafaq
Messages: 5
Registered: June 2007

Junior Member

I still get the same error

SQL> declare
2 v_qry varchar2(1000);
3 v_temp varchar2(100);
4 begin
5 v_qry:='select ename from emp where (select filter from filterstore)';
6 execute immediate v_qry into v_temp;
7 dbms_output.put_line(v_temp);
8 end;
9 /
declare
*
ERROR at line 1:
ORA-00936: missing expression
ORA-06512: at line 6

Report message to a moderator

Re: Where Clause from table [message #245808 is a reply to message #245801]

Mon, 18 June 2007 19:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

IMO, using EXECUTE IMMEDIATE to do DML is a BAD, POOR, NON_SCALABLE coding "solution" & should be avoided at all costs.
EXECUTE IMMEDIATE expects/requires a valid SQL statement.
What you have attempted is simply NOT valid SQL.
You need to manually create a string variable which is a single, complete & correct SQL statement before passing it to EXECUTE IMMEDIATE.

Report message to a moderator

Re: Where Clause from table [message #245809 is a reply to message #245801]

Mon, 18 June 2007 19:25

tj_orafaq
Messages: 5
Registered: June 2007

Junior Member

Thanks

Thats what I'm trying to do I guess Make a valid SQl statement

Report message to a moderator

Re: Where Clause from table [message #245810 is a reply to message #245801]

Mon, 18 June 2007 19:29

tj_orafaq
Messages: 5
Registered: June 2007

Junior Member

I got the solution.

Thanks

Report message to a moderator

Re: Where Clause from table [message #245811 is a reply to message #245801]

Mon, 18 June 2007 19:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would help those who visit here after you, if you actually posted your solution so others can learn from what you learned.

Report message to a moderator

Re: Where Clause from table [message #245813 is a reply to message #245801]

Mon, 18 June 2007 19:50

tj_orafaq
Messages: 5
Registered: June 2007

Junior Member

I did this earlier in office but didnt work..guess it was late and my brain dumped

declare
v_qry varchar2(1000);
v_bind varchar2(1000);
begin
select filter into v_bind from filterstore;
dbms_output.put_line(v_bind);
v_qry:='select ename from emp where '||v_bind;
execute immediate v_qry into v_temp;
dbms_output.put_line(v_temp);
exception when others then
dbms_output.put_line('Exception is '||sqlerrm);
end;
/

[Updated on: Mon, 18 June 2007 19:50]

Report message to a moderator

Re: Where Clause from table [message #245852 is a reply to message #245813]

Tue, 19 June 2007 01:00

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

exception when others then
dbms_output.put_line('Exception is '||sqlerrm);

This is useless and dangerous.
If you don't put this clause, Oracle will through the exception and you will still get the message. There is no reason to display it by yourself.
"when others" without a raise "hides" the exception to the caller even if you call dbms_output to display the message. Do you have an analyzer behind it to see what error you get?

Regards
Michel

Report message to a moderator

Re: Where Clause from table [message #245908 is a reply to message #245852]

Tue, 19 June 2007 04:01

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

dbms_output.put_line('Exception is'||' '||sqlerrm)

What is the harm in using this? Why do you say that it is dangerous?

[Updated on: Tue, 19 June 2007 04:09]

Report message to a moderator

Re: Where Clause from table [message #245920 is a reply to message #245908]

Tue, 19 June 2007 05:20

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do you want to ignore ANY error?
There is no harm for the database. It just say you "it does not work, it does not".
It can hurt your application. You say "I don't care".

A light flashes on your car board, it is "your headlights are on" or "the water temperature exceeds the critical one", your answer is the same: "Nothing to care"...

Exception is the way Oracle warns you from something wrong.

Can you tell me what is the interest of "when others then dbms_output.put_line('Exception is'||' '||sqlerrm)"?

Regards
Michel

Report message to a moderator

Re: Where Clause from table [message #245942 is a reply to message #245920]

Tue, 19 June 2007 06:56

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

I can see the error message and can try debugging with the error message obtained to get errorfree code if i use SQLERRM.

I mean if my procedure returns too many rows exception, As a beginner i may not be knowing about this. So i will try and display the error message using SQLERRM so thst my debugging becomes easy.

Report message to a moderator

Re: Where Clause from table [message #245950 is a reply to message #245942]

Tue, 19 June 2007 07:13

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No problem if you execute it in interactive.
It becomes a problem if you execute it in a program.

Regards
Michel

Report message to a moderator

Re: Where Clause from table [message #246018 is a reply to message #245942]

Tue, 19 June 2007 10:23

Frank
Messages: 7901
Registered: March 2000

Senior Member

caliguardo wrote on Tue, 19 June 2007 13:56

I can see the error message and can try debugging with the error message obtained to get errorfree code if i use SQLERRM.

I mean if my procedure returns too many rows exception, As a beginner i may not be knowing about this. So i will try and display the error message using SQLERRM so thst my debugging becomes easy.

And what do you think you will see if you remove the exception handler?
Exactly the same message. Oracle even shows you the line number the error was raised on...

Report message to a moderator

Re: Where Clause from table [message #246234 is a reply to message #246018]

Wed, 20 June 2007 06:46

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

Well, when i am calling a procedure from a product that is designed in java, then then there would be an abrupt stop which is highly undesirable. In such a case, I can go for SQLERRM and the debugging becomes simple and smoother.

Report message to a moderator

Re: Where Clause from table [message #246242 is a reply to message #246234]

Wed, 20 June 2007 07:15

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your application doesn't know how to trap exception?
Very weird.
I think you have to change your development team.

Regadrs
Michel

Report message to a moderator

Re: Where Clause from table [message #246251 is a reply to message #246242]

Wed, 20 June 2007 07:34

caliguardo
Messages: 107
Registered: February 2007
Location: Chennai

Senior Member

Well, We are in the processof developing a product with different features. My product is just 4 months old. SO if u want my development team to be changed, it is me who will be fired first ... Embarassed

Embarassed

Report message to a moderator

Re: Where Clause from table [message #246255 is a reply to message #246251]

Wed, 20 June 2007 07:55

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The correct to develop, in any language, is to trap exception not to return an error message.
Exceptions are great. They raise exactly where there is an error and gives you all information about your environment when the error occurs.
If you hide them through "when other then return error messsage" you loose many of useful information.
"Exception" block should only exists for expected errors, the one you know how to handle in the program, not others.

Regards
Michel

Report message to a moderator

Previous Topic:	Oracle UTL_FILE.Read Error (Ora-29284)
Next Topic:	insert pdf & image in oracle database using sql command line syntax

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 13:02:29 CST 2024