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;
/