Where Clause – Joins

Description: The WHERE Clause servers two different purposes. One is to join two or more tables, view, or materialized views for DML. SELECT p.last_name, t.title_name FROM person p, title t WHERE p.title_1 = t.title_abbrev;   SELECT p.last_name, r.role_name FROM person p, person_role_ie i, person_role r WHERE p.person_id = i.person_id AND i.role_id = r.role_id;   Left […]
Continue reading…

Enjoyed this post? Share it!

 
 

While Loop Example

Description: Basic WHILE loop skeleton code. DECLARE salary emp.sal%TYPE; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno CONSTANT NUMBER(4) := 7902; BEGIN SELECT sal, mgr INTO salary, mgr_num FROM emp WHERE empno = starting_empno; WHILE salary < 4000 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP;   INSERT INTO […]
Continue reading…

Enjoyed this post? Share it!

 
 

With Clause: Double Alias

Description: The WITH query_name clause provides a way assign a name to a subquery block. The subquery block can then be referenced in multiple places in the query by specifying the query name. The query is optimized by treating it as either an inline view or as a temporary table. WITH <alias_one> AS (subquery_sql_statement) <alias_two> […]
Continue reading…

Enjoyed this post? Share it!

 
 

With Clause: Single Alias

Description: The WITH query_name clause provides a way assign a name to a subquery block. The subquery block can then be referenced in multiple places in the query by specifying the query name. The query is optimized by treating it as either an inline view or as a temporary table. WITH <alias_name> AS (subquery_sql_statement) SELECT […]
Continue reading…

Enjoyed this post? Share it!

 
 

View

Description: A view is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace. CREATE OR REPLACE VIEW test_v AS SELECT owner, table_name FROM all_tab_columns;   — any valid sql statement can follow the create or […]
Continue reading…

Enjoyed this post? Share it!

 
 

Timestamp_To_Scn

Description: The TIMESTAMP_TO_SCN function takes as an argument a timestamp value and returns the approximate system change number (SCN) associated with that timestamp. The returned value is of datatype NUMBER. This function is useful any time you want to know the SCN associated with a particular timestamp. TIMESTAMP_TO_SCN(<timestamp>)     — The following w3mentor inserts […]
Continue reading…

Enjoyed this post? Share it!

 
 

Users: Lock And Unlock Accounts

Description: Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. — lock an account: ALTER USER <user_name> ACCOUNT LOCK;   — unlock an account: ALTER USER <user_name> ACCOUNT UNLOCK;     — for w3mentor: ALTER USER mike ACCOUNT LOCK;   ALTER USER mike ACCOUNT UNLOCK;     — force user ‘mike’ […]
Continue reading…

Enjoyed this post? Share it!

 
 

To_Yminterval

