Can’t compile a stored procedure when it’s locked

Trying to recompile a procedure causes the application to hang
(ie: SQL*Plus hangs after submitting the statement). Eventually ORA-4021 errors
occur after the timeout (usually 5 minutes). Here is the soluation from metalink:
Note:ID 107756.1

Error: ORA 4021
Text: time-out occurred while waiting to lock object
—————————————————————————–
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

Solution Description
——————–

Verify that the package is not locked by another user by selecting from
V$ACCESS view. To do this, run:

SELECT * FROM v$access WHERE object = ”;

Where is the package name (usually in all uppercase). If there is a row
returned, then the package is already locked and cannot be dropped until the
lock is released. Returned from the query above will be the SID that has this
locked. You can then use this to find out which session has obtained the lock.

In some cases, that session might have been killed and will not show up. If
this happens, the lock will not be release immediately. Waiting for PMON to
clean up the lock might take some time. The fastest way to clean up the lock
is to recycle the database instance.

If an ORA-4021 error is not returned and the command continues to hang after
issuing the CREATE OR REPLACE or DROP statment, you will need to do further
analysis see where the hang is occuring. A starting point is to have a
look in v$session_wait, see the referenced NOTE.61552.1 for how to analyze hang
situations in general

Solution Explanation
——————–

Consider the following example:

Session 1:

create or replace procedure lockit(secs in number) as
shuttime date;
begin
shuttime := sysdate + secs/(24*60*60);
while sysdate <= shuttime loop
null;
end loop;
end;
/
show err

begin
– wait 10 minutes
lockit(600);
end;
/

Session 2:
create or replace procedure lockit as
begin
null;
end;
/

Result: hang and eventually (the timeout is 5 minutes):

create or replace procedure lockit as
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object LOCKIT

Session 3:

connect / as sysdba
col owner for a10
col object for a15
select * from v$access where object = 'LOCKIT';

Result:
SID OWNER OBJECT TYPE
———- ———- ————— ————————
9 OPS$HNAPEL LOCKIT PROCEDURE

select sid, event from v$session_wait;

Result:

SID EVENT
———- —————————————————————-
9 null event
…
12 library cache pin

In the above result, the blocking sid 9 waits for nothing while session 12, the
hanging session, is waiting for event library cache pin.

two ways to call Oracle Stored procedure from VB.net

PROCEDURE TEST_C(temp out varchar2,a IN varchar2, b in varchar2)
IS
BEGIN
temp:=a || b;
END;

Solution 1:
add “Imports System.Data.OleDb” at the code beginning

Dim dbConn As New OleDbConnection
Dim dbComm As OleDbCommand

dbConn.ConnectionString = “Provider=MSDAORA;User ID=xxx;Password=xxx;Data Source=xxx;”
dbConn.Open()
dbComm = dbConn.CreateCommand

dbComm.Parameters.Add(“temp”, OleDbType.VarChar, 30).Direction = ParameterDirection.Output
dbComm.Parameters.Add(“a”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“a”).Value = “test “
dbComm.Parameters.Add(“b”, OleDbType.VarChar, 30).Direction = ParameterDirection.Input
dbComm.Parameters(“b”).Value = “OK”

dbComm.CommandText = “TEST_C”
dbComm.CommandType = CommandType.StoredProcedure
dbComm.ExecuteNonQuery()
dbConn.Close()

MessageBox.Show(dbComm.Parameters(“temp”).Value)

Solution 2:
add “Imports System.Data.OracleClient” at the code beginning

Dim oraConn As New OracleConnection
Dim oraComm As New OracleCommand

oraConn.ConnectionString = “Data Source=xxx;User Id=xxx;Password=xxx”
oraComm.Connection = oraConn

oraComm.Parameters.Add(“temp”, OracleType.VarChar, 10).Direction = ParameterDirection.Output
oraComm.Parameters.Add(“a”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“a”).Value = “test “
oraComm.Parameters.Add(“b”, OracleType.VarChar, 10).Direction = ParameterDirection.Input
oraComm.Parameters(“b”).Value = “OK”

