PLSQL

 Q- Declare a cursor to list the department name (dname), total number of employees (ttemp), total salary (ttsal), and average salary (avsal) for each department from the department table and employee table order by the department name. 
Write all department name with their total number of employees for each department using the notepad editor. 
  
For example: ACCOUNTING has 3 employees. 
(Note: Don’t use the ttemp, ttsal, and avsal item at this time) 


ANS :
set serveroutput on
declare

type t_ds is record
(
dname dept.dname%type,
ttemp  number(3),
ttsal  number(8,2),
avsal  number(8,2)
);

v_ds t_ds;

cursor  c_ds is

select dname,count(*) ttemp,sum(sal) ttsal, avg(sal) avsal
from dept d , emp e
where d.deptno=e.deptno
group by dname
order by 1;

begin
open c_ds;
loop
fetch c_ds into v_ds;
exit when c_ds%notfound;
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line(v_ds.dname||' has '||v_ds.ttemp||'  employees.');

end loop;

close c_ds;
end;
/
Q- Give the table name and Generate your control file to load data from flat file to your corresponding table.


ANS:
set echo off ver off feed off pages 0
accept tname prompt 'Enter Name of Table: '
accept dformat prompt 'Enter Format to Use for Date Columns: '

spool d:&tname.ctl

select 'LOAD DATA'|| chr (10) ||
       'INFILE ''' || lower (table_name) || '.dat''' || chr (10) ||
       'INTO TABLE '|| table_name || chr (10)||
       'FIELDS TERMINATED BY '','''||chr (10)||
       'TRAILING NULLCOLS' || chr (10) || '('
from   user_tables
where  table_name = upper ('&tname');

select decode (rownum, 1, '   ', ' , ') ||
       rpad (column_name, 33, ' ')      ||
       decode (data_type,
           'VARCHAR2', 'CHAR NULLIF ('||column_name||'=BLANKS)',
           'FLOAT',    'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
           'NUMBER',   decode (data_precision, 0,
                       'INTEGER EXTERNAL NULLIF ('||column_name||
                       '=BLANKS)', decode (data_scale, 0,
                       'INTEGER EXTERNAL NULLIF ('||
                       column_name||'=BLANKS)',
                       'DECIMAL EXTERNAL NULLIF ('||
                       column_name||'=BLANKS)')),
           'DATE',     'DATE "&dformat" NULLIF ('||column_name||'=BLANKS)', null)
from   user_tab_columns
where  table_name = upper ('&tname')
order  by column_id;

select ')'
from dual;
spool off
Q- Write a cursor to find out the total count of  each table in a schema.
ANS:


METHOD 1:
set serveroutput on
DECLARE
  v_count NUMBER;
  type t_curs is ref cursor;
  v_curs t_curs;
BEGIN
  FOR C_TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES) loop
      OPEN V_CURS FOR 'SELECT COUNT(1) FROM '||C_TABLE_NAME.TABLE_NAME;
            fetch v_curs into v_count;
            dbms_output.put_line(C_TABLE_NAME.TABLE_NAME ||'   '|| v_count );
           close v_curs;
   
  END LOOP;
END;

METHOD 2:

set serveroutput on
declare
    row_cnt number;
begin
    for x in (select table_name
                from user_tables
               order by table_name) loop
        execute immediate
            'select count(*) from '
            ||x.table_name into row_cnt;
        dbms_output.put_line(rpad(x.table_name,30)||lpad(to_char(row_cnt),7));
    end loop;
end;
/
Q- Write a trigger to track who is deleting the table at what  date and time.

ANS:

CREATE OR REPLACE TRIGGER SCOTT.EMPAUDITTRG
BEFORE DELETE
ON SCOTT.EMP
declare
v_trantype  empaudit.trantype%type;

begin
if deleting then
v_trantype := 'DELETE';

end if;

insert into empaudit  values(user,to_date(to_char(sysdate,'dd-mon-yyyy'), 'dd-mon-yyyy'),
to_date(to_char(sysdate,'hh24:mi:ss'), 'hh24:mi:ss'),v_trantype);

end;
/
Q- Write a cursor to gather schema statistics.
ANS:

connect system/password@sid;
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE

    v_datetime  VARCHAR2(19);
    v_rowcount  NUMBER :=0;
    user_schema VARCHAR(30);
   CURSOR c1 IS
        select username from dba_users where username in ('SCHEMA_NAME');
BEGIN
   OPEN c1;
   /*set transaction use rollback segment system;*/
   LOOP
      FETCH c1 INTO user_schema;
      EXIT WHEN c1%NOTFOUND;
    SELECT to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS') INTO v_datetime FROM dual;
dbms_output.put_line ('Begin Update Stats Time: '||v_datetime);
DBMS_STATS.GATHER_SCHEMA_STATS(user_schema, 10, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO', DEGREE=>DBMS_STATS.DEFAULT_DEGREE, CASCADE=>TRUE);
/* dbms_stats.gather_schema_stats (user_schema,cascade=> TRUE);*/
dbms_output.put_line ('Statistics completed for: '||user_schema);

    SELECT to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS') INTO v_datetime FROM dual;
dbms_output.put_line ('End Update Stats Time: '||v_datetime);
   END LOOP;

EXCEPTION
   WHEN OTHERS THEN
   DECLARE
      error_message varchar2(255) := SQLERRM;
   BEGIN
      DBMS_OUTPUT.PUT_LINE(error_message);
   END;
   
end;
/