Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to include a '%' in a PL/SQL script?
"Ramon F Herrera" <ramon_at_conexus.net> wrote in message
news:c9bc36ff.0403302245.6820f3e9_at_posting.google.com...
| What's the correct syntax to include a percentage sign
| in a PL/SQL script like the one below?
|
| In sqlplus the line would be something like this:
|
| SQL> SELECT parent_id FROM family WHERE company_id like '%1234';
|
| Thanks,
|
| -Ramon F. Herrera
|
| -----------------------------------------------------------------------
|
|
| CREATE OR REPLACE PROCEDURE fullid IS
|
| BEGIN
|
| DECLARE
| CURSOR traverse IS SELECT claim_id, company_id FROM hearing;
| identify VARCHAR2(512);
| parentID VARCHAR2(512);
|
| BEGIN
|
| FOR tab IN traverse LOOP
|
| identify :=
| 'SELECT parent_id FROM family WHERE company_id like ''%:here''';
|
| EXECUTE IMMEDIATE identify INTO parentID USING tab.company_id;
|
| DBMS_OUTPUT.PUT_LINE('Child ' || tab.company_id || ' Parent ' ||
parentID);
|
| END LOOP;
|
| END;
|
| END fullid;
since :here is a bind variable, it should not be included in quotation marks within your SELECT string, and you must use string concatenation
use this syntax
'SELECT parent_id FROM family WHERE company_id like ''%'' || :here';
also note:
[_] add an exception handler for both no_data_found and too_many_rows
[_] presumably company_id is a number, make sure that it really makes sense
to do a character search on it
[_] if company_id is a varchar2, then using the wildcard at the leading edge
of the search value will not be able to use an index
-- if the digits at the end of COMPANY_ID are meaningful in and of
themselves, they probably should be normalized into a separate column
;-{ mcs Received on Wed Mar 31 2004 - 05:40:17 CST