Live ORACLE

if you are Oracle Developer ,than this Blog most likely will make you happy.

About my Blog

This Blog particularized for Oracle Developers ... you will see interesting Codes for SQL , PL/SQL as well as new ideas for Developer Suite and Client Tools that will help you in your professional life ... and I hope I reach for your satisfaction.

About Me

I'm Hany Freedom , 25 years old , I live in EL-Minia City in Egypt , I'm Moderator at ArabOUG.org the best Arabic Forum for ORACLE on the Net. if you interested to know more about me .... just Click Here.

Creating a Table with a Varray Column

DROP TABLE dept_projects CASCADE CONSTRAINTS PURGE;
DROP type projectlist;

-- Each project has a 16-character code name.
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE projectlist AS VARRAY(50) OF VARCHAR2(16);
/

CREATE TABLE dept_projects ( -- create database table
dept_id NUMBER(2),
NAME VARCHAR2(15),
budget NUMBER(11,2),
-- Each department can have up to 50 projects.
projects PROJECTLIST);


Varray Constructor Within a SQL Statement

BEGIN
INSERT INTO dept_projects
VALUES (60,
'Security',
750400,
Projectlist('New Badges','Track Computers','Check Exits'));
END;
/


Share/Save/Bookmark

Declaring a Nested Table in SQL

DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;
DROP TYPE student;
DROP TYPE CourseList;
-------------------------------------------
CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
/
CREATE TYPE student AS OBJECT ( -- create object
id_num INTEGER(4),
name VARCHAR2(25),
address VARCHAR2(35),
status CHAR(2),
courses CourseList); -- declare nested table as attribute
/
CREATE TABLE sophomores of student
NESTED TABLE courses STORE AS courses_nt
/



OUTPUT:-

20:40:43 SQL> DROP TABLE sophomores CASCADE CONSTRAINTS PURGE;

Table dropped.

Elapsed: 00:00:00.04
20:40:44 SQL> DROP TYPE student;

Type dropped.

Elapsed: 00:00:00.09
20:40:44 SQL> DROP TYPE CourseList;

Type dropped.

Elapsed: 00:00:00.06

20:40:44 SQL> CREATE TYPE CourseList AS TABLE OF VARCHAR2(10) -- define type
20:40:44 2 /

Type created.

Elapsed: 00:00:00.01
20:40:44 SQL> CREATE TYPE student AS OBJECT ( -- create object
20:40:44 2 id_num INTEGER(4),
20:40:44 3 name VARCHAR2(25),
20:40:44 4 address VARCHAR2(35),
20:40:44 5 status CHAR(2),
20:40:44 6 courses CourseList); -- declare nested table as attribute
20:40:44 7 /

Type created.

Elapsed: 00:00:00.06
20:40:44 SQL> CREATE TABLE sophomores of student
20:40:44 2 NESTED TABLE courses STORE AS courses_nt
20:40:44 3 /

Table created.

Elapsed: 00:00:00.03

Share/Save/Bookmark

Using DEREF in a SELECT INTO Statement

DECLARE
emp EMPLOYEE_TYP;
emp_ref REF EMPLOYEE_TYP;
emp_name VARCHAR2(50);
BEGIN
SELECT Ref(e)
INTO emp_ref
FROM employee_tab e
WHERE e.employee_id = 370;
-- the following assignment raises an error, not allowed in PL/SQL
-- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
-- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
SELECT Deref(emp_ref)
INTO emp
FROM dual; -- use dummy table DUAL
emp_name := emp.first_name
||' '
||emp.last_name;
dbms_output.Put_line(emp_name);
END;
/


Share/Save/Bookmark

Updating Rows in an Object Table With a REF Modifier

DECLARE
emp EMPLOYEE_TYP;
emp_ref REF EMPLOYEE_TYP;
BEGIN
SELECT Ref(e)
INTO emp_ref
FROM employee_tab e
WHERE e.employee_id = 370;
UPDATE employee_tab e
SET e.address = Address_typ('8701 College','Oakland','CA','94321')
WHERE Ref(e) = emp_ref;
END;
/


Share/Save/Bookmark

Updating and Deleting Rows in an Object Table

DECLARE
emp EMPLOYEE_TYP;
BEGIN
INSERT INTO employee_tab
VALUES (Employee_typ(370,'Robert','Myers','RMYERS','555.111.2277',
'07-NOV-04','SA_REP',8800,.12,101,110,Address_typ('540 Fillmore','San Francisco','CA','94011')));
UPDATE employee_tab e
SET e.address.street = '1040 California'
WHERE e.employee_id = 370;
DELETE FROM employee_tab e
WHERE e.employee_id = 310;
END;
/