Description: Converts a character string (CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted. TO_YMINTERVAL(<char>) RETURN YMINTERVAL   — calculate for each employee in the a date that is one year — and four months after the hire date:   SELECT hire_date, […]
Continue reading…

Enjoyed this post? Share it!

 
 

Update: Based On Multiple Returned Values

Description: Update based on multiple returned values from a query. UPDATE <table_name> <alias> SET (<column_name>,<column_name> ) = ( SELECT (<column_name>, <column_name>) FROM <table_name> WHERE <alias.column_name> = <alias.column_name>) WHERE <column_name> <condition> <value>;   /*————————————————-*/   UPDATE indirect_customerb b SET (b.class_of_trade) = (SELECT a.classoftrade FROM gpo_memberships a ,(SELECT name,importance FROM COT_importance) c WHERE a.dea = b.industry_id AND […]
Continue reading…

Enjoyed this post? Share it!

 
 

Users: View Currently Connected Users

Description: View all of the currently connected users on the system. Handy for spot security checks. SELECT SUBSTR(v_$session.USERNAME,1,15) USERNAME, SUBSTR(X.status,1,8) STATUS, SUBSTR(X.server,1,10) SERVER, SUBSTR(X.TYPE,1,10) TYPE, SUBSTR(X.event,1,20) "WAIT EVENT", DECODE(X.command, 1,’Create Table’, 2,’Insert’, 3,’Select’, 6,’Update’, 7,’Delete’, 8,’Drop’, 9,’Create Index’, 10,’Drop Index’, 12,’Drop Table’, 17,’Grant’, 26,’Lock Table’, 42,’Alter Session’, 43,’Alter User’, 44,’Commit’, 45,’Rollback’, X.command) COMMAND FROM v_$session […]
Continue reading…

Enjoyed this post? Share it!

 
 

Update: Nested Table Update Example

Description: Nested Tables are tables that have columns that are tables. The base entity table exists with its children as columns nested inside of it. CREATE Name Column WITH "ROW" TYPE AND "TABLE" TYPE CREATE OR REPLACE TYPE "ROW_NAME" AS OBJECT (FIRST VARCHAR2(60), LAST VARCHAR2(60), MIDDLE VARCHAR2(30), PREFIX VARCHAR2(10), SUFFIX VARCHAR2(10), CREATE_DATE DATE, CHANGE_DATE DATE, […]
Continue reading…

Enjoyed this post? Share it!

 
 

Users: View Specific User Info

Description: View information on a specific Oracle user. SELECT usr.username, usr.default_tablespace, usr.temporary_tablespace "TMP TBS", usr.profile, rl.granted_role, rl.admin_option, rl.default_role   FROM sys.dba_users usr, sys.dba_role_privs rl   WHERE usr.username = rl.grantee (+)   GROUP BY usr.username, usr.default_tablespace, usr.temporary_tablespace, usr.profile, rl.granted_role, rl.admin_option, rl.default_role;
Continue reading…

Enjoyed this post? Share it!

 
 

Utl_File

Description: The Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is, on the computer where the client is running. OPEN a file FOR read operations UTL_FILE.fopen( file_location IN VARCHAR2, file_name IN VARCHAR2, open_mode IN VARCHAR2, max_linesize […]
Continue reading…

Enjoyed this post? Share it!

 
 

Update: Update A Partitioned Table

Description: Update records that exist only in a single partition. UPDATE <table_name> PARTITION (<partition_name>) SET <column_name> = <value> WHERE <column_name> <condition> <value>;     — The following w3mentor updates values in a single — partition of the read_messages table:   UPDATE read_messages PARTITION (user_2153) s SET s.msg_id = 1 WHERE read_date = ‘2009-04-01’;
Continue reading…

Enjoyed this post? Share it!

 
 

Utl_File File Write To File Example

Description: Example of using UTL_FILE to write to a flat (txt) file. CREATE DIRECTORY test_dir AS ‘c:\’; — CREATE DIRECTORY test_dir AS ‘/tmp’;   DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN(‘test_dir’, ‘test_file.txt’, ‘W’); UTL_FILE.PUTF(fileHandler, ‘Writing TO a file\n’); UTL_FILE.FCLOSE(fileHandler); EXCEPTION WHEN utl_file.invalid_path THEN raise_application_error(-20000, ‘ERROR: Invalid PATH FOR file.’); END; /
Continue reading…

Enjoyed this post? Share it!

 
 

Utl_File Import Data

Description: This procedure makes use of the built-in Oracle package “UTL_FILE” and its ability to import data. PROCEDURE load_import (file_name IN VARCHAR2) IS vSFile UTL_FILE.FILE_TYPE; vNewLine VARCHAR2(2000); BEGIN vSFile := UTL_FILE.FOPEN (’c:\temp\’,file_name,’r’); IF UTL_FILE.IS_OPEN(vSFile) THEN LOOP BEGIN utl_file.get_line (vSFile, vNewLine); IF vNewLine IS NULL THEN EXIT; END IF; INSERT INTO test (fld1, fld2) VALUES (vNewLine, […]
Continue reading…

Enjoyed this post? Share it!