Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link (Oracle 11.2.0.1, x64, Server 2008)
| ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link [message #569022] |
Thu, 18 October 2012 14:07  |
 |
mbrown22
Messages: 3
Registered: October 2012
|
Junior Member |
|
|
Hi,
This is my first post on this forum. I read the posting guidelines, but pelase excuse any issues beforehand 
The Oracle DB in question is 11.2.0.1, x64, Server 2008.
I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds.
This SQL Server server is also Win 2008, x64.
Now, on the server that runs SQL Server, I have a simple view which is defined as such :
select DOCID,
DOCINDEX1,
DOCINDEX2,
DOCINDEX3,
DOCINDEX4,
DOCINDEX5,
DOCINDEX6,
DOCINDEX7,
DOCINDEX8,
convert( int, convert(varchar(30), docindex9, 0)) DOCINDEX9,
DOCINDEX10,
convert( int, convert(varchar(30), docindex11, 0)) DOCINDEX11,
DOCINDEX12,
DOCINDEX13,
DOCINDEX14,
DATEADD( month, -1*DOCINDEX9,DOCINDEX7) ALERT_PERIOD_START,
DOCINDEX7 ALERT_PERIOD_END
from pvdm_docs_1_1
This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.
It is defined as such :
CREATE PUBLIC DATABASE LINK PVE_SQLSERVER
CONNECT TO EVP_PVE_USER
IDENTIFIED BY <PWD>
USING 'PVE_SQLSERVER';
Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.
I know it works because I get results with a sql command like :
select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;
I also created a view on the Oracle server that refines this information. It is defined as such :
CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT
(
DOCID,
EFFECTIVE_DATE,
EXPIRATION_TYPE,
ALERT_PERIOD_START,
ALERT_PERIOD_END,
ACRONYM,
CONTRACTING_PARTY,
CONTRACT_TYPE,
CONTRACT_OWNER,
CC1,
CC2
)
AS
SELECT DOCID,
DOCINDEX1 AS EFFECTIVE_DATE,
DECODE (DOCINDEX8,
'Fixed Period (Terrminates)', 'Termination',
'Auto Renewal')
AS EXPIRATION_TYPE,
ALERT_PERIOD_START,
ALERT_PERIOD_END,
DOCINDEX10 AS ACRONYM,
DOCINDEX3 AS CONTRACTING_PARTY,
DOCINDEX4 AS CONTRACT_TYPE,
DOCINDEX6 CONTRACT_OWNER,
DOCINDEX13 CC1,
DOCINDEX14 CC2
FROM vw_pve_docs_1_1@pve_sqlserver
WHERE DOCINDEX5 = 'Active'
AND SYSDATE >= ALERT_PERIOD_START
AND SYSDATE <= ALERT_PERIOD_END
ORDER BY ALERT_PERIOD_END;
This view also works fine, i.e., I can select * from it from the sql command line.
Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.
I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :
CREATE OR REPLACE PROCEDURE EVPDBA.TESTME
is
tnum number := 0;
begin
select count(*) into tnum from VW_PVE_CONTRACTS_INALERT;
end;
/
If I execute this procedure from the sql command line, all is well.
When I run it from a scheduler job, I get
ORA-01010: invalid OCI operation
ORA-02063: preceding line from PVE_SQLSERVER
ORA-06512: at "EVPDBA.TESTME", line 5
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.
I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.
The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.
One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).
Any help or directions of research would be greatly appreciated. I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.
Thanks in advance,
Matt
|
|
|
|
| Re: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link [message #569023 is a reply to message #569022] |
Thu, 18 October 2012 14:19  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Below likely won't help, but seeing it can't hurt anything or any one
01010, 00000, "invalid OCI operation"
// *Cause: One of the following:
// 1) You attempted an invalid OCI operation.
// 2) You are using an Oracle client application linked with
// version 7.1 (or higher) libraries, the environment variable
// ORA_ENCRYPT_LOGIN is set to TRUE, and you attempted to
// connect to a version 7.0 (or lower) Oracle Server.
// 3) You are connected to a version 7.1 (or higher) Oracle Server,
// the initialization parameter DBLINK_ENCRYPT_LOGIN is set to
// TRUE, and you attempted to use a database link pointing to a
// version 7.0 (or lower) Oracle Server.
// 4) You are connected to a version 9.0.2(or higher) Oracle Server
// and you attempted to use a database link pointing to a version
// 9.0.1(or lower) Oracle Server for distributed autonomous
// transaction.
// *Action: For the above causes:
// 1) Do not use the invalid OCI operation.
// 2) If you do not wish to use encrypted connect passwords in your
// distributed database, set ORA_ENCRYPT_LOGIN to FALSE.
// If you wish to use encrypted connect passwords, you must upgrade
// all Oracle Servers to version 7.1 (or higher).
// 3) If you do not wish to use encrypted database links in your
// distributed database, set DBLINK_ENCRYPT_LOGIN to FALSE.
// If you wish to use encrypted database links, you must upgrade
// all Oracle Servers to version 7.1 (or higher).
// 4) Do not attempt distributed autonomous transaction on version
// 9.0.1(or lower) Oracle Server.
[oracle@localhost ~]$
Below is another truism, that may or may not apply
privileges acquired via ROLE do not apply within named PL/SQL procedure.
direct GRANT is required.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:24:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
