Copy-table-structure-or-Data

Below code provide information creating new table as a replica from existing table or to create structure with similar data.

Such situation occurs when one try to take backup of existing DB table or Table with Data

1) Creating blank structure from existing table

--Execute on sql prompt

begin
  execute immediate 'create table emp1 as select * from emp where 1=2';
end;

--Execute on sql prompt
select count(1) from emp1;


2) Creating structure from existing table with data

--Execute on sql prompt
begin
  execute immediate 'create table emp1 as select * from emp';
end;

--Execute on sql prompt

select count(1) from emp1;

plsql-table-or-associated-array

Sample prorams for plsql table or associated array.

DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000; salary_list('Minakshi') := 75000; salary_list('Martin') := 100000; salary_list('James') := 78000;
-- printing the table name := salary_list.FIRST; WHILE name IS NOT null
LOOP dbms_output.put_line ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); name := salary_list.NEXT(name); END LOOP;
END;
/

DECLARE
CURSOR c_customers is select name from customers;
TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
name_list c_list; counter integer :=0;
BEGIN
FOR n IN c_customers LOOP counter := counter +1;
name_list(counter) := n.name; dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
END LOOP;
END;
/

Oracle-GTTs

GTTS: Global Temporary Tables
Data stored in table to be referred during the session i.e. data in a temporary table is private to the session and purpose can be as follows:

1) Calculation
2) To maintain some data in temp state to process some operation

on commit delete rows
on commit preserve rows till session last

Oracle-Package-Benifits

The purpose of writing packages in oracle to implement the concept of modular methodology which explained as follows: 

1)Consolidate logical procedure and functional under one package 

2)There is way to define member variable in global and can be accessed with in package or outside packages 

3)The program units defined in package will be loaded at once in memory for processing and reduces context switching time

4)Program units can be make private

5)Package body can be re-compiled in isolation and will not invalidate dependencies 

5) Program units can be Overloaded