Home » SQL & PL/SQL » SQL & PL/SQL » VARIABLE IN PLSQL IN WHERE CLAUSE
VARIABLE IN PLSQL IN WHERE CLAUSE [message #199182] Fri, 20 October 2006 19:25 Go to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

hi

i have a select in a plsql block

but in a clause WHERE i'm looking for a value in a variable


select count(*) from table where field like variable;
i need that my variable is as content of my field

for example
'%variable%'

but i dont know if my expression will be evaluated as variable or string...

i ´ve tried with '%||variable||%' but i dont have the results wanted...

Any idea
Thanks for your answers
Alex
Re: VARIABLE IN PLSQL IN WHERE CLAUSE [message #199183 is a reply to message #199182] Fri, 20 October 2006 19:40 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You must construct the SQL Select as a varchar2 string (complete)
then use EXECUTE IMMEDIATE
Re: VARIABLE IN PLSQL IN WHERE CLAUSE [message #199184 is a reply to message #199183] Fri, 20 October 2006 20:00 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Can somebody post an example?

i have this select:

SELECT count(*) INTO v_matcheo FROM DM08_SEPOMEX_TIPO_ASENTAMIENTO where TIPO_ASENTAMIENTO like %v_sub_espac%;

and i´ve tried with

query:= 'SELECT count(*) FROM DM08_SEPOMEX_TIPO_ASENTAMIENTO where TIPO_ASENTAMIENTO like %v_sub_espac%'; EXECUTE IMMEDIATE query INTO v_matcheo;
Procedure created.

SQL>
SQL> EXEC DM08_PROCEDURE_CP_TIPOASEN2;
BEGIN DM08_PROCEDURE_CP_TIPOASEN2; END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "DWH_MTP.DM08_PROCEDURE_CP_TIPOASEN2", line 58
ORA-06512: at line 1

i dont know how enclose the variable to define that should be in the field

Thanks again
Alex

[Updated on: Fri, 20 October 2006 20:47]

Report message to a moderator

Re: VARIABLE IN PLSQL IN WHERE CLAUSE [message #199188 is a reply to message #199184] Fri, 20 October 2006 20:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Enclose your % in quotes, not your variable.

select count(*) from table where field like '%' || variable || '%';


Ross Leishman
Re: VARIABLE IN PLSQL IN WHERE CLAUSE [message #199215 is a reply to message #199188] Sat, 21 October 2006 11:15 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Thanks now it works...

Greetings
Alex
Previous Topic: Reading the ref cursor value returned by a function
Next Topic: Char to Numeric Functionality
Goto Forum:
  


Current Time: Wed Dec 07 07:04:36 CST 2016

Total time taken to generate the page: 0.13401 seconds