Share/Save/Bookmark

Inserting Rows in an Object Table

DECLARE
emp EMPLOYEE_TYP;
BEGIN
INSERT INTO employee_tab
VALUES (Employee_typ(310,'Evers','Boston','EBOSTON','555.111.2222',
'01-AUG-04','SA_REP',9000,.15,101,110,Address_typ('123 Main','San Francisco','CA','94111')));
INSERT INTO employee_tab
VALUES (Employee_typ(320,'Martha','Dunn','MDUNN','555.111.3333',
'30-SEP-04','AC_MGR',12500,0,101,110,Address_typ('123 Broadway','Redwood City','CA','94065')));
END;
/


Share/Save/Bookmark

Accessing Object Attributes

DECLARE
emp EMPLOYEE_TYP;
BEGIN
emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',
'01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));
dbms_output.Put_line(emp.first_name
||' '
||emp.last_name);
dbms_output.Put_line(emp.address.street);
dbms_output.Put_line(emp.address.city
||', '
||emp.address.state
||' '
||emp.address.postal_code);
END;
/



OUTPUT:-

Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Null Objects in a PL/SQL Block

DECLARE
emp EMPLOYEE_TYP; -- emp is atomically null

BEGIN
IF emp IS NULL THEN
dbms_output.Put_line('emp is NULL #1');
END IF;
IF emp.employee_id IS NULL THEN
dbms_output.Put_line('emp.employee_id is NULL #1');
END IF;
emp.employee_id := 330;
IF emp IS NULL THEN
dbms_output.Put_line('emp is NULL #2');
END IF;
IF emp.employee_id IS NULL THEN
dbms_output.Put_line('emp.employee_id is NULL #2');
END IF;
emp := Employee_typ(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,Address_typ(NULL,NULL,NULL,NULL));
-- emp := NULL; -- this would have made the following IF statement TRUE
IF emp IS NULL THEN
dbms_output.Put_line('emp is NULL #3');
END IF;
IF emp.employee_id IS NULL THEN
dbms_output.Put_line('emp.employee_id is NULL #3');
END IF;
EXCEPTION
WHEN access_into_null THEN
dbms_output.Put_line('Cannot assign value to NULL object');
END;
/



OUTPUT:-

emp is NULL #1
emp.employee_id is NULL #1
emp is NULL #2
emp.employee_id is NULL #3

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Declaring Object Types in a PL/SQL Block

DECLARE

emp EMPLOYEE_TYP; -- emp is atomically null



BEGIN

-- call the constructor for employee_typ

emp := Employee_typ(315,'Francis','Logan','FLOGAN','555.777.2222',

'01-MAY-04','SA_MAN',11000,.15,101,110,Address_typ('376 Mission','San Francisco','CA','94222'));

dbms_output.Put_line(emp.first_name

||' '

||emp.last_name); -- display details

emp.Display_address(); -- call object method to display details



END;

/



OUTPUT:-

Francis Logan
Francis Logan
376 Mission
San Francisco, CA 94222

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Working With Object Types

DROP TYPE address_typ;

CREATE TYPE address_typ AS OBJECT
(street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
postal_code VARCHAR2(6)
);
/

DROP TYPE employee_typ;

CREATE TYPE employee_typ AS OBJECT
(employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4),
address ADDRESS_TYP,
MAP MEMBER FUNCTION get_idno RETURN NUMBER,
MEMBER PROCEDURE display_address(SELF IN OUT NOCOPY EMPLOYEE_TYP)
);
/

CREATE TYPE BODY employee_typ AS
MAP MEMBER FUNCTION Get_idno
RETURN NUMBER
IS
BEGIN
RETURN employee_id;
END;
MEMBER PROCEDURE Display_address
(SELF IN OUT NOCOPY EMPLOYEE_TYP)
IS
BEGIN
dbms_output.Put_line(first_name
||' '
||last_name);
dbms_output.Put_line(address.street);
dbms_output.Put_line(address.city
||', '
||address.state
||' '
||address.postal_code);
END;
END;
/


CREATE TABLE employee_tab OF employee_typ;


OUTPUT: ALL Feedback return without any Errors.
Share/Save/Bookmark

Using a Pipelined Table Function as an Aggregate Function

DROP TABLE gradereport CASCADE CONSTRAINTS PURGE;

CREATE TABLE gradereport (student VARCHAR2(30), subject VARCHAR2(30),
weight NUMBER, grade NUMBER);

