Imagine you have to drop an Oracle user and create it with all privs/roles again.  This often happens in test cycle of 3rd party products. The privs are sent to user on ad hoc basis to get around the installation errors and then comes requirement to replicate it on another server.  How do you do it?  Attached script will create a spool file for user granted roles, object Privileges

set serveroutput on
set feedback off
set verify off
declare
test varchar2(10000);
h number;
j number := 0;
begin
dbms_output.enable(10000);
–prompt enter the user name accept user
dbms_output.put_line(‘***********************************************************************’);
dbms_output.put_line(‘The Roles granted to the users are ‘);
dbms_output.put_line(‘***********************************************************************’);
select DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,upper(‘&&USER’)) into test from dual;
for i in 1..ceil(length(test)/255)
loop
dbms_output.put_line(substr(test,j,255));
j := j+255;
end loop;
dbms_output.put_line(‘***********************************************************************’);
j := 0;
dbms_output.put_line(‘The System privileges are ‘);
dbms_output.put_line(‘***********************************************************************’);
select DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,upper(‘&&USER’)) into test from dual;
for i in 1..ceil(length(test)/255)
loop
dbms_output.put_line(substr(test,j,255));
j := j+255;
end loop;
dbms_output.put_line(‘************************************************************************’);
j := 0;
dbms_output.put_line(‘The Object level privileges are ‘);
dbms_output.put_line(‘***********************************************************************’);
select DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,upper(‘&&USER’)) into test from dual;
for i in 1..ceil(length(test)/255)
loop
dbms_output.put_line(substr(test,j,255));
j := j+255;
end loop;
dbms_output.put_line(‘*************************************************************************’);
end;

Sample OUTPUT produced

Enter value for user: SYSTEM
***********************************************************************
The Roles granted to the users are
***********************************************************************
GRANT “DBA” TO “SYSTEM” WITH ADMIN OPTION
GRANT “AQ_ADMINISTRATOR_ROLE” TO “SYSTEM” WITH ADMIN OPTION
GRANT “MGMT_USER” TO “SYSTEM”

***********************************************************************
The System privileges are
***********************************************************************
GRANT GLOBAL QUERY REWRITE TO “SYSTEM”
GRANT CREATE MATERIALIZED VIEW TO “SYSTEM”
GRANT SELECT ANY TABLE TO “SYSTEM”
GRANT CREATE TABLE TO “SYSTEM”
GRANT UNLIMITED TABLESPACE TO”SYSTEM” WITH ADMIN OPTION

************************************************************************
The Object level privileges are
***********************************************************************
GRANT ALTER ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT DELETE ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT INDEX ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT INSERT ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT SELECT ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT UPDATE ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT REFERENCES ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT ON COMMIT REFRESH ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT QUERY REWRITE ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT DEBUG ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT FLASHBACK ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT ALTER ON “SYS”.”INCVID” TO “SYSTEM”
GRANT DELETE ON”SYS”.”INCVID” TO “SYSTEM”
GRANT INDEX ON “SYS”.”INCVID” TO “SYSTEM”
GRANT INSERT ON”SYS”.”INCVID” TO “SYSTEM”
GRANT SELECT ON “SYS”.”INCVID” TO “SYSTEM”
GRANT UPDATE ON “SYS”.”INCVID” TO “SYSTEM”
GRANT REFERENCES ON “SYS”.”INCVID” TO “SYSTEM”
GRANT ON COMMIT REFRESH ON “SYS”.”INCVID” TO “SYSTEM”
GRANT QUERY REWRITE ON “SYS”.”INCVID” TO “SYSTEM”
GRANT DEBUG ON “SYS”.”INCVID” TO “SYSTEM”
GRANT FLASHBACK ON “SYS”.”INCVID” TO “SYSTEM”
GRANT ALTER ON “SYS”.”INCFIL” TO”SYSTEM”
GRANT DELETE ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT INDEX ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT INSERT ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT SELECT ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT UPDATE ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT REFERENCES ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT ON COMMIT REFRESH ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT QUERY REWRITE ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT DEBUG ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT FLASHBACK ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_ALERT” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_SYS_ERROR” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_RULE_EXIMP” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQ” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQADM” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQ_IMPORT_INTERNAL” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQELM” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_TRANSFORM_EXIMP” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”SYS_GROUP” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_DEFER_IMPORT_INTERNAL” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_REPCAT” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”SET_TABLESPACE” TO”SYSTEM”
GRANT EXECUTE ON “SYS”.”CHECK_UPGRADE” TO “SYSTEM”
*************************************************************************