oraConn.Open()
oraComm.CommandText = “TEST_C”
oraComm.CommandType = CommandType.StoredProcedure
oraComm.ExecuteNonQuery()
oraConn.Close()

MessageBox.Show(oraComm.Parameters(“temp”).Value)

Note: the first parameter name has to be the same as the oracle stored procedure parameter name;
if there is dblink in the stored procedure, then the solution 1 is the only choice.

if you are trying to call oracle stored procedure by passing clob/lob as parameters, then don’t use odbc solution because it has 32k limitation. Oracle have this metalink note talk about this:
From metalink: Subject-32k Limitation When Passing LOB Parameter Through Stored Procedure Doc ID: 252102.1

https://metalink2.oracle.com/metalink/plsql/f?p=130:14:6916025231277951933::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,126125.1,1,0,1,helvetica

Workaround
~~~~~~~~~~

Workaround 1:

Use the Oracle Provider for OLEDB instead, making sure to set the command
property SPPrmsLOB to TRUE:

objCmd.Properties(“SPPrmsLOB”) = TRUE

Workaround 2:

Instead of passing a CLOB as a parameter to a stored procedure, use a method
that directly interfaces with the database and does not require the use of
a stored procedure to update the CLOBs as in the following example:

Note 126125.1 – ADO Streaming BLOB & CLOB Example Using ODBC and OLEDB in VB (SCR 1388).

Oracle XML practise

A very good Oracle XML practise from this link: http://www.itpub.net/viewthread.php?tid=899445&highlight=xml

quoted here for future reference.

1、判断一下数据库中xml功能是否已经安装成功

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on 星期一 11月 26 10:35:23 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

idle>conn sys/sys@doscdb as sysdba
已连接。

DOSCDB(sys)>select * from v$version;

BANNER
———————————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

DOSCDB(sys)>select username from dba_users;

USERNAME
———————————————————–
DIP
TSMSYS
DBSNMP
SYSMAN
MDSYS
ORDSYS
CTXSYS
ANONYMOUS
EXFSYS
DMSYS
WMSYS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
MGMT_VIEW
SYS
SYSTEM
OUTLN
UOA
UBASE
UWF
UOCEAN
UBI
UTEMP

已选择24行。

–存在XDB用户

DOSCDB(sys)>DESCRIBE RESOURCE_VIEW;
名称                                                  是否为空? 类型
—————————————————– ——– ————————————
RES                                                            XMLTYPE(XMLSchema “http://xmlns.orac
le.com/xdb/XDBResource.xsd” Element
“Resource”)
ANY_PATH                                                       VARCHAR2(4000)
RESID                                                          RAW(16)

–从RESOURCE_VIEW 信息中可以判断XDB功能已经安装完成

2、准备xml_test.xml文件，并放在e:\testxml目录下

–xml_test.xml文件内容

<xwork>
<include file=”webwork-default.xml”/>
<package name=”default” extends=”webwork-default”>
<Refpeng>PENG XML_TEST </Refpeng>
<result-types>
<result-type1 name=”excel” class=”com.hisoft.web.view.ExcelResult”/>
<result-type2 name=”excelReport” class=”com.hisoft.web.view.ExcelReportResult”/>
</result-types>

<interceptors>
<interceptor name=”auth” class=”com.hisoft.web.interceptor.RoleInterceptor” />
<interceptor name=”XData” class=”com.hisoft.web.interceptor.XDataInterceptor” />
<interceptor-stack name=”roleStack”>
<interceptor-ref name=”defaultStack”/>
<interceptor-ref name=”auth”/>
</interceptor-stack>
</interceptors>

<!– global results –>
<global-results>
<result name=”error” type=”freemarker”>/WEB-INF/pages/globalError.ftl</result>
<result name=”redirect” type=”freemarker”>/WEB-INF/pages/redirect.ftl</result>
<result name=”login” type=”freemarker”>/WEB-INF/pages/login.ftl</result>
</global-results>

<action name=”login” class=”com.hisoft.web.action.LoginAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/globalredirect.ftl</result>
<result name=”input” type=”freemarker”>/WEB-INF/pages/login.ftl</result>
<interceptor-ref name=”validationWorkflowStack” />
</action>

