How to put escape character in SQL query

SELECT * FROM SQL_TUTORIAL WHERE SQL_NAME like UPPER('%'||vSearchItem||'%')

How to create and execute procedure in oracle express edition?

We are going to create a simple procedure in Oracle, which will print a statement.

CREATE OR REPLACE PROCEDURE procedureName
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('You have successfully executed the procedure!');
END;

To execute or call the above created procedure in Oracle Application express use the following statement.

BEGIN
procedureName;
END;

Below is the screenshot:

Create and execute procedure in oracle application express edition | Screenshot

You can also download the complete exercise with code from Git .

Execute Oracle procedure using Toad

How to execute a procedure written in Oracle using Toad?

Below is the syntax to execute the stored procedure using Toad for Oracle tool.

Syntax: 

var <variable_name> refcursor
BEGIN
rocedure_name('<parameter1>','<parameter2>',:<variable_name>);

END;

print <variable_name>

e.g.
var result refcursor
BEGIN
proc_MyProcedure('jorvee',:result);
END;
print result 

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;