PL/SQL

How to Resolve PLS-00103: Encountered the symbol when expecting one of the

Posted on
PLS-00103 I cannot cover all error patterns of PL-00103 in this post, here is one case that encounter PLS-00103 in anonymous PL/SQL block using EXECUTE IMMEDIATE. SQL> begin   2    execute immediate ‘select nvl(first_name, ‘NO_VALUE’) from employees’;   3  end;   4  /   execute immediate ‘select nvl(first_name, ‘NO_VALUE’) from employees’;       […]
PL/SQL

How to Use Bind Variable in Oracle

Posted on
Shareable Cursor Using bind variables can make statements with minor differences become literally the same so as to share the same cursor when executions. That means, SQL engine doesn’t have to parse those similar statements every time they are executed, it reuses the shared cursor to service millions of executions. Unshareable Cursors In contrast, dynamic […]
PL/SQL

External Procedures May Consume a Lot of Memory

Posted on
External procedures are very special procedures stored outside of the database, and it can be coded by C or Java language. Since the external libraries were executed on OS-level, their behaviors are not managed and bounded by database anymore, so it could become a security leak. The users who have been granted the privilege to […]
PL/SQL

How to Identify Blocking Sessions

Posted on
Here I introduce a sample of PL/SQL code to monitor the blocking sessions every N minutes and output in a formatted report.First of all, edit the sql script.[oracle@primary01 ~]$ vi CheckBlockingSessions.sqlSET SERVEROUTPUT ON;ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’;DECLARE  V_MAX_COUNT NUMBER := 4;  V_WAIT_MIN  NUMBER := 5;  CURSOR C_BLOCKING  IS    SELECT BLOCKER.INST_ID,      BLOCKER.SID BLOCKER_SID […]