My Statspack has following details.

Enqueue activity DB/Inst: Snaps: 1791-1808
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Above list indicates oracle was waiting about 4 secs to acquire a lock. Let’s look at row contention and enqueues closely.

I was looking on google and stumbled across exceptional article “Diagnose performance problems, using the wait interface in Oracle 10g.”
John, the DBA at Acme Bank, is on the phone with an irate user, Bill, who complains that his database session is hanging, a complaint not unfamiliar to most DBAs. What can John do to address Bill’s complaint?
Acme Bank’s database is Oracle Database 10g, so John has many options. Automatic Database Diagnostic Manager (ADDM), new in Oracle Database 10g, can tell John about the current overall status and performance of the database, so John starts with ADDM to determine whether what Bill’s session is experiencing is the result of a databasewide issue. The ADDM report identifies no databasewide issues that could have this impact on Bill’s session, so John moves on to the next option.

One way to diagnose session-level events such as Bill’s is to determine whether the session is waiting for anything, such as the reading of a block of a file, a lock on a table row, or a latch. Oracle has provided mechanisms to display the waits happening inside the database since Oracle7, and during the last several years, the model has been steadily perfected, with more and more diagnostic information added to it. In Oracle Database 10g, which makes significantly improved wait event information available, diagnosing a session slowdown has become even easier. This article shows you how to use the wait events in Oracle Database 10g to identify bottlenecks.

Session Waits : How can John the DBA determine what’s causing Bill’s session to hang? Actually, the session is not hanging; it’s waiting for an event to happen, and that’s exactly what John checks for.

To continue his investigation, John could use Oracle Enterprise Manager or he could directly access V$ views from the command line. John has a set of scripts he uses to diagnose these types of problems, so he uses the command line.

John queries the V$SESSION view to see what Bill’s session is waiting for. (Note that John filters out all idle events.)

select sid, username, event, blocking_session,
seconds_in_wait, wait_time
from v$session where state in (‘WAITING’)
and wait_class != ‘Idle’;

The output follows, in vertical format.
SID : 270
USERNAME : BILL
EVENT : enq: TX – row lock
contention
BLOCKING_SESSION : 254
SECONDS_IN_WAIT : 83
WAIT_TIME : 0

Looking at this information, John immediately concludes that Bill’s session with SID 270 is waiting for a lock on a table and that that lock is held by session 254 (BLOCKING_SESSION).

But John wants to know which SQL statement is causing this lock. He can find out easily, by issuing the following query joining the V$SESSION and V$SQL views:

select sid, sql_text
from v$session s, v$sql q
where sid in (254,270)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);

Listing 1 shows the result of the query. And there (in Listing 1) John sees it?both sessions are trying to update the same row. Unless session 254 commits or rolls back, session 270 will continue to wait for the lock. He explains this to Bill, who, considerably less irate now, decides that something in the application has gone awry and asks John to kill session 254 and release the locks.

Wait Classes After John kills the blocking session, Bill’s session continues but is very slow. John decides to check for other problems in the session. Again, he checks for any other wait events, but this time he specifically checks Bill’s session.

In Oracle Database 10g, wait events are divided into various wait classes, based on their type. The grouping of events lets you focus on specific classes and exclude nonessential ones such as idle events. John issues the following against the V$SESSION_WAIT_CLASS view:

select wait_class_id, wait_class,
total_waits, time_waited
from v$session_wait_class
where sid = 270;

The output, shown in Listing 2, shows the wait classes and how many times the session has waited for events in each class. It tells John that application-related waits such as those due to row locks have occurred 17,760 times, for a total of 281,654 centiseconds (cs)?hundredths of a second?since the instance started. John thinks that this TIME_WAITED value is high for this session. He decides to explore the cause of these waits in the application wait class. The times for individual waits are available in the V$SYSTEM_EVENT view. He issues the following query to identify individual waits in the application wait class (class id 4217450380):

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and wait_class_id = 4217450380;

Listing 3 shows the output of this query. It shows that lock contentions (indicated by the event enq: TX – row lock contention) constitute the major part of the waiting time in the application wait class. This concerns John. Is it possible that a badly written application made its way through to the production database, causing these lock contention problems?

Being the experienced DBA that he is, however, John does not immediately draw that conclusion. The data in Listing 3 merely indicates that the users have experienced lock-contention-related waits a total of 2,275 times, for 280,856 cs. It is possible that mostly 1- or 2-cs waits and only one large wait account for the total wait time, and in that case, the application isn’t faulty. A single large wait may be some freak occurrence skewing the data and not representative of the workload on the system. How can John determine whether a single wait is skewing the data?

