-
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
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Instead
SELECT
* FROM Table WHERE Name LIKE '%' + p_name + '%'
use
SELECT
* FROM Table WHERE Name LIKE '''%' || p_name || '%'''
Aleš Orehek
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|