<action name=”index” class=”com.opensymphony.xwork.ActionSupport”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/index.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”left” class=”com.hisoft.web.action.LeftMenuAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/leftmenu.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”top” class=”com.hisoft.web.action.TopAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/top.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”main”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/welcome.ftl</result>
<interceptor-ref name=”roleStack” />
</action>

<action name=”exportExcel” class=”com.hisoft.web.action.ExportExcelAction”>
<result name=”success” type=”excel”>
<param name=”filename”>product.</param>
</result>
</action>

<action name=”exportExcelReport” class=”com.hisoft.web.action.ExportExcelListAction”>
<result name=”success” type=”excelReport”>
<param name=”filename”>product</param>
<param name=”template”>/WEB-INF/pages/product.xls</param>
</result>
</action>
</package>

</xwork>

3、创建xml表，用来存储xml 数据

DOSCDB(sys)>create table xml_test of xmltype;

表已创建。

DOSCDB(sys)>desc xml_test;
名称                                                  是否为空? 类型
—————————————————– ——– ————————————
TABLE of XMLTYPE

DOSCDB(sys)>CREATE DIRECTORY xmldir AS ‘e:\testxml\’;

目录已创建。

DOSCDB(sys)>INSERT INTO xml_test VALUES (XMLType(bfilename(‘XMLDIR’, ‘testxml.xml’),
2   nls_charset_id(‘AL32UTF8′)));

已创建 1 行。

DOSCDB(sys)>commit;

提交完成。

4、如何使用呢，既然存储进去了，当然是为了更好的使用了

DOSCDB(sys)>desc user_xml_tables;
名称                                                  是否为空? 类型
—————————————————– ——– ————————————
TABLE_NAME                                                     VARCHAR2(30)
XMLSCHEMA                                                      VARCHAR2(700)
SCHEMA_OWNER                                                   VARCHAR2(30)
ELEMENT_NAME                                                   VARCHAR2(2000)
STORAGE_TYPE                                                   VARCHAR2(17)

DOSCDB(sys)>select table_name from user_xml_tables;

TABLE_NAME
————————————————————
XML_TEST

–取得所有文档的内容

DOSCDB(sys)>SELECT OBJECT_VALUE from xml_test;

OBJECT_VALUE
—————————————————————————————————-
<xwork>
<include file=”webwork-default.xml”/>
<package name=”default” extends=”webwork-default”>
<Refpeng>PENG XML_TEST </Refpeng>
<result-types>
<result-type1 name=”excel” class=”com.hisoft.web.view.ExcelResult”/>
<result-type2 name=”excelReport” class=”com.hisoft.web.view.ExcelReportResult”/>
</result-types>
<interceptors>
<interceptor name=”auth” class=”com.hisoft.web.interceptor.RoleInterceptor”/>
<interceptor name=”XData” class=”com.hisoft.web.interceptor.XDataInterceptor”/>
<interceptor-stack name=”roleStack”>
<interceptor-ref name=”defaultStack”/>

<action name=”left” class=”com.hisoft.web.action.LeftMenuAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/leftmenu.ftl</result>
<interceptor-ref name=”roleStack”/>
</action>
<action name=”top” class=”com.hisoft.web.action.TopAction”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/top.ftl</result>
<interceptor-ref name=”roleStack”/>
</action>
<action name=”main”>
<result name=”success” type=”freemarker”>/WEB-INF/pages/welcome.ftl</result>
<interceptor-ref name=”roleStack”/>
</action>
<action name=”exportExcel” class=”com.hisoft.web.action.ExportExcelAction”>
<result name=”success” type=”excel”>
<param name=”filename”>product.</param>
</result>
</action>
<action name=”exportExcelReport” class=”com.hisoft.web.action.ExportExcelListAction”>
<result name=”success” type=”excelReport”>
<param name=”filename”>product</param>
<param name=”template”>/WEB-INF/pages/product.xls</param>
</result>
</action>
</package>
</xwork>

–取得<xwork>内的内容

