Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Issue in Connecting to Oracle Database Using Python with cx_oracle

krishnabharatApr 27 2020 — edited May 12 2020

I am using cx_oracle to connect the Oracle DB using python.I need your assistance in connecting DB. I'm using following code till date it worked well

Code-1:

import cx_Oracle as cx dsn_tns = cx.makedsn(HOST,PORT, service_name=SERVICE_NAME)

conn = cx.connect(user=USER, password=PW, dsn=dsn_tns)

Recently TCP port is disabled and we are using jdbc connection string to connect DB which has TCPS port number.can you help where I need to pass the protocol in above/ suggest any other method.

i tried following code

code-2:

dsn_tns='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST =hostdetails)(PORT =port))) (CONNECT_DATA = (SERVICE_NAME = servicename)))' conn = cx.connect(user=USER, password=PW, dsn=dsn_tns) print(cx.version)

I got error: ORA-28759: failure to open file.

Any suggestion is appreciated. Thank you in advance.

Comments

John Thorton 
0f4d766e-18a2-4235-9bf7-bd506202d4e9 wrote:
I am using cx_oracle to connect the Oracle DB using python.I need your assistance in connecting DB. I'm using following code till date it worked well

What changed? What is different?

Every connection request that gets to Oracle listener is logged to listener.log file.

Post excerpt from listener.log file showing this connection request.

krishnabharat

I'm able to connect the db using tcp port in past but it's disabled now. I think by default the tcp port is enabled for cx_oracle.

Is it possible to connect the DB with TCPS port number using python? I don't find the parameter to pass the protocol in cx_oracle . can you suggest some way / library to connect.

krishnabharat

when i am using tcps port . I'm getting for following error for code-1

ORA-12537: TNS:connection closed

krishnabharat

Can you give me any idea about cx_Oracle.SUBSCR_PROTO_OCI

evgenyg

Not sure what it means: "Recently TCP port is disabled and we are using jdbc connection string to connect DB which has TCPS port number"

I guess to connect you need to have port that listener listening on to be open.

1 person found this helpful
krishnabharat

I'm able to connect the DB in SQl developer using this connection string

jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST =hostdetails)(PORT =port))) (CONNECT_DATA = (SERVICE_NAME = servicename)))

but when i'm giving the same connection string to cx_oracle library. I'm getting the following error

ORA-12154: TNS:could not resolve the connect identifier specified.

Can you suggest the possible checks to be perform

Gaz in Oz

You have shown what works in java, grate.

Now show your actual connection string being used in python and cx_Oracle.connect function.

In theory it should look something like

connstr = 'user/password@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL=tcps)(HOST=hostdetails)(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=servicename)))';
cx_Oracle.connect(connstr);

or perhaps

cx_oracle.connect(username, password, (DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL=tcps)(HOST=hostdetails)(PORT=port)))(CONNECT_DATA=(SERVICE_NAME=servicename)))';

Also there is a dedicated forum for Python:

1 person found this helpful
krishnabharat

Thanks for your response GAZ.

I tried the above code and i am getting below error .

ORA-28759: failure to open file

Can you suggest something ?

Gaz in Oz

Did you perhaps copy/paste the example I gave and missed putting in the missing leading single quote?

...and username and password are variables holding the username and password you want to login as.

Or did you actually connect successfully and you are now asking a completely unrelated question with regard to

SQL> !oerr ora 28759
28759, 00000, "failure to open file"
// *Cause:  The system could not open the specified file.
// *Action: Enable tracing to determine the exact cause of this error.
//          Contact Oracle customer support if needed.

?

krishnabharat

Please find the code I used.

import cx_Oracle as cx

dsn_tns='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = ****)(PORT = ****))) (CONNECT_DATA = (SERVICE_NAME = ****)))'

conn = cx.connect(user=USER, password=PW, dsn=dsn_tns)

print(cx.version)

DatabaseError: ORA-28759: failure to open file

evgenyg

Sorry, I am misread your post.

You using TCPS protocol, I never used it, thanks learning something new.

From my reading seems something to do with wallet .

Please take a look on following discussion, probably you will find it useful

https://forums.toadworld.com/t/connecting-with-tcps-ora-28759-failure-to-open-file/47937/2

Regards

Evgeni

krishnabharat

Thank for link Evgeni.

I have add the sqlnet.ora and tnsnames.ora in the path ⁨instantclient_19_3⁩ \⁨network⁩\admin.

but I still getting the same error.

Can you help me on this ?

evgenyg

You may need to ensure it "picked up". I am not sure how but something to do with environment  variables. Are error (as per my understanding) is about failure of open wallet file.

Gaz in Oz

Set the TNS_ADMIN environment variable to point at the full directory path containing your sqlnet.ora file

1 person found this helpful
krishnabharat

any other suggestions . I have added the environment variables but still i'm facing the same issue. Am I missing any other files ?

krishnabharat

I can see there is some issue with the sqlnet.ora and tnsnames.ora  after updating the files I'm getting new error

ORA-12560: TNS:protocol adapter error

John Thorton 
0f4d766e-18a2-4235-9bf7-bd506202d4e9 wrote:
I can see there is some issue with the sqlnet.ora and tnsnames.ora after updating the files I'm getting new error
ORA-12560: TNS:protocol adapter error

If this is a Windows system, this is a very common error with any number of possible causes all of which are OS mis-configuration.

Is Oracle DB installed on Windows OS?

From DB Server system issue OS commands below

lsnrctl status

lsnrctl service

COPY the results from 2 commands above then PASTE full results back here so we can see the status

krishnabharat

Can you share the command for MAC?

platform.platform: Darwin-18.7.0-x86_64-i386-64bit

sys.maxsize > 2**32: True

platform.python_version: 3.7.4

cx_Oracle.version: 7.3.0

cx_Oracle.clientversion: (19, 3, 0, 0, 0)

DB: 12.2.0.1.0

John Thorton 
krishnabharat wrote:
Can you share the command for MAC?
platform.platform: Darwin-18.7.0-x86_64-i386-64bit
sys.maxsize > 2**32: True
platform.python_version: 3.7.4
cx_Oracle.version: 7.3.0
cx_Oracle.clientversion: (19, 3, 0, 0, 0)
DB: 12.2.0.1.0

From DB Server system issue OS commands below

lsnrctl status

lsnrctl service

COPY the results from 2 commands above then PASTE full results back here so we can see the status

What is OS name & version for client system?

What is OS name & version for DB Server system?

1 - 19

Post Details

Added on Apr 27 2020
#general-database-discussions
19 comments
3,417 views