CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
brillox
brillox

Reputation: 373

How to use a variable in a LIKE clause in PL/SQL

I am new to Oracle and learning; I am simply trying to run this T-SQL query

DECLARE @SearchObj varchar(100);
SET @SearchObj='%aldbrough%';

SELECT
      obj_id,
      name,
      description
   FROM
      agnis.t_object
   WHERE
      lower(name) = ObjToSearch ;

I am using SQL Developer Oracle tool which also have a "Scratch Editor" to help with translation from T-SQL. When i run the tool it gave me this code

     DECLARE
   v_SearchObj VARCHAR2(100);

    BEGIN
       v_SearchObj := '%aldbrough%' ;
       SELECT obj_id ,
              NAME ,
              DESCRIPTION 
         FROM agnis.t_object 
        WHERE  LOWER(NAME) = ObjToSearch;

    END;

but the same tool give me this error

    Error report -
ORA-06550: line 10, column 26:
PL/SQL: ORA-00904: "OBJTOSEARCH": invalid identifier
ORA-06550: line 6, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

so what is the correct syntax to use a variable into a LIKE clause that returns multiple rows? I hope I do not have to use cursors etc for such of a simple statement as suggested in this question

Upvotes: 0

Views: 3768

Answers (2)

MT0
MT0

Reputation: 168232

Since this is tagged SQL Developer, use a bind variable:

SELECT obj_id,
       name,
       description
FROM   agnis.t_object
WHERE  lower(name) = :ObjToSearch;

and SQL developer will pop up a dialog box where you can set the value of the ObjToSearch variable.

If you want to specify the bind variable in code then:

VARIABLE objtosearch VARCHAR2(50)

BEGIN
  :objtosearch := '%aldbrough%';
END;
/

SELECT obj_id,
       name,
       description
FROM   agnis.t_object
WHERE  lower(name) = :ObjToSearch;

And run the statements as a script using F5 rather than as individual statements.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142958

Well, yes - those "translators" don't always do what they are supposed to.

This is how your code should look like:

  • use like, not = in the where clause
  • in PL/SQL, you have to put the result of the select statement into something - for example, locally declared variables (as my example shows).

So:

DECLARE
   v_SearchObj    VARCHAR2 (100) := '%aldbrough%';
   --
   v_obj_id       t_object.obj_id%TYPE;
   v_name         t_object.name%TYPE;
   v_description  t_object.description%TYPE;
BEGIN
   SELECT obj_id, NAME, DESCRIPTION
     INTO v_obj_id, v_name, v_description
     FROM agnis.t_object
    WHERE LOWER (NAME) LIKE v_searchobj;
END;

If such a code returns an error - too_many_rows (and yes, it does), then one option is to loop through rows and do something (such as display those values):

DECLARE
   v_SearchObj  VARCHAR2 (100) := '%aldbrough%';
BEGIN
   FOR cur_r IN (SELECT obj_id, NAME, DESCRIPTION
                   FROM agnis.t_object
                  WHERE LOWER (NAME) LIKE v_searchobj)
   LOOP
      DBMS_OUTPUT.put_line (
         'Name = ' || cur_r.name || ', description = ' || cur_r.description);
   END LOOP;
END;

Upvotes: 3

Related Questions