‘8. PL/SQL’

PL/SQL: Cursor

What Is a Cursor? Cursor is a temporary memory or context area where Oracle executes SQL statements. Oracle associates each SELECT statement with a cursor to hold the query information in this context area. How Many Types of Cursors Supported in PL/SQL? There are two types of cursors – implicit and explicit cursor. Implicit cursor: […]

March 13, 2014 В· Sanjay В· No Comments
Posted in: 8. PL/SQL

PL/SQL Different options for sql prompt setting

Different options for setting 1. SPOOL SPOOL D:\LOG_FILE.LOG APPEND SPOOL D:\LOGFILE.SQL APPEND NOTES: 1. Append will append if spool file is already existing. 2. You must spool off in order to complete the spool. 3. Catching OS and PL/SQL errors. WHENEVER SQLERROR: For PL/SQL errors. WHENEVER OSERROR: For operating system errors. WHENEVER OSERROR EXIT 9 […]

September 30, 2012 В· Sanjay В· No Comments
Posted in: 8. PL/SQL

PL/SQL SET Command

Let’s see how to set the ‘SET to get the required output. 1. SET TERM ON/OFF TERM = ON will display on terminal screen (OFF = show in LOG only) 2. SET ECHO ON/OFF ECHO = ON will Display the command on screen (+ spool) ECHO = OFF will Display the command on screen but […]

September 30, 2012 В· Sanjay В· No Comments
Posted in: 8. PL/SQL

Move segments into another tablespace

Below script can be used to move the segments from one tablespace to another. Moreover you can provide the tablespaces name during execution of script. Pre Requisites: 1. TABLESPACES, THOSE WILL BE PROVIDED DURING RUN TIME, MUST BE EXISTING BEFORE YOU EXECUTE THIS SCRIPT. 2. SCHEMA OWNER, WHICH WILL BE PROVIDED DURING RUN TIME, MUST […]

September 28, 2012 В· Sanjay В· No Comments
Posted in: 1. DB Admin, 3. All Scripts, 8. PL/SQL