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 Go to next message
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 Smile
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 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
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.
Re: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link [message #569024 is a reply to message #569023] Thu, 18 October 2012 14:28 Go to previous messageGo to next message
mbrown22
Messages: 3
Registered: October 2012
Junior Member
Thanks for the reply. yeah, I saw the OCI error Oracle recommendations. I intially, and perhaps foolishly, thought that because the procedure runs fine from the SQL command line, that this would not apply to my issue. Perhaps this needs more testing. Along those lines, can someone inform me how to change this DBLINK_ENCRYPT_LOGIN parameter? There is nothing close to that name in v$parameter, and "show parameter DBLINK_ENCRYPT_LOGIN" returns nothing.
Thanks
Matt
Re: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link [message #569026 is a reply to message #569024] Thu, 18 October 2012 14:31 Go to previous messageGo to next message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:987230523008
Re: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link [message #569083 is a reply to message #569022] Fri, 19 October 2012 09:10 Go to previous messageGo to next message
mbrown22
Messages: 3
Registered: October 2012
Junior Member
Thanks for the reference. AskTom implies this parameter is deprecated. Any other ideas?
Re: ORA-01010 Invalid OCI Operation during SCHEDULER job procedure that selects from db link [message #569084 is a reply to message #569083] Fri, 19 October 2012 09:12 Go to previous message
BlackSwan
Messages: 23029
Registered: January 2009
Senior Member
>Any other ideas?

submit Service Request
Previous Topic: Get Comma separate Values
Next Topic: Trying to get employee hire annyversary
Goto Forum:
  


Current Time: Sun Nov 23 03:38:43 CST 2014

Total time taken to generate the page: 0.14790 seconds