Home » SQL & PL/SQL » SQL & PL/SQL » Want to se Arithmetic Operators Dynamically (Oracle 9i)
Want to se Arithmetic Operators Dynamically [message #386039] Thu, 12 February 2009 05:22 Go to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Hi ,

I have to select certain values from a table based on some condition, but till run time i don't know the arithmetic condition,
The test query looks like

select name , salary
from employees
where salary > 1000;

Now in place of > i could get anything and based on that i have to use it in where clause, if i get '<>' then my where clause will be
where salary <> 1000;

Please suggest how would i implement it my code.

Thanks.
Re: Want to se Arithmetic Operators Dynamically [message #386040 is a reply to message #386039] Thu, 12 February 2009 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you get the condition?
From where do you execute the query? SQL*Plus script? Procedure? Application?...
Solution depends on the environment.

Regards
Michel
Re: Want to se Arithmetic Operators Dynamically [message #386041 is a reply to message #386040] Thu, 12 February 2009 05:37 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
I am using this condition in pl/sql procedure

I will get the operator through procedure input and based on it i need to extract the records.

Do let me know if you need any other information.
Re: Want to se Arithmetic Operators Dynamically [message #386042 is a reply to message #386041] Thu, 12 February 2009 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You hace to use dynamic SQL: execute immediate statement or dbms_sql package or dynamic cursor [loop].

Regards
Michel

[Updated on: Thu, 12 February 2009 05:41]

Report message to a moderator

Re: Want to se Arithmetic Operators Dynamically [message #386043 is a reply to message #386039] Thu, 12 February 2009 05:43 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Suggest you read up on sql injection before you try to use it though.
Re: Want to se Arithmetic Operators Dynamically [message #386045 is a reply to message #386043] Thu, 12 February 2009 06:01 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
I have created below function
CREATE OR REPLACE FUNCTION get_num_of_employees (loc VARCHAR2)
RETURN NUMBER IS
query_str VARCHAR2(1000);
num_of_employees NUMBER;
BEGIN
query_str := 'SELECT COUNT(*) into INTO num_of_employee FROM '
|| ' employees '
|| ' WHERE rule_nbr '|| loc || ' 1 ';
EXECUTE IMMEDIATE query_str;
RETURN num_of_employees;
END;


And call it in external environment

declare
a number;
begin
a:= get_num_of_employees('=');
dbms_output.put_line(a);
end;

but i will get Missing expression error
Re: Want to se Arithmetic Operators Dynamically [message #386049 is a reply to message #386039] Thu, 12 February 2009 06:09 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
suggest you also read up on dynamic SQL


EXECUTE IMMEDIATE query_str INTO num_of_employee;

Re: Want to se Arithmetic Operators Dynamically [message #386050 is a reply to message #386043] Thu, 12 February 2009 06:09 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You cant "select into" *inside* the "execute immediate" dynamic sql. You have to use bind variables have a look at the examples here.

Also,

Quote:

Now in place of > i could get anything



So how should Oracle handle it when you get a "greener then" in stead of a "bigger then" Very Happy

If the only possibilities are <, >, = and != then hardcoded IF - Statements might be better than dynamic SQL.

Also, read the Forum Guide on how to format code in your post, and post the complete session when you get an error.


Re: Want to se Arithmetic Operators Dynamically [message #386053 is a reply to message #386039] Thu, 12 February 2009 06:21 Go to previous messageGo to next message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
I got the solutions ,and able to resolve the get the required results.
Thanks for all your help.
Re: Want to se Arithmetic Operators Dynamically [message #386054 is a reply to message #386053] Thu, 12 February 2009 06:25 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
It might be useful for others following this thread, if you were to post the code that you finally settled on
Re: Want to se Arithmetic Operators Dynamically [message #386201 is a reply to message #386054] Fri, 13 February 2009 02:08 Go to previous message
sanyadu
Messages: 30
Registered: October 2006
Location: Chicago
Member
Sure here is the sql :-
declare 
lv_char VARCHAR2(10) :=  '=' ;
lv_sql VARCHAR2(100);
lv_val_name VARCHAR2(14);
strsql VARCHAR2(100);
strsqlwher VARCHAR2(100);
strfinal VARCHAR2(100);
begin 
strsql := ' Select value_type_name from value ' ;
strsqlwher := ' WHERE value_id ' || lv_char ||  13286 ;
strfinal := strsql || strsqlwher;
execute immediate strfinal into lv_val_name ;
dbms_output.put_line(' Final query is  '|| strsql || strsqlwher);
dbms_output.put_line( 'lv_val_name is ' || lv_val_name);  
end;
 And below is the Procedure 

CREATE OR REPLACE FUNCTION get_num_of_employees (loc VARCHAR2) 
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_of_employees NUMBER;
BEGIN
    query_str := 'SELECT COUNT(*)  FROM '
        || ' rule ' 
        || ' WHERE rule_nbr '|| loc ||  1 ;
    EXECUTE IMMEDIATE query_str INTO num_of_employees ;
    RETURN num_of_employees;
END;

declare 
a number;
begin
a:= get_num_of_employees('>');
dbms_output.put_line(a);
end;

Thanks
Sandeep

[Updated on: Fri, 13 February 2009 02:13] by Moderator

Report message to a moderator

Previous Topic: Need procedure for deactivation
Next Topic: Regular Expression (3 threads merged by bb)
Goto Forum:
  


Current Time: Tue Dec 06 12:14:14 CST 2016

Total time taken to generate the page: 0.05873 seconds