DOSCDB(sys)>SET LONG 10000
DOSCDB(sys)>SET PAGESIZE 100
DOSCDB(sys)>SELECT extract(OBJECT_VALUE, ‘/xwork’)
2    FROM xml_test;

DOSCDB(sys)>SELECT extract(OBJECT_VALUE, ‘/xwork/package’)
2  from xml_test;

EXTRACT(OBJECT_VALUE,’/XWORK/PACKAGE’)
—————————————————————————————————-
<package name=”default” extends=”webwork-default”><Refpeng>PENG XML_TEST </Refpeng><result-types><re
sult-type1 name=”excel” class=”com.hisoft.web.view.ExcelResult”/><result-type2 name=”excelReport” cl
ass=”com.hisoft.web.view.ExcelReportResult”/></result-types><interceptors><interceptor name=”auth” c
lass=”com.hisoft.web.interceptor.RoleInterceptor”/><interceptor name=”XData” class=”com.hisoft.web.i
/pages/login.ftl</result><interceptor-ref name=”valida
tionWorkflowStack”/></action><action name=”index” class=”com.opensymphony.xwork.ActionSupport”><resu
lt name=”success” type=”freemarker”>/WEB-INF/pages/index.ftl</result><interceptor-ref name=”roleStac
k”/></action><action name=”left” class=”com.hisoft.web.action.LeftMenuAction”><result name=”success”
type=”freemarker”>/WEB-INF/pages/leftmenu.ftl</result><interceptor-ref name=”roleStack”/></action><
action name=”top” class=”com.hisoft.web.action.TopAction”><result name=”success” type=”freemarker”>/
WEB-INF/pages/top.ftl</result><interceptor-ref name=”roleStack”/></action><action name=”main”><resul
t name=”success” type=”freemarker”>/WEB-INF/pages/welcome.ftl</result><interceptor-ref name=”roleSta
ck”/></action><action name=”exportExcel” class=”com.hisoft.web.action.ExportExcelAction”><result nam
e=”success” type=”excel”><param name=”filename”>product.</param></result></action><action name=”expo
rtExcelReport” class=”com.hisoft.web.action.ExportExcelListAction”><result name=”success” type=”exce
lReport”><param name=”filename”>product</param><param name=”template”>/WEB-INF/pages/product.xls</pa
ram></result></action></package>

5、牛刀小试
–简单访问

DOSCDB(sys)>SELECT extract(OBJECT_VALUE, ‘/xwork/package/Refpeng’)
2  from xml_test;

EXTRACT(OBJECT_VALUE,’/XWORK/PACKAGE/REFPENG’)
—————————————————————————————————-
<Refpeng>PENG XML_TEST </Refpeng>

–访问一下属性值看看

DOSCDB(sys)>SELECT extractvalue(OBJECT_VALUE, ‘/xwork/package/Refpeng’)
2  from xml_test;

EXTRACTVALUE(OBJECT_VALUE,’/XWORK/PACKAGE/REFPENG’)
—————————————————————————————————-
PENG XML_TEST

–小试 where

DOSCDB(sys)>SELECT extractValue(OBJECT_VALUE, ‘/xwork/package/Refpeng’) “Peng Test”
2  FROM xml_test
3  WHERE existsNode(OBJECT_VALUE,
4                     ’/xwork/package[Refpeng="PENG XML_TEST "]‘) = 1;

Peng Test
———————————————————————————————
PENG XML_TEST

–小试update

DOSCDB(sys)>UPDATE xml_test
2    SET OBJECT_VALUE =
3          updateXML(OBJECT_VALUE,
4                    ’/xwork/package/Refpeng/text()’,
5                    ’PENG XML_UPDATE_TEST’
6                    )
7    WHERE existsNode(OBJECT_VALUE,
8                       ’/xwork/package[Refpeng="PENG XML_TEST "]‘) = 1;

已更新 1 行。

DOSCDB(sys)>COMMIT;

提交完成。

DOSCDB(sys)>SELECT extractValue(OBJECT_VALUE, ‘/xwork/package/Refpeng’) “Peng Test”
2  FROM xml_test
3  /

Peng Test
—————————————————————————————————-
PENG XML_UPDATE_TEST