CODEWITHSUNDEEP
oracle-databaseplsql
user3588800
user3588800

Reputation: 11

Use of variable in LIKE condition of PL/SQL

I have a requirement where I need to search all records like '00456159/%'. In database there are two records 00456159/A and 00456159/I. But in the below code that I'm using it does not give any of the records.Can someone point out the mistake that I'm doing? Thanks,

SET SERVEROUTPUT ON;
DECLARE

Cursor c2(v_comp IN VARCHAR2) is select  distinct rate_agreement_nbr rag FROM APL_PRICING.rate_agreement 
WHERE  RATE_AGREEMENT_NBR like   ''''||v_comp||'%'||'''';

v_comp1 VARCHAR2(15);

BEGIN

v_comp1 :=SUBSTR('00456159/A',1,9);

For v_rec in c2(v_comP1)
loop
    DBMS_OUTPUT.PUT_LINE(v_rec.rag);
end loop;

END;

Upvotes: 0

Views: 2505

Answers (1)

Thomas Krojer
Thomas Krojer

Reputation: 1018

Your predicate is concatenating '''' which adds an extra literal quote character (') to the start and end of your expression. The data you're matching, however, does not have any embedded quotes, so the query does not find any matching rows.

Instead of

''''||v_comp||'%'||'''';

use this:

v_comp||'%';

Upvotes: 6

Related Questions