SQL : TABLE DATA TYPE INTO Records

Here is the another new technique which I found in ORACLE sql today.

Following SQL helps to convert the SQL TABLEDATA type in to Rows.
SELECT * FROM THE
(SELECT str2tbl(str)
FROM DUAL)

str2tbl -- Procedure converts ; separated String into table.

CREATE OR REPLACE FUNCTION APPS.str2tbl( p_str IN VARCHAR2 ) return SRRDTABLETYPE
AS
l_str LONG DEFAULT p_str ';';
l_n NUMBER;
l_data SRRDTABLETYPE := SRRDTABLETYPE();
BEGIN
LOOP
l_n := INSTR( l_str, ';' );
EXIT WHEN (NVL(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1)));
l_str := SUBSTR( l_str, l_n+1 );
end LOOP;
RETURN l_data;
END;
/
This helps us to write SQL for tabledata types.

Address - Chicago

http://www.saisamsthanusa.org/
Shiridi Temple -- Chicago
1101 Foran Lane Aurora, IL-60506, USA

http://www.balaji.org/

Sri Venkateswara Swami Temple of greater Chicago
1145 W. Sullivan Rd
Aurora, IL 60506

Oracle Apps/SQL: MERGE Statement

Oracle9i introduces a new set of server functionality especially beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow, addressing all the needs of highly scalable data transformation inside the database.
One of the most exciting new features addressing the needs for ETL is the SQL statement MERGE. The new SQL combines the sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality.

MERGE INTO USING ON ()WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN [LOG ERRORS ]; CREATE TABLE employee (employee_id NUMBER(5),first_name VARCHAR2(20),last_name VARCHAR2(20),dept_no NUMBER(2),salary NUMBER(10));
INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);INSERT INTO employee VALUES (2, 'Jack', 'Cline', 20, 100000);INSERT INTO employee VALUES (3, 'Helen', 'Lofstrom', 20, 50000);INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);
CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);
INSERT INTO bonuses (employee_id) VALUES (1);INSERT INTO bonuses (employee_id) VALUES (2);INSERT INTO bonuses (employee_id) VALUES (4);INSERT INTO bonuses (employee_id) VALUES (6);INSERT INTO bonuses (employee_id) VALUES (7);COMMIT;
SELECT * FROM employee;
SELECT * FROM bonuses;
MERGE INTO bonuses bUSING ( SELECT employee_id, salary, dept_no FROM employee WHERE dept_no =20) eON (b.employee_id = e.employee_id)WHEN MATCHED THEN UPDATE SET b.bonus = e.salary * 0.1 DELETE WHERE (e.salary <> 40000);
SELECT * FROM bonuses;