How to put escape character in SQL query

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

How to create pagination in oracle 11g

We can create pagination in oracle itself, while querying for data. Pagination helps us to get the specific result instead of returning all the result from database.
Lets understand this using an example:
Suppose we have a table client_table which have thousands of client information, and when we query on the table it return thousands of records or all the records which match the query. By executing the below database query, you will get all the records from database table.

e.g. SELECT * FROM client_table

But here we don't want all the record at a time, we want the records in chunk, so we can easily investigate and understand the record and make it more readable and easier to analyse. To do so, we will optimize the same query and add the pagination into it, in pagination we will specify begin and end of row, which is actually from row number to to row number which we want to fetch.  

Here is a simple example of pagination, where we are querying database to provide the range of data.

SELECT * FROM(

SELECT CT.*, rownum FROM client_table CT) rowss

WHERE rowss."ROWNUM" BETWEEN 12 and 17;

Above query will fetch the record from row number 12 to 17 and return only 6 record to you.

References:

https://docs.oracle.com/cd/B14117_01/server.101/b10759/pseudocolumns008.htm
https://stackoverflow.com/questions/4552769/sql-rownum-how-to-return-rows-between-a-specific-range
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1
https://renenyffenegger.ch/notes/development/databases/Oracle/SQL/select/first-n-rows/index 

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 .