tracker issue : CF-3494006

select a category, or use search below
(searches all categories and all time range)
Title:

Calling Oracle Package: [Macromedia][Oracle JDBC Driver]User defined type not found: SYS.DBMS_UTILITY

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/CannotReproduce

Reporter/Name(from Bugbase): Jim Yang / Jim Yang (Another James Yang)

Created: 01/31/2013

Components: Database

Versions: 10.0

Failure Type: Crash

Found In Build/Fixed In Build: Final /

Priority/Frequency: Critical / All users will encounter

Locale/System: English / Win 2008 Server R2

Vote Count: 0

Problem Description:
Error occurs when calling Oracle Package from CF10. Error does not occur when calling Oracle Package from CF9.  CF script and Oracle Packages are identical on all systems.

Steps to Reproduce:
Method 1
[Macromedia][Oracle JDBC Driver]User defined type not found: SYS.DBMS_UTILITY  
The error occurred in PAGE.cfm: line 4
2 : 	<cfstoredproc procedure="DBMS_MVIEW.REFRESH" datasource="DATA_SOURCE">
3 : 		<cfprocparam cfsqltype="cf_sql_varchar" value="USER.USER_MV">
4 : 		<cfprocparam cfsqltype="cf_sql_varchar" value="c">
5 : 	</cfstoredproc>
6 : </cflock>

1) Browser Back -> Submit (again) -> Works
2) Data Sources :: Advanced Settings :: Maintain Connections -> UNCHECKED
3) Browser Back -> Submit (again) -> Error (consistent)
4) Data Sources :: Advanced Settings :: Maintain Connections -> CHECKED
5) Browser Back -> Submit (again) -> Works

Method 2
[Macromedia][Oracle JDBC Driver]User defined type not found: SYS.DBMS_UTILITY  
The error occurred in PAGE.cfm: line 11
9 : 
10 : <cflock name="DB_Lock" type="Exclusive" timeout="30">
11 : 	<cfquery name="CallProc" datasource="DATA_SOURCE">
12 : 		CALL DBMS_MVIEW.REFRESH('USER.USER_MV','c'); 	
13 : 	</cfquery>

1) Browser Back -> Submit (again) -> Error (consistent)

Actual Result:
Error: [Macromedia][Oracle JDBC Driver]User defined type not found: SYS.DBMS_UTILITY  

Expected Result:
No error

Any Workarounds:
When using cfstoredproc, refreshing page will not throw error.

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3494006

Deployment Phase:	Release Candidate

External Customer Info:
External Company:  
External Customer Name: Another James Yang
External Customer Email:  
External Test Config: My Hardware and Environment details:



Working Config:

-- Win 2003 Server Enterprise SP1

-- ColdFusion 9,0,1,274733

-- Oracle 10.2 (legacy) and Oracle 11.2 (production)



Non-working Config:

-- Win 2008 Server R2

-- ColdFusion 10,282462

-- Oracle 10.2 (legacy) and Oracle 11.2 (production)

Attachments:

Comments:

We have tried several different database settings/permissions with no success. The problem seems to occur when CF10 is making its initial connection with Oracle. The problem does not occur with CF9 regardless of any setting changes.
Comment by External U.
16419 | January 31, 2013 12:04:11 PM GMT
The error does not occur when called from SQL*Plus.
Comment by External U.
16420 | January 31, 2013 12:05:56 PM GMT
Can you attach a repro case with this issue? (Comment added from ex-user id:gtiwari)
Comment by Adobe D.
16421 | February 13, 2013 06:23:18 AM GMT
Workaround: Created another Oracle Package which calls USER.USER_MV. Since the second Oracle Package does not try to reference another schema/user, it works.
Comment by External U.
16422 | February 14, 2013 03:35:22 PM GMT
Similar/related issue: We discovered that USER1 querying USER2's schema yields a similar error (table or view not found). Same as the Oracle Packages, when the page is refreshed, the query executes as expected. USER1 has the Oracle Role to access USER2's schema. However, (we believe) since it is not referencing its own object, it kicks an error. I will try to provide a simple case shortly.
Comment by External U.
16423 | February 14, 2013 03:40:34 PM GMT
@James: Can you please provide a repro case soon?
Comment by Nimit S.
16424 | December 17, 2013 03:25:03 AM GMT
You can close this. We are just sticking with our workaround.
Comment by External U.
16425 | December 17, 2013 09:22:53 AM GMT