Oracle 10g provides a new view, V$EVENT_HISTOGRAM, that shows the wait time periods and how often sessions have waited for a specific time period. He issues the following against V$EVENT_HISTOGRAM:

select wait_time_milli bucket, wait_count
from v$event_histogram
where event =
‘enq: TX – row lock contention’;
The output looks like this:

BUCKET WAIT_COUNT
———– ———-
1 252
2 0
4 0
8 0
16 1
32 0
64 4
128 52
256 706
512 392
1024 18
2048 7
4096 843

The V$EVENT_HISTOGRAM view shows the buckets of wait times and how many times the sessions waited for a particular event?in this case, a row lock contention?for that duration. For example, sessions waited 252 times for less than 1 millisecond (ms), once less than 16
ms but more than 1 ms, and so on. The sum of the values of the WAIT_COUNT column is 2,275, the same as the value shown in the event enq: TX – row lock contention, shown in Listing 3. The V$EVENT_HISTOGRAM view shows that the most waits occurred in the ranges of 256 ms, 512 ms, and 4,096 ms, which is sufficient evidence that the applications are experiencing locking issues and that this locking is the cause of the slowness in Bill’s session. Had the view showed numerous waits in the 1-ms range, John wouldn’t have been as concerned, because the waits would have seemed normal.

Time Models : Just after John explains his preliminary findings to Bill, Lora walks in with a similar complaint: Her session SID 355 is very slow. Once again, John looks for the events the session is waiting for, by issuing the following query against the V$SESSION_WAIT view:

select event, seconds_in_wait,
wait_time
from v$session_wait
where sid = 355;

The output, shown in Listing 4, shows a variety of wait events in Lora’s session, including latch contention, which may be indicative of an application design problem. But before he sends Lora off with a prescription for an application change, John must support his theory that bad application design is the cause of the poor performance in Lora’s session. To test this theory, he decides to determine whether the resource utilization of Lora’s session is extraordinarily high and whether it slows not only itself but other sessions too.

In the Time Model interface of Oracle Database 10g, John can easily view details of time spent by a session in various activities. He issues the following against the V$SESS_TIME_MODEL view:

select stat_name, value
from v$sess_time_model
where sid = 355;

The output, shown in Listing 5, displays the time (in microseconds) spent by the session in various places. From this output, John sees that the session spent 503,996,336 microseconds parsing (parse time elapsed), out of a total of 878,088,366 microseconds on all SQL execution (sql execute elapsed time), or 57 percent of the SQL execution time, which indicates that a cause of this slowness is high parsing. John gives Lora this information, and she follows up with the application design team.

OS Statistics :While going over users’ performance problems, John also wants to rule out the possibility of the host system’s being a bottleneck. Before Oracle 10g, he could use operating system (OS) utilities such as sar and vmstat and extrapolate the metrics to determine contention. In Oracle 10g, the metrics at the OS level are collected automatically in the database. To see potential host contention, John issues the following query against the V$OSSTAT view:

select * from v$osstat;

The output in Listing 6 shows the various elements of the OS-level metrics collected. All time elements are in cs. From the results in Listing 6, John sees that the single CPU of the system has been idle for 51,025,805 cs (IDLE_TICKS) and busy for 2,389,857 cs (BUSY_TICKS), indicating a CPU that is about 4 percent busy. From this he concludes that the CPU is not a bottleneck on this host. Note that if the host system had more than one CPU, the columns whose headings had the prefix AVG_, such as AVG_IDLE_TICKS, would show the average of these metrics over all the CPUs.

Active Session History: So far the users have consulted John exactly when each problem occurred, enabling him to peek into the performance views in real time. This good fortune doesn’t last long?Janice comes to John complaining about a recent performance problem. When John queries the V$SESSION view, the session is idle, with no events being waited for. How can John check which events Janice’s session was waiting for when the problem occurred?

Oracle 10g collects the information on active sessions in a memory buffer every second. This buffer, called Active Session History (ASH), which can be viewed in the V$ACTIVE_SESSION_HISTORY dynamic performance view, holds data for about 30 minutes before being overwritten with new data in a circular fashion. John gets the SID and SERIAL# of Janice’s session and issues this query against the V$ACTIVE_SESSION_HISTORY view to find out the wait events for which this session waited in the past.

