PL/SQL Split function

SELECT substr(main_string, position_from + 1, position_to - position_from - 1) FROM (SELECT main_string, decode(rownum - 1, 0, 0, instr(main_string, ',', 1, rownum - 1)) position_from, instr(main_string, ',', 1, rownum) position_to FROM (SELECT '111,123,aaabbb,555,' main_string FROM dual) CONNECT BY LEVEL <= length(main_string)) WHERE position_to > 0;

ARRAY IN ORACLE PL/SQL

-- BASIC SYNTAX OF ARRAY IN ORACLE PL/SQL

CREATE OR REPLACE PROCEDURE proc1(p_strarr DBMS_SQL.VARCHAR2S ) AS
BEGIN
dbms_output.put_line('Length of array = ' || p_strarr.COUNT ) ;
FOR i IN 1..p_strarr.COUNT LOOP
dbms_output.put_line('Array element '|| i ||' = '|| p_strarr(i));
END LOOP;
END;
/

-- Test it...
SET SERVEROUTPUT ON
DECLARE
v_strarr DBMS_SQL.VARCHAR2S;
BEGIN
v_strarr(1) := 'mamun';
v_strarr(2) := 'ashik';
v_strarr(3) := 'kamal';
v_strarr(4) := '';
v_strarr(5) := 'tukh';
proc1(v_strarr);
END;
/

SYNTAX OF FOR LOOP

-- for loop sysntax

set serveroutput on
declare
begin
for i in 1..10 loop
DBMS_OUTPUT.PUT_LINE('Loop controll varriable value = ' || i);
end loop;
end;
/

INSERT MULTIPLE ROWS USING SINGLE QUERRY

-- inserting multiple rows at a time by sIngle querry

insert all
when 1=1 then into month(dt, val)
select '11-JAN-2011' dt , 5 val from dual
union all
select '11-JAN-2011' dt , 7 val from dual
union all
select '11-JAN-2011' dt , 9 val from dual
union all
select '1-JAN-2011' dt , 10 val from dual

SHOWING ALL TABLE NAME

-- SHOWING ALL TABLE NAME OF AN USER

select * from ALL_TABLES
where owner='SCOTT'

INSERT MULTIPLE TABLE BY SINGLE QUERRY

-- Inserting multiple table using one single querry
-- Inserting master and chield table at a time using one single querry

insert all
when type = 'MASTER' then
into scott.mst(id, des)
values(id, des)
when type = 'DET' then
into scott.emp(empno, sal)
values(empno, sal)
select 11 id, 'new' des,1 empno,2 sal, 'MASTER' type from dual
union all
select 1,'gg',001 empno, 50 sal, 'DET' type from dual
union all
select 1,'gg', 002 empno, 44 sal, 'DET' type from dual
union all
select 1,'gg',003 empno, 33 sal, 'DET' type from dual
union all
select 1,'gg',004 empno, 44 sal, 'DET' type from dual

CINTINIOUS RATE PRINT

-- when fx rate change then value will insert the fx_rate table.
-- But we need to show the continious rate for each date from the fx_rate table.

--example
-- let the rate table is as follows :

-- date val
-- 1/1/2011 5
-- 1/10/2011 15
-- 1/15/2011 10
-- 1/20/2011 25

-- Now output will be continius rate of 1/1/2011 to 1/20/2011
-- for this the rate from 1/1/2011 to 1/9/2011 will be 5 as the rate change at 1/10/2011
-- and similarly the rate of 1/10/2011 to 1/20/2011 will be 15


create table fx_rate
(
dt date,
val integer
)
commit;

select a.*,(select val from fx_rate where dt=(select max(dt) from fx_rate where dt<=a.value_date)) value from
(
select ((select min(dt) from fx_rate) + level - 1) value_date from dual connect by
level<=((select max(dt) from fx_rate) - (select min(dt) from fx_rate) + 1)
) a