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!

 
 

Where Clause – Conditions

Description: The WHERE Clause servers two different purposes. One is to limit the records affected by a DML statement. SELECT sal FROM emp WHERE deptno = 30;
Continue reading…

Enjoyed this post? Share it!

 
 

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!

 
 

Update: Update Based On A Record

Description: Update a table based on the value or presence of a record. UPDATE <table_name> SET ROW = <record_name> WHERE <column_name> <condition> <value>;   CREATE TABLE my_table AS SELECT table_name, tablespace_name FROM all_tables;   SELECT DISTINCT tablespace_name FROM my_table;   DECLARE trec my_table%ROWTYPE; BEGIN trec.table_name := ‘DUAL’; trec.tablespace_name := ‘NEW_TBSP’;   UPDATE my_table SET ROW […]
Continue reading…

Enjoyed this post? Share it!

 
 

Subst And Instr Together

Description: Using SUBST and INSTR together allow you to do complex extractions of characters from strings. — String parsing using both SUBSTR And INSTR     – get ALL characters up TO the FIRST comma SELECT SUBSTR(’abc,def,ghi’, 1 ,INSTR(’abc,def,ghi’, ‘,’, 1, 1)-1) FROM dual;     — get the character(s) in between the commas SELECT […]
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!