INSERT INTO gradereport VALUES('Mark', 'Physics', 4, 4);
INSERT INTO gradereport VALUES('Mark','Chemistry', 4, 3);
INSERT INTO gradereport VALUES('Mark','Maths', 3, 3);
INSERT INTO gradereport VALUES('Mark','Economics', 3, 4);

DROP PACKAGE pkg_gpa;

CREATE PACKAGE pkg_gpa IS
TYPE gpa IS TABLE OF NUMBER;
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED;
END pkg_gpa;
/

CREATE PACKAGE BODY pkg_gpa IS
FUNCTION weighted_average(input_values SYS_REFCURSOR)
RETURN gpa PIPELINED IS
grade NUMBER;
total NUMBER := 0;
total_weight NUMBER := 0;
weight NUMBER := 0;
BEGIN
-- The function accepts a ref cursor and loops through all the input rows
LOOP
FETCH input_values INTO weight, grade;
EXIT WHEN input_values%NOTFOUND;
-- Accumulate the weighted average
total_weight := total_weight + weight;
total := total + grade*weight;
END LOOP;
PIPE ROW (total / total_weight);
RETURN; -- the function returns a single result
END;
END pkg_gpa;
/

-- the query result comes back as a nested table with a single row
-- COLUMN_VALUE is a keyword that returns the contents of a nested table
SELECT w.column_value "weighted result" FROM TABLE(
pkg_gpa.weighted_average(CURSOR(SELECT weight, grade FROM gradereport))) w;




OUTPUT:-

weighted result
---------------
3.5

Share/Save/Bookmark

Using Multiple REF CURSOR Input Variables

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types
CREATE PACKAGE refcur_pkg IS
TYPE refcur_t1 IS REF CURSOR RETURN employees%ROWTYPE;
TYPE refcur_t2 IS REF CURSOR RETURN departments%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE PACKAGE BODY refcur_pkg IS
FUNCTION g_trans(p1 refcur_t1, p2 refcur_t2)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec1 p1%ROWTYPE;
in_rec2 p2%ROWTYPE;
BEGIN
LOOP
FETCH p2 INTO in_rec2;
EXIT WHEN p2%NOTFOUND;
END LOOP;
CLOSE p2;
LOOP
FETCH p1 INTO in_rec1;
EXIT WHEN p1%NOTFOUND;
-- first row
out_rec.var_num := in_rec1.employee_id;
out_rec.var_char1 := in_rec1.first_name;
out_rec.var_char2 := in_rec1.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_num := in_rec2.department_id;
out_rec.var_char1 := in_rec2.department_name;
out_rec.var_char2 := TO_CHAR(in_rec2.location_id);
PIPE ROW(out_rec);
END LOOP;
CLOSE p1;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the g_trans table function
SELECT * FROM TABLE(refcur_pkg.g_trans(
CURSOR(SELECT * FROM employees WHERE department_id = 60),
CURSOR(SELECT * FROM departments WHERE department_id = 60)));



OUTPUT:-


Share/Save/Bookmark

Using a Pipelined Table Function For a Transformation

DROP PACKAGE refcur_pkg;

-- Define the ref cursor types and function
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.var_num := in_rec.employee_id;
out_rec.var_char1 := in_rec.first_name;
out_rec.var_char2 := in_rec.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_char1 := in_rec.email;
out_rec.var_char2 := in_rec.phone_number;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
END refcur_pkg;
/

-- SELECT query using the f_transc table function
SELECT * FROM TABLE(
refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));



OUTPUT:-


Share/Save/Bookmark

Assigning the Result of a Table Function

CREATE PACKAGE pkg1 AS
TYPE numset_t IS TABLE OF NUMBER;
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));

Share/Save/Bookmark

Checking for PLSQL Compilation Type

SELECT   TYPE,
plsql_code_type,
Count(* )
FROM dba_plsql_object_settings
WHERE plsql_code_type IS NOT NULL
GROUP BY TYPE,
plsql_code_type
ORDER BY TYPE,
plsql_code_type;



OUTPUT:-


Share/Save/Bookmark

Using NOCOPY With Parameters

