An example of PL/SQL collection for nested table

DECLARE
   TYPE employee IS TABLE OF VARCHAR (5);
   employees   employee:= employee ('A', 'B', 'C', 'D','E');
BEGIN
   FOR i IN employees.FIRST .. employees.LAST
   LOOP
      IF employees (i) = 'C'
      THEN
         DBMS_OUTPUT.PUT_LINE (employees (i));
      END IF;
   END LOOP;
END;

[ADVICE] Using both of semicolon and slash characters at the same time

Please note that if you use semicolon and slash at the same time in sql queries, there will be executed as twice on the system.

e.g.
insert into mytable values (007,'Alper');
/

-- result of sql
-- 1 row inserted
-- 1 row inserted

-- do not use "/" with ";" to prevent this case.

Please note that if you use begin-end pl-sql code block, there should be ";" at the end of pl-sql block.

e.g.
begin
insert into mytable values (007,'Alper');
end;

-- result of sql
-- 1 row inserted

-- ";" should be existed in here.

How to modify "sql page size"?

SQL> SET PAGESIZE 100

Anymore, there will be 100 lines for each page, after executes SQL queries.

How to use to_date function?

e.g.
select to_date('2014-12-18','yyyy-mm-dd') from dual;
output: 12/18/2014

select to_date('2014-12-18 12:15','yyyy-mm-dd hh:mi') from dual;
output: 12/18/2014 12:15:00 PM

select to_date('2014-12-18 13:15:55','yyyy-mm-dd hh24:mi:ss') from dual;
output: 12/18/2014 1:15:55 PM

select to_date('2014-12-18','yyyy-mm-dd') - 7 from dual;
output: 12/11/2014

select to_char(to_date('2014-12-18','yyyy-mm-dd') + 30,'DAY') from dual;
output: SATURDAY

select to_char(to_date('2014-12-18','yyyy-mm-dd') + 365,'MONTH') from dual;
output: DECEMBER

[CONCAT] How to merge two strings as a single string?

Please run the sql lines in SQL Editor as "hr" user;

e.g.
select CONCAT('Bilgi havuzu ','at ozsoyler.blogspot.com') from dual;
result
Bilgi havuzu at ozsoyler.blogspot.com

Used database user: hr
Used database server: oracle xe

[INITCAP] How to make uppercase the first character of each word in a string?

Please run the sql lines in SQL Editor as "hr" user;

e.g.
select INITCAP('ozsoyler.blogspot.com') from dual;
result
ozsoyler.Blogspot.Com

Used database user: hr
Used database server: oracle xe