Use variable in SQL query in Oracle

In this post we will learn how to add a PL SQL variable in a SQL query.

Lets declare a variable vText in PL/SQL.

vText IN VARCHAR

Now we will use this variable in a SQL query using pipe (||) symbol before and after the variable name.

CREATE OR REPLACE PROCEDURE TESTDEV1.proc_PEOPLES(vText IN VARCHAR,result OUT SYS_REFCURSOR)ISBEGINOPEN result FORSELECTSRU.USER_ID,SRU.NAME,SRU.DESIGNATION,SRU.EMAIL_ID,SRU.LOCATION FROM SE_USER SRU WHERE NAME like '%'||vText||'%';END proc_PEOPLES;