-
LIKE clause in PL/SQL
Hi,
Need help in this. I have a procedure which is passed a varchar parameter. It needs to do a SELECT on a table to get all rows where Name like the passed name. Here is a code I wrote:
PROCEDURE GET_NAME
(
p_name IN VARCHAR2
,p_cursor OUT dnCursor
)
IS
BEGIN
OPEN p_cursor FOR
SELECT
* FROM Table WHERE Name LIKE '%' + p_name + '%'
END GET_NAME;
This returns a invalid number error.
Whats wrong here..
Please advise
Thanks
orauser
-
Does this select work out of PL/SQL?
;)
+ is not string concatenation character, || is
-
Instead
SELECT
* FROM Table WHERE Name LIKE '%' + p_name + '%'
use
SELECT
* FROM Table WHERE Name LIKE '''%' || p_name || '%'''
-
I don't think so. That will only match patterns that start and end with a single-quote
-
Thanks for the reply. Sorry I am new to Oracle..
Trying to execute the procedure
I give this
execute PakageName.ProcedureName('Name_to_be_searched', dnOutCursor);
I get invalid SQL error
How do initialize the output cursor here and execute it?
THanks Again
orauser73
-
Hi
say at the sqlplus prompt
var c refcursor
exec PackageName.ProcedureName('Name_to_be_searched', :c);
print c - this will output ur cursor values
HTH
SS