1. Use table [Employee]

Write a stored procedure named Get_Employee_Detail which will accept 2 parameters.

@col_name varchar(100)
@col_value varchar(500)

col_name parameter can accept any column name from the table and col_value will accept the value for that column.

For example if I call stored procedure as

Exec Get_Employee_Detail ‘NationalIDNumber’,’ 695256908’

Output wiil give all the rows of table where NationalIDNumber = 695256908

BusinessEntityID


NationalIDNumber


OrganizationLevel


JobTitle


BirthDate


MaritalStatus


Gender


HireDate


SalariedFlag


VacationHours


SickLeaveHours


CurrentFlag

5


695256908


3




2)
Create a table with five columns A, B, C, D and E with varchar(100) as their data types. Table Name will be My_table

Write a stored procedure named Update_Records which will accept 1 parameter.

@col_name varchar(100)

If I pass any column name to this procedure, it will check if the column is already present in the table. If present it will print ‘Column already exists’. If not present, you will alter My_table and add that column. Output will be: ‘Table Altered’

For example if I call stored procedure as

Exec Update_Records ‘B’

Output will be:

Column already exists.

Exec Update_Records ‘N’

Output will be:

Table Altered

SQL> create or replace
  2  procedure p(col varchar2, val varchar2) is
  3    rc sys_refcursor;
  4    inj_test varchar2(255);
  5
  6    type elist is table of scott.emp%rowtype index by pls_integer;
  7    r elist;
  8  begin
  9
 10    select sys.dbms_assert.simple_sql_name(col) into inj_test from dual;
 11    open rc for 'select * from scott.emp where '||col||' = :1' using val;
 12
 13    fetch rc bulk collect into r;
 14    close rc;
 15
 16    for i in 1 .. r.count loop
 17      dbms_output.put_line(r(i).empno||','||r(i).ename);
 18    end loop;
 19  end;
 20  /

Procedure created.

SQL>
SQL> set serverout on
SQL> exec p('DEPTNO',10);
7782,CLARK
7839,KING
7934,MILLER