select sample_time, event, wait_time
from v$active_session_history
where session_id = 271
and session_serial# = 5;

The output, excerpted in Listing 7, shows several important pieces of information. First it shows SAMPLE_TIME?the time stamp showing when the statistics were collected?which lets John tie the occurrence of the performance problems to the wait events. Using the data in the V$ACTIVE_SESSION_HISTORY view, John sees that at around 3:17 p.m., the session waited several times for the log buffer space event, indicating that there was some problem with redo log buffers. To further aid the diagnosis, John identifies the exact SQL statement executed by the session at that time, using the following query of the V$SQL view:

select sql_text, application_wait_time
from v$sql
where sql_id in (
select sql_id
from v$active_session_history
where sample_time =
’22-FEB-04 03.17.31.188 PM’
and session_id = 271
and session_serial# = 5
);

The output is shown in Listing 8.
The column APPLICATION_WAIT_TIME shows how long the sessions executing that SQL waited for the application wait class. In addition to the SQL_ID, the V$ACTIVE_SESSION_HISTORY view also lets John see specific rows being waited for (in case of lock contentions), client identifiers, and much more.

What if a user comes to John a little late, after the data is overwritten in this view? When purged from this dynamic performance view, the data is flushed to the Active Workload Repository (AWR), a disk-based repository. The purged ASH data can be seen in the DBA_HIST_ACTIVE_SESSION_HIST view, enabling John to see the wait events of a past session. The data in the AWR is purged by default after seven days.

Conclusion : Oracle Database 10g introduces a number of enhancements designed to automate and simplify the performance diagnostic process. Wait event information is more elaborate in Oracle Database 10g and provides deeper insight into the cause of problems, making the diagnosis of performance problems a breeze in most cases, especially in proactive performance tuning.

One of my 10.2.0.1 database was hanging despite allocating ample opf resources. The CPU, Memory,Disk accesses were normal but under heavy load server would freeze for no valid reason. I had to raise TAR with Oracle.

My Statspack report had following details

—–Enqueue activity DB/Inst:
-> only enqueues with waits are shown
-> Enqueue stats gathered prior to 10g should not be compared with 10g data
-> ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)
——————————————————————————
Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
———— ———— ———– ———– ———— ————–
TX-Transaction (row lock contention)
6,611 6,615 0 6,615 32,521 4,916.24

Above list indicates oracle was waiting about 4 secs to get lock . Enqueue is a SGA memory structure used by Oracle.

Oracle support suggested to locate hot sql and hot block like table/index/cluster to reoslve this issue.

There are some articles on google but this PDF “Resolving_Oracle_Latch_Contention.pdf” from Guy Harrison at Quest is interesting …

Avoiding Hot Blocks
Cache buffers chains latch contention is one of the most intractable types of latch contention. There are a couple of things you can do at the application level to reduce the severity of this type of contention.
Firstly, identify the blocks that are hot.? Metalink note 163424.1, “How to Identify a Hot Block Within The Database”? describes how to do this. Having identified the identity of the hot block, you will most likely find that it is an index root or branch block.

It was suggested by Oracle support to look at Locks and provide them historical information about it. I couldn’t see any blokcing locks i TOAD/ deadlocks at alrtlog so I was surelocks are not the isse but I had to do what Oracle suypport suggested. So here is what I did.

Unix Shell script Process Stack diagnosis.
#!/usr/bin/ksh
# Create Oracle Trace
# Script 3 : Get Dump of Pmon, Smon & lgwr processes
# Pmon,Smon & Lgwr script files created in same directory
# Please use ./option3.sh <SID> for results
#################################################################################
export PID_PMON=$(ps -ef | grep ora_pmon_$1 | sed -e ‘/pts/d’ | awk ‘{print $2}’)
export PID_SMON=$(ps -ef | grep ora_smon_$1 | sed -e ‘/pts/d’ | awk ‘{print $2}’)
export PID_LGWR=$(ps -ef | grep ora_lgwr_$1 | sed -e ‘/pts/d’ | awk ‘{print $2}’)
echo $PID_PMON
echo $PID_SMON
echo $PID_LGWR
/usr/bin/pstack $PID_PMON > $1_PMON.log
/usr/bin/pstack $PID_SMON > $1_SMON.log
/usr/bin/pstack $PID_LGWR > $1_LGWR.log

