Home » SQL & PL/SQL » SQL & PL/SQL » Where Clause from table
Where Clause from table [message #245801] Mon, 18 June 2007 18:05 Go to next message
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.

Re: Where Clause from table [message #245802 is a reply to message #245801] Mon, 18 June 2007 18:12 Go to previous messageGo to next message
BlackSwan
Messages: 25049
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 Go to previous messageGo to next message
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
Re: Where Clause from table [message #245808 is a reply to message #245801] Mon, 18 June 2007 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 25049
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.
Re: Where Clause from table [message #245809 is a reply to message #245801] Mon, 18 June 2007 19:25 Go to previous messageGo to next message
tj_orafaq
Messages: 5
Registered: June 2007
Junior Member
Thanks

Thats what I'm trying to do I guess Make a valid SQl statement
Re: Where Clause from table [message #245810 is a reply to message #245801] Mon, 18 June 2007 19:29 Go to previous messageGo to next message
tj_orafaq
Messages: 5
Registered: June 2007
Junior Member
I got the solution.

Thanks
Re: Where Clause from table [message #245811 is a reply to message #245801] Mon, 18 June 2007 19:30 Go to previous messageGo to next message
BlackSwan
Messages: 25049
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.
Re: Where Clause from table [message #245813 is a reply to message #245801] Mon, 18 June 2007 19:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Where Clause from table [message #245908 is a reply to message #245852] Tue, 19 June 2007 04:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Where Clause from table [message #245942 is a reply to message #245920] Tue, 19 June 2007 06:56 Go to previous messageGo to next message
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.
Re: Where Clause from table [message #245950 is a reply to message #245942] Tue, 19 June 2007 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Where Clause from table [message #246018 is a reply to message #245942] Tue, 19 June 2007 10:23 Go to previous messageGo to next message
Frank
Messages: 7880
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...
Re: Where Clause from table [message #246234 is a reply to message #246018] Wed, 20 June 2007 06:46 Go to previous messageGo to next message
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.
Re: Where Clause from table [message #246242 is a reply to message #246234] Wed, 20 June 2007 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Where Clause from table [message #246251 is a reply to message #246242] Wed, 20 June 2007 07:34 Go to previous messageGo to next message
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
Re: Where Clause from table [message #246255 is a reply to message #246251] Wed, 20 June 2007 07:55 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Oracle UTL_FILE.Read Error (Ora-29284)
Next Topic: insert pdf & image in oracle database using sql command line syntax
Goto Forum:
  


Current Time: Fri Dec 09 09:53:18 CST 2016

Total time taken to generate the page: 0.15543 seconds