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