DECLARE
TYPE emptabtyp IS TABLE OF employees%ROWTYPE;
emp_tab EMPTABTYP := Emptabtyp(NULL); -- initialize
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
PROCEDURE Get_time
(t OUT NUMBER)
IS
BEGIN
t := dbms_utility.get_time;
END;
PROCEDURE Do_nothing1
(tab IN OUT EMPTABTYP)
IS
BEGIN
NULL;
END;
PROCEDURE Do_nothing2
(tab IN OUT NOCOPY EMPTABTYP)
IS
BEGIN
NULL;
END;
BEGIN
SELECT *
INTO Emp_tab(1)
FROM employees
WHERE employee_id = 100;
emp_tab.Extend(49999,1); -- copy element 1 into 2..50000
Get_time(t1);
Do_nothing1(emp_tab); -- pass IN OUT parameter
Get_time(t2);
Do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
Get_time(t3);
dbms_output.Put_line('Call Duration (secs)');
dbms_output.Put_line('--------------------');
dbms_output.Put_line('Just IN OUT: '
||To_char((t2
- t1)
/ 100.0));
dbms_output.Put_line('With NOCOPY: '
||To_char((t3
- t2))
/ 100.0);
END;
/



OUTPUT:-

Call Duration (secs)
--------------------
Just IN OUT: .16
With NOCOPY: 0

PL/SQL procedure successfully completed.

Share/Save/Bookmark

Site Map

Site Map

Homepage

Last updated: 2009, November 18

/ 12 pages
Live ORACLE
Live ORACLE: November 2009
Live ORACLE: October 2009
Live ORACLE: September 2009
Live ORACLE: July 2009
Live ORACLE: June 2009
Live ORACLE: May 2009
Live ORACLE: April 2009
Live ORACLE: March 2009
Live ORACLE: February 2009
Live ORACLE: December 2008
Live ORACLE: November 2008
11/ 27 pages
Live ORACLE: password encryption package
Live ORACLE: populate a PL/SQL Table from a cursor
Live ORACLE: Profile PL/SQL code to get run-time statistics
Live ORACLE: BULK COLLECT and BULK BIND
Live ORACLE: UPDATE/DELETE in a LOOP
Live ORACLE: Delete duplicate values from a table
Live ORACLE: Convert LONG datatypes to LOBs (Large Objects)
Live ORACLE: VARRAY
Live ORACLE: Oracle database types and object tables
Live ORACLE: Count the number of rows for ALL tables in the current schema
Live ORACLE: Encoding and Decoding of Secret Messages
Live ORACLE: Spell out Numbers to Words
Live ORACLE: Check if a year is a leap year
Live ORACLE: Example of how to pass application info through to Oracle RDBMS
Live ORACLE: Display table and column comments for the current schema
Live ORACLE: Example script to demonstrate DEFAULT column values
Live ORACLE: Select the Nth highest - lowest value from a table
Live ORACLE: Reports information about your current database context
Live ORACLE: Show database version with options installed
Live ORACLE: Oracle Magazine (May/June 2008)
Live ORACLE: Oracle Magazine (July/August 2008)
Live ORACLE: Oracle Magazine (September/October 2008)
Live ORACLE: CROSS MATRIX report
Live ORACLE: SQL Hacks Source code
Live ORACLE: Oracle to Excel via Sqlplus
Live ORACLE: Oracle Magazine (November/December 2008)
Live ORACLE: BULK COLLECT and BULK BIND
12/ 41 pages
Live ORACLE: Oracle Database Design
Live ORACLE: Oracle Forms & Reports
Live ORACLE: Oracle Database Programming
Live ORACLE: Introduction on Oracle Database
Live ORACLE: Register on Oracle.com
Live ORACLE: Make a shortcut Icon for your Oracle Project
Live ORACLE: Oracle Magazine (January/February 2009)
Live ORACLE: Download and Install Oracle DB and Developer Step by Step
Live ORACLE: Foreign Key by TOAD
Live ORACLE: Package to generate HTML-type documentation for Oracle objects
Live ORACLE: Converts a string of text into seperate soundex values
Live ORACLE: NYSIIS function (an improvement on soundex)
Live ORACLE: convert numbers to chars for printing cheques amount in Indian Style
Live ORACLE: This function will convert a number to words, handy for ptinting cheques
Live ORACLE: Replace all occurences of a substring with another substring
Live ORACLE: Count the number of rows for ALL tables in current schema
Live ORACLE: List tables from schema with more than X rows
Live ORACLE: Maintain an audit log of DDL changes (alter/ drop/ create) within a schema
Live ORACLE: Access Internet Web pages from SQL or PL/SQL
Live ORACLE: Send e-mail messages and attachments from PL/SQL (using UTL_SMTP)
Live ORACLE: Send e-mail messages and attachments from PL/SQL
Live ORACLE: Send e-mail messages from PL/SQL
Live ORACLE: Execute operating system commands from PL/SQL
Live ORACLE: Demonstrate Java stored procedures
Live ORACLE: Example PL/SQL code to demonstrate Dynamic SQL
Live ORACLE: Create a database view on top of a file
Live ORACLE: writing to a file using the UTL_FILE package
Live ORACLE: Fetch Long column values piece-wise from PL/SQL
Live ORACLE: Load a binary file (images, documents, etc) into a database table
Live ORACLE: Fetch LOB column values piece-wise from PL/SQL
Live ORACLE: Algorithms
Live ORACLE: Print ASCII table
Live ORACLE: Check if a year is a leap year (FUNCTION)
Live ORACLE: Random number/ string generator package
Live ORACLE: Package with functions to convert numbers between the Decimal, Binary, Octal and Hexidecimal numbering systems
Live ORACLE: Pass Result sets (REF CURSOR) between Procedures and Functions
Live ORACLE: Oracle Database Programming
Live ORACLE: Download and Install Oracle DB and Developer Step by Step
Live ORACLE: Execute operating system commands from PL/SQL
Live ORACLE: Execute operating system commands from PL/SQL
Live ORACLE: Fetch Long column values piece-wise from PL/SQL
02/ 143 pages
Live ORACLE: Using ROWNUM
Live ORACLE: Using CURRVAL and NEXTVAL
Live ORACLE: Oracle Magazine - March/April 2009
Live ORACLE: Calling the SQL COUNT Function in PL/SQL
Live ORACLE: Substituting PL/SQL Variables
Live ORACLE: Checking SQL%ROWCOUNT After an UPDATE
Live ORACLE: Data Manipulation With PL/SQL
Live ORACLE: Using BULK COLLECT With a SELECT INTO Statement
Live ORACLE: Using the RETURNING Clause with a Record
Live ORACLE: Updating a Row Using a Record
Live ORACLE: Inserting a PL/SQL Record Using %ROWTYPE
Live ORACLE: Using SELECT INTO to Assign Values in a Record
Live ORACLE: Assigning All the Fields of a Record in One Statement
Live ORACLE: Assigning Default Values to a Record
Live ORACLE: Declaring a Nested Record
Live ORACLE: Using a Record as Parameter to a Procedure
Live ORACLE: Returning a Record from a Function
Live ORACLE: Using %ROWTYPE to Declare a Record
Live ORACLE: Declaring and Initializing Record Types
Live ORACLE: Declaring and Initializing a Simple Record Type
Live ORACLE: Incompatibility Between Package and Local Collection Types
Live ORACLE: How Invalid Subscripts are Handled With DELETE(n)
Live ORACLE: Collection Exceptions
Live ORACLE: Using the DELETE Method on a Collection
Live ORACLE: Using TRIM on Deleted Elements
Live ORACLE: Using TRIM to Decrease the Size of a Collection
Live ORACLE: Using EXTEND to Increase the Size of a Collection
Live ORACLE: Using NEXT to Access Elements of a Nested Table
Live ORACLE: Using PRIOR and NEXT to Access Collection Elements
Live ORACLE: Using FIRST and LAST With a Collection
Live ORACLE: Checking the Maximum Size of a Collection With LIMIT
Live ORACLE: Counting Collection Elements With COUNT
Live ORACLE: Checking Whether a Collection Element EXISTS
Live ORACLE: Multilevel Associative Array
Live ORACLE: Multilevel Nested Table
Live ORACLE: Multilevel VARRAY
Live ORACLE: Comparing Nested Tables with Set Operators
Live ORACLE: Comparing Two Nested Tables
Live ORACLE: Checking if a Collection Is Null
Live ORACLE: Assigning Values to Tables with Complex Datatypes
Live ORACLE: Assigning Values to VARRAYs with Complex Datatypes
Live ORACLE: Assigning Nested Tables with Set Operators
Live ORACLE: Assigning a Null Value to a Nested Table
Live ORACLE: Datatype Compatibility for Collection Assignment
Live ORACLE: Referencing an Element of an Associative Array
Live ORACLE: Referencing a Nested Table Element
Live ORACLE: Empty Varray Constructor
Live ORACLE: Combining Collection Declaration and Constructor
Live ORACLE: Collection Constructor Including Null Elements
Live ORACLE: Constructor for a Varray
Live ORACLE: Constructor for a Nested Table
Live ORACLE: NOT NULL Constraint on Collection Elements
Live ORACLE: VARRAY of Records
Live ORACLE: Specifying Collection Element Types with %TYPE and %ROWTYPE
Live ORACLE: Calling a Procedure With a Nested Table Parameter
Live ORACLE: Declaring a Procedure Parameter as a Nested Table
Live ORACLE: Declaring Collections with %TYPE
Live ORACLE: Declaring Nested Tables, Varrays, and Associative Arrays
Live ORACLE: Declaring an Associative Array
Live ORACLE: Declaring Collection Types
Live ORACLE: Using NULL as a Placeholder When Creating a Subprogram
Live ORACLE: Using the NULL Statement to Show No Action
Live ORACLE: Using a GOTO Statement to Branch an Enclosing Block
Live ORACLE: Using a NULL Statement to Allow a GOTO to a Label
Live ORACLE: Using a Simple GOTO Statement
Live ORACLE: Using EXIT With a Label in a LOOP
Live ORACLE: Using EXIT in a LOOP
Live ORACLE: Using Labels on Loops for Referencing
Live ORACLE: Using a Label for Referencing Variables Outside a Loop
Live ORACLE: Scope of the LOOP Counter Variable
Live ORACLE: Specifying a LOOP Range at Run Time
Live ORACLE: Changing the Increment of the Counter in a FOR..LOOP Statement
Live ORACLE: Using a Reverse FOR..LOOP Statement
Live ORACLE: Using a Simple FOR..LOOP Statement
Live ORACLE: Using EXIT With Labeled Loops
Live ORACLE: Using an EXIT Statement
Live ORACLE: Using the Searched CASE Statement
Live ORACLE: Using the CASE-WHEN Statement
Live ORACLE: Extended IF-THEN Statement
Live ORACLE: Using the IF-THEN-ELSIF Statement
Live ORACLE: Nested IF Statements
Live ORACLE: Using a Simple IF-THEN-ELSE Statement
Live ORACLE: Using a Simple IF-THEN Statement
Live ORACLE: Using the Function RTRIM
Live ORACLE: Comparing Character Values
Live ORACLE: Implicit Conversion
Live ORACLE: Using SUBTYPE With %TYPE and %ROWTYPE
Live ORACLE: Default Value With Subtypes
Live ORACLE: Constraints Inherited by Subprograms
Live ORACLE: Type Compatibility With the NUMBER Datatype
Live ORACLE: Using Ranges With Subtypes
Live ORACLE: Assigning Literals to an INTERVAL DAY TO SECOND Variable
Live ORACLE: Assigning Literals to an INTERVAL YEAR TO MONTH Variable
Live ORACLE: Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE
Live ORACLE: Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable
Live ORACLE: Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions
Live ORACLE: Assigning a Literal Value to a TIMESTAMP Variable
Live ORACLE: Using the Function REPLACE
Live ORACLE: Using the Function NVL
Live ORACLE: Using the Function DECODE
Live ORACLE: Using NULLs in Comparisons
Live ORACLE: Using a Search Condition With a CASE Statement
Live ORACLE: Using the WHEN Clause With a CASE Statement
Live ORACLE: Using BOOLEAN Variables in Conditional Tests
Live ORACLE: Using Comparison Operators
Live ORACLE: Short-Circuit Evaluation
Live ORACLE: Assigning a Query Result to a Variable
Live ORACLE: Assigning BOOLEAN Values
Live ORACLE: Initialization of Variables and Constants
Live ORACLE: PL/SQL Block Using Multiple and Duplicate Labels
Live ORACLE: Replace letter from word by number
Live ORACLE: Using Subprogram Qualifier With Identifiers
Live ORACLE: Using a Label Qualifier With Identifiers
Live ORACLE: Scope Rules
Live ORACLE: Using a Subprogram Name for Name Resolution
Live ORACLE: Using a Block Label for Name Resolution
Live ORACLE: Using an Alias for Column Names
Live ORACLE: Assigning Values to a Record With a %ROWTYPE Declaration
Live ORACLE: Using the %ROWTYPE Attribute
Live ORACLE: Using %ROWTYPE With Table Rows
Live ORACLE: Using %TYPE With Table Columns
Live ORACLE: Using %TYPE With the Datatype of a Variable
Live ORACLE: Creating a Database Trigger
Live ORACLE: Techniques for Calling a Standalone Procedure From SQL*Plus
Live ORACLE: Creating a Stored Subprogram
Live ORACLE: Defining an Object Type
Live ORACLE: Declaring a Record Type
Live ORACLE: Using a PL/SQL Collection Type
Live ORACLE: Creating a Package and Package Body
Live ORACLE: Using the GOTO Statement
Live ORACLE: Using the EXIT-WHEN Statement
Live ORACLE: Using WHILE-LOOP for Control
Live ORACLE: Using the FOR-LOOP
Live ORACLE: Using the IF-THEN_ELSE and CASE Statement for Conditional Control
Live ORACLE: Using %ROWTYPE with an Explicit Cursor
Live ORACLE: Processing Query Results in a LOOP
Live ORACLE: Assigning Values to Variables as Parameters of a Subprogram
Live ORACLE: Assigning Values to Variables by SELECTing INTO
Live ORACLE: Assigning Values to Variables With the Assignment Operator
Live ORACLE: Substituting PL/SQL Variables
Live ORACLE: Using NEXT to Access Elements of a Nested Table
Live ORACLE: Using the IF-THEN-ELSIF Statement
Live ORACLE: Assigning Literals to an INTERVAL DAY TO SECOND Variable
03/ 27 pages
Live ORACLE: Using ROLLBACK
Live ORACLE: Using COMMIT With the WRITE Clause
Live ORACLE: Using a Cursor Expression
Live ORACLE: Declaration of Cursor Variables in a Package
Live ORACLE: Fetching from a Cursor Variable into Collections
Live ORACLE: Fetching from a Cursor Variable into a Record
Live ORACLE: Cursor Variable with Different Return Types
Live ORACLE: Stored Procedure to Open Ref Cursors with Different Queries
Live ORACLE: Stored Procedure to Open a Ref Cursor
Live ORACLE: Checking If a Cursor Variable is Open
Live ORACLE: Passing a REF CURSOR as a Parameter
Live ORACLE: Passing Parameters to Explicit Cursors
Live ORACLE: Passing Parameters to a Cursor FOR Loop
Live ORACLE: Using a Correlated Subquery
Live ORACLE: Using a Subquery in a FROM Clause
Live ORACLE: Using a Subquery in a Cursor
Live ORACLE: Using an Alias For Expressions in a Query
Live ORACLE: Using %ROWCOUNT
Live ORACLE: Using %NOTFOUND
Live ORACLE: Using %ISOPEN
Live ORACLE: Using %FOUND
Live ORACLE: Fetching Bulk Data With a Cursor
Live ORACLE: Fetching the Same Cursor Into Different Variables
Live ORACLE: Referencing PL/SQL Variables Within Its Scope
Live ORACLE: Fetching With a Cursor
Live ORACLE: Using SQL%ROWCOUNT
Live ORACLE: Using SQL%FOUND
04/ 2 pages
Live ORACLE: The day when most of employees were Hired
Live ORACLE: Oracle Magazine - May/June 2009
05/ 35 pages
Live ORACLE: The Employee's Period by years and months
Live ORACLE: Know the Current USER of your Session by SQL Statement
Live ORACLE: Aliasing from Passing Global Variable with NOCOPY Hint
Live ORACLE: Specifying Invoker's Rights With a Procedure
Live ORACLE: Resolving PL/SQL Functions With Inheritance
Live ORACLE: Resolving PL/SQL Procedure Names
Live ORACLE: Overloading a Subprogram Name
Live ORACLE: Procedure with Default Parameter Values
Live ORACLE: Using the OUT Mode
Live ORACLE: Subprogram Calls Using Positional, Named, and Mixed Notation
Live ORACLE: Formal Parameters and Actual Parameters
Live ORACLE: Forward Declaration for a Nested Subprogram
Live ORACLE: Simple PL/SQL Function
Live ORACLE: Simple PL/SQL Procedure
Live ORACLE: Dynamic SQL Fetching into a Record
Live ORACLE: Accessing %ROWCOUNT For an Explicit Cursor
Live ORACLE: Using Duplicate Placeholders With Dynamic SQL
Live ORACLE: Oracle Forms Tutorial (www.aboutoracleapps.com)
Live ORACLE: Dynamic SQL Inside FORALL Statement
Live ORACLE: Dynamic SQL with RETURNING BULK COLLECT INTO Clause
Live ORACLE: Dynamic SQL with BULK COLLECT INTO Clause
Live ORACLE: Dynamic SQL Procedure that Accepts Table Name and WHERE Clause
Live ORACLE: Calling an Autonomous Function
Live ORACLE: Using Autonomous Triggers
Live ORACLE: Declaring an Autonomous Trigger
Live ORACLE: Declaring an Autonomous PL/SQL Block
Live ORACLE: Declaring an Autonomous Standalone Procedure
Live ORACLE: Declaring an Autonomous Function in a Package
Live ORACLE: Using %ROWTYPE with Cursors reference ROWID
Live ORACLE: Fetching Across COMMITs Using ROWID
Live ORACLE: Fetching Across Commits
Live ORACLE: Using CURRENT OF to Update the Latest Row Fetched From a Cursor
Live ORACLE: Using SET TRANSACTION to Begin a Read-only Transaction
Live ORACLE: Reusing a SAVEPOINT With ROLLBACK
Live ORACLE: Using SAVEPOINT With ROLLBACK
06/ 3 pages
Live ORACLE: Oracle Reports Online Help (Help Contents)
Live ORACLE: Oracle Forms Online Help
Live ORACLE: Oracle Magazine - July/August 2009
07/ 2 pages
Live ORACLE: Delete Value from LOV after selecting it
Live ORACLE: Delete Value from LOV after selecting it
09/ 1 pages
Live ORACLE: Oracle Magazine - September/October 2009
10/ 7 pages
Live ORACLE: Creating the emp_admin Package
Live ORACLE: Matching Package Specifications and Bodies
Live ORACLE: A Simple Package Specification Without a Body
Live ORACLE: Aliasing from Assigning Cursor Variables to Same Work Area
Live ORACLE: Aliasing Passing Same Parameter Multiple Times
Live ORACLE: SCROLL_VIEW Built-in
Live ORACLE: November/December 2009
11/ 47 pages
Live ORACLE: Using DEREF in a SELECT INTO Statement
Live ORACLE: Updating Rows in an Object Table With a REF Modifier
Live ORACLE: Updating and Deleting Rows in an Object Table
Live ORACLE: Inserting Rows in an Object Table
Live ORACLE: Accessing Object Attributes
Live ORACLE: Null Objects in a PL/SQL Block
Live ORACLE: Declaring Object Types in a PL/SQL Block
Live ORACLE: Site Map
Live ORACLE: Working With Object Types
Live ORACLE: Using a Pipelined Table Function as an Aggregate Function
Live ORACLE: Using Multiple REF CURSOR Input Variables
Live ORACLE: Using a Pipelined Table Function For a Transformation
Live ORACLE: Assigning the Result of a Table Function
Live ORACLE: Checking for PLSQL Compilation Type
Live ORACLE: Using NOCOPY With Parameters
Live ORACLE: SET_CANVAS_PROPERTY Built-in
Live ORACLE: Using FORALL With BULK COLLECT
Live ORACLE: Using BULK COLLECT With the RETURNING INTO Clause
Live ORACLE: Using LIMIT to Control the Number of Rows In a BULK COLLECT
Live ORACLE: Bulk-Fetching from a Cursor Into a Collection of Records
Live ORACLE: Bulk-Fetching from a Cursor Into One or More Collections
Live ORACLE: Using the Pseudocolumn ROWNUM to Limit Query Results
Live ORACLE: Retrieving Query Results With BULK COLLECT
Live ORACLE: Bulk Operation That Continues Despite Exceptions
Live ORACLE: Counting Rows Affected by FORALL With %BULK_ROWCOUNT
Live ORACLE: Using %BULK_ROWCOUNT With the FORALL Statement
Live ORACLE: Using Rollbacks With FORALL
Live ORACLE: Using FORALL with Non-Consecutive Index Values
Live ORACLE: Using FORALL with Part of a Collection
Live ORACLE: Issuing DELETE Statements in a Loop
Live ORACLE: Nesting a Query to Improve Performance
Live ORACLE: Using the DBMS_WARNING Package to Display Warnings
Live ORACLE: Controlling the Display of PL/SQL Warnings
Live ORACLE: Using a Locator Variable to Identify the Location of an Exception
Live ORACLE: Retrying a Transaction After an Exception
Live ORACLE: Continuing after an Exception Is Raised
Live ORACLE: Displaying SQLCODE and SQLERRM
Live ORACLE: How PL/SQL Exceptions Propagate
Live ORACLE: Reraising a PL/SQL Exception
Live ORACLE: Scope of an Exception
Live ORACLE: Using RAISE to Force a Pre-Defined Exception
Live ORACLE: Using RAISE to Force a User-Defined Exception
Live ORACLE: Raising an Application Error With raise_application_error
Live ORACLE: Scope of PL/SQL Exceptions
Live ORACLE: Managing Multiple Errors With a Single Exception Handler
Live ORACLE: Runtime Error Handling
Live ORACLE: Using PUT_LINE in the DBMS_OUTPUT Package




Share/Save/Bookmark
Newer Posts Older Posts Home Page
 
http://www.dpriver.com/images/sqlpp-banner-2.png

Thanks for reading my Blog ... you Visitor Number :-