1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

help on pl/sql collections

Discussion in 'SQL PL/SQL' started by oraclefrend, Mar 24, 2010.

  1. oraclefrend

    oraclefrend Guest

    1.
    Create an anonymous PL/SQL block to do the following.

    Traditionally we use PL/SQL table of Index by Binary_interger.We would like to use a collection of index by varchar2.
    Define a PL/SQL table which has only one column that stores number with index as varchar2 type.
    Store the emp.sal in PL/SQL table with ename as index.
    Define a cursor from table emp.
    Loop through the cursor and display name from cursor variable and salary from the collection for each employee.

    2.------
    Create an anonymous PL/SQL block to do the following
    Use Oracle data dictionary to find if a FK constraint exists on table emp.deptno to dept.deptno
    If exists Display error message Constraint Exists with the name of the constraint
    else create a FK constraint using dynamic SQL.

    1. Create an object Type with the following structure.
    EMPNO NUMBER(4)
    ENAME VARCHAR2(10)
    JOB VARCHAR2(9)
    MGR NUMBER(4)
    HIREDATE DATE
    SAL NUMBER(7,2)
    COMM NUMBER(7,2)
    DEPTNO NUMBER(2)

    2. Create a collection (PL/SQL Table) for the above object type.

    II. Create an anonymous PL/SQL Block for the following.

    3. Load the following data into the collection.

    EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT



    --------------------------------------------------------------------------------
    ----------
    --------------------------------------------------------------------------------
    ----------
    --------------------------------------------------------------------------------
    ----------
    --------------------------------------------------------------------------------
    ----------
    --------------------------------------------------------------------------------
    8369 SMITH CLERK 7902 17-DEC-80 800 200 10
    8499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 10
    WARD SALESMAN 7698 22-FEB-81 1250 20
    8566 JONES MANAGER 7839 02-APR-81 2975 20
    8654 MARTIN SALESMAN 7698 28-SEP-81 1250 30
    8698 BLAKE MANAGER 7839 01-MAY-81 2850 500 30
    8782 CLARK MANAGER 7839 09-JUN-81 2450 40
    8788 SCOTT ANALYST 7566 30-FEB-87 3000 40
    8839 KING PRESIDENT 17-NOV-81 5000 20

    4. Insert the above data into emp table using bulk binds. Limit 3 records at a time.
    5. Capture the errors and display them.
    6. Commit transactions.
    --------------------------------------------------------------------------------

    4


    --------------------------------------------------------------------------------
    USE ANALYTIC FUNCTIONS for the below...

    1. In a single select query display ename, job, hitedate, sal and cumulative sal.
    2. In a single select query display ename, job, hitedate, sal and cumulative sal for each department.
    3. In a single select query display ename, job, sal, hiredate, no of days between next hire within the department.

    5

    --------------------------------------------------------------------------------
    Modify the below statement using table emp in WITH clause

    select ename, job, hitedate, sal, comm, deptname
    from emp, dept
    where emp.dept = dep.dept
    and emp.dept = 10
    union
    select ename, job, hitedate, sal, comm, deptname
    from emp, dept
    where emp.dept = dep.dept
    and emp.dept = 20
    union
    select ename, job, hitedate, sal, comm, deptname
    from emp, dept
    where emp.dept = dep.dept
    and emp.dept = 30

    6

    --------------------------------------------------------------------------------
    In a single query display the department name and total salary for each department from table scott.dept


    7


    --------------------------------------------------------------------------------

    Create a procedure p_get_emp_data with three parameters piv_col_name varchar2, piv_value varchar2, poc ref_cursor
    Based on the value in piv_col_name and piv_value query the emp table and return the result set in a Cursor.
    Validate the value of piv_col_name, it must be a valid column_name in table emp
    Hint: Use Oracle data dictionary to validate the column name.

    8

    --------------------------------------------------------------------------------
    Write a Anonymous PL/SQL block to do the following.
    1. Increase the salary of all employees other than managers by 6%+(50$+no_of_days difference with the last joined employee within the department).
    New salary of employee cannot be more the salary of his manager.
    Compute the increase in tax amount, assuming that tax is 30% of new salary. Also compute the total tax for the year.
    Do not update the record but print the department Name, Employee Name, Old salary, new salary, increase in tax, total tax for year order by department name and employee name.



    9


    --------------------------------------------------------------------------------
    1. How would you force the optimizer to use the primary key index when querying table emp.
    2. If the query plan is using the index how would you for the optimizer not to use the index.
    3. Display the query plan for the query
    select ename, job, hitedate, sal, comm, deptname
    from emp, dept
    where emp.dept = dep.dept
    and emp.dept = 10
    4. A session (sid = 112) is updating a record on emp which was locked by another session.
    Which data dictionary view will reveal the lock information?
    5. A Proc was executing fine until yesterday. Suddenly the users are complaining performance issue.
    How would you diagnose the problem. Write step by step instructions.
    6. How would to gather statistics in oracle release 8i and 9i above?
    7. Explain in few words when the optimizer would use nested loop / hash join
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A wonderful assignment for YOU to complete. Asking others to do your work for you teaches you nothing.

    Report back what you've written if it doesn't work and possibly someone can assist you.
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Oraclefrend it's very rude to actually ask others to do your assignment. Take Zargon's advice, try it out. If you get stuck at something, post your problem and we will help you out.