Oracle PL/SQL – SQL Plus Startup Script with glogin.sql or login.sql

This article shows about how to configure the environment variable when SqlPlus – SqlPlus Command start up in order to have always a good behavior in the formatting of the result. You have to setup the Windows – Environment Variable (SQL Plus|SQL Developer)- SQLPATH with a directory. Copy then the file login.sql described below in it. This file is execute each time that you connect to a database with SQLPlus. SQLPATH in Windows environment (Windows Oracle server):  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ SQLPATH in…

Read More

Oracle – Create Table – Using Select Statement With Data or No Data

Here is a sample script: WITH data   CREATE TABLE test3 AS SELECT table_name, tablespace_name FROM all_tables;   ———————————   Without data   CREATE TABLE ctas AS SELECT table_name, tablespace_name FROM all_tables WHERE 1=2;     — For example, create a table named EMPLOYEE3 that includes all — of the column definitions from EMPLOYEE where the DEPTNO = D11.   CREATE TABLE EMPLOYEE3 AS (SELECT PROJNO, PROJNAME, DEPTNO FROM EMPLOYEE WHERE DEPTNO = ‘D11’) WITH NO DATA —

Read More

Posted in Oracle Tagged , Comments Off on Oracle – Create Table – Using Select Statement With Data or No Data
Oracle: Using the AFTER INSERT and AFTER UPDATE triggers

A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has occurred and an ‘AFTER UPDATE’ trigger after an update event has occurred. Let’s see an example for ‘AFTER INSERT’ trigger. Syntax: CREATE or REPLACE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW DECLARE variable declarations BEGIN trigger statement END; First problem: We want to insert a record…

Read More

Posted in Oracle Tagged , Comments Off on Oracle: Using the AFTER INSERT and AFTER UPDATE triggers