| To use LIKE in dynamic SQL [message #627979] |
Tue, 18 November 2014 09:22  |
 |
gopal.biswal
Messages: 11
Registered: October 2013
Location: Pune
|
Junior Member |
|
|
Hi,
I would like to use LIKE in dynamic SQL and receving below error while trying to do that.
Could you please let me know how to use this.
CREATE TABLE DYN_TEST
(A NUMBER, B VARCHAR2(10) );
DECLARE
V_CUR SYS_REFCURSOR;
V_VAR VARCHAR2(10):='a';
BEGIN
OPEN V_CUR FOR 'SELECT * FROM DYN_TEST WHERE B = :P_B' USING V_VAR;
END;
Above block runs successfully.
DECLARE
V_CUR SYS_REFCURSOR;
V_VAR VARCHAR2(10):='a';
BEGIN
OPEN V_CUR FOR 'SELECT * FROM DYN_TEST WHERE B LIKE ''%:P_B%''' USING V_VAR;
END;
While trying to execute above mentioned PL/SQL block, I am receiving below error:
Error starting at line 3 in command:
DECLARE
V_CUR SYS_REFCURSOR;
V_VAR VARCHAR2(10):='a';
BEGIN
OPEN V_CUR FOR 'SELECT * FROM DYN_TEST WHERE B LIKE ''%:P_B%''' USING V_VAR;
END;
Error report:
ORA-01006: bind variable does not exist
ORA-06512: at line 5
01006. 00000 - "bind variable does not exist"
*Cause:
*Action:
Regards;
Gopal
|
|
|
|
| Re: To use LIKE in dynamic SQL [message #627980 is a reply to message #627979] |
Tue, 18 November 2014 09:49  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The code you're using generates a dynamic string like this:
SELECT * FROM DYN_TEST WHERE B LIKE '%:P_B%'
There is no bind variable there, just a text string with contents that happens to resemble the name of a bind variable.
You need to generate a string either like this:
SELECT * FROM DYN_TEST WHERE B LIKE '%'||:P_B||'%'
or like this:
SELECT * FROM DYN_TEST WHERE B LIKE :P_B
In the later case the value you supply for the bind needs to contain the wildcard characters(%).
|
|
|
|
| Re: To use LIKE in dynamic SQL [message #627981 is a reply to message #627979] |
Tue, 18 November 2014 09:56 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Hello Gopal,
How about following the posting guidelines. It has been made for some purpose, isn't it?
Did you search for this issue? I bet you didn't.
Anyway, you could do it as,
like :variable or like '%' || :variable || '%'
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