# SQL script to carry system state dump
# Please execute this file by connecting to background sqlplus sessoion on live
# Once done please upload trace files created at $ORACLE_HOME/UDUMP to Oracle support
set term off;
set scan off;
set define off;
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
exec sys.dbms_lock.sleep(60);
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
exec sys.dbms_lock.sleep(60);
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
exec sys.dbms_lock.sleep(60);
alter session set max_dump_file_size=unlimited;
alter session set events ‘immediate trace name systemstate level 266’;
set term on;
set scan on;
set define on;

I have also created a small pl/sql procedure to locate blocking locks on system. This procedure will create a txt spool file with relevant details. Monitor these spool files over period of time to see which is a hot object.

CREATE OR REPLACE procedure locate_blocking_sid as
t_sid number;
t_sql_text varchar2(200);
msg varchar2(2000);
maxlinesize NUMBER := 32767;
log_file_handle UTL_FILE.file_type;
cursor c1 is
select sid, blocking_session,event, seconds_in_wait,wait_time
from sys.v_$session where state in (‘WAITING’)
and wait_class != ‘Idle’;
c1_rec c1%ROWTYPE;

cursor C2 (t_sid VARCHAR2, t_blocking_session varchar2) is
select ‘ ‘|| sql_text || sid
from sys.v_$session s, v$sql q
where sid in (t_sid,t_blocking_session)
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id);

BEGIN
log_file_handle := UTL_FILE.fopen(‘/oracle/app/oracle/admin/startp/udump’,’blocking_locks ‘||to_char(sysdate,’DD-MM-YY HH24:MI’)||’.txt’,’w’,maxlinesize);
— log_file_handle := UTL_FILE.fopen(‘/oracle/app/oracle/admin/catp/udump’,’blocking_locks’,’w’);
UTL_FILE.PUT_LINE(log_file_handle, ‘ SQL Running & V$ Session ID ;’|| ‘ Event for Wait ;’||’ Seconds in Wait ;’ || ‘ Wait Time’);
open c1;
loop
FETCH c1 INTO c1_rec;
EXIT WHEN c1%NOTFOUND;

BEGIN
open c2(c1_rec.sid,c1_rec.blocking_session);
Loop
FETCH c2 into msg;
msg := msg || ‘ ; ‘|| trim(c1_rec.event)|| ‘ ;’|| c1_rec.seconds_in_wait||’ ; ‘||c1_rec.wait_time;
EXIT WHEN c2%NOTFOUND;
UTL_FILE.PUT_LINE(log_file_handle,msg );
End Loop;
close c2;
END;
end loop;
close c1;
UTL_FILE.FCLOSE(log_file_handle);
end;
/

Example 1 : Running Execute immediate with an OUT parameter.

I wanted to output a count of tables to see if there is a data difference between Live/Test. I would have created a SQL script but it is hard to share with other developers so I found creating a procedure handy.

Create or replace procedure update_row_count as
l_count number;
l_sql_error_message varchar2(100) :=NULL;
SQL_STMT varchar2(200) :=NULL;
TYPE table_name_type IS VARRAY(7) OF VARCHAR2(30);
table_names table_name_type
:= table_name_type
(‘EMP’,
‘DEPT’,
‘SALARY’);
BEGIN
FOR i IN Table_Names.FIRST..Table_Names.LAST
LOOP
SQL_STMT := ‘SELECT COUNT(*) FROM SCOTT.’||table_names(i);
EXECUTE IMMEDIATE sql_stmt INTO l_count;
l_sql_error_message := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE(‘TABLE : ‘||upper(table_names(i)) || ‘ ‘|| l_count);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Others Error Message : ‘||l_sql_error_message);
END;
set serverout on size 10000;
/
exec update_row_count;

For an IN and OUT parameters you would do

EXECUTE IMMEDIATE ‘BEGIN SCOTT.’|| Table_Names(i)||'(:1,:2,:3);END;’

USING parameter1,OUT parameter2,OUT parameter3;

Example 2 : Running Execute immediate with number of IN parameters. In an example below I am sending IN parameters Emp_id , Dept_id, Dept_name,location and retrieving Salary & Emp_rec

Create or replace procedure update_row_count as
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ‘PERSONNEL’;
location VARCHAR2(13) := ‘DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := ‘SELECT * FROM emp WHERE empno = :id’;
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := ‘BEGIN emp_pkg.raise_salary(:id, :amt); END;’;
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := ‘UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2’;
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE ‘DELETE FROM dept WHERE deptno = :num’
USING dept_id;
EXECUTE IMMEDIATE ‘ALTER SESSION SET SQL_TRACE TRUE’;
END;