SQL查询中的命名参数-cx_Oracle-ORA-01460:请求未实现或不合理的转换 [英] Named Parameters in SQL Queries - cx_Oracle - ORA-01460: unimplemented or unreasonable conversion requested

问题描述

按照Python DB-API在RAW SQL查询中实现命名参数后，我遇到了问题.

I have encountered a problem after implementing the named parameters in RAW SQL Queries as per Python DB-API.

以前，我的代码如下(并且在我的DEV服务器和客户端的测试服务器上都可以正常工作)

Earlier, my code was as follows (and this works fine, both on my DEV Server and my Client's test server)

cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR = '%s' " %(TAG_NBR))

我将其更改为以下

cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR = :TAG_NBR " ,{'TAG_NBR':TAG_NBR})

此更改的版本(带有命名参数)在我的开发服务器上可以正常运行

This changed version (with named parameters) works fine on my Development Server

• Windows XP Oracle XE
• SQL * Plus:版本11.2.0.2.0
• cx_Oracle-5.1.2-11g.win32-py2.7

• Windows XP Oracle XE
• SQL*Plus: Release 11.2.0.2.0
• cx_Oracle-5.1.2-11g.win32-py2.7

但是，当部署到我的客户的测试服务器上时，它并不会....所有查询的执行都会失败.

However, when deployed on my Client's Test Server, it does not.... execution of all queries fail.

我的客户端服务器的特征如下

Characteristics of my client's server are as follows

• Windows Server 2003
• Oracle数据库10g企业版10.2.0.1.0-64bi
• cx_Oracle-5.1.2-10g.win32-py2.7

我得到的错误如下

Traceback (most recent call last):
  File "C:/Program Files/App_Logic/..\apps\views.py", line 400, in regularize_TAG
    T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR = :TAG_NBR " ,{'TAG_NBR':TAG_NBR})
DatabaseError: ORA-01460: unimplemented or unreasonable conversion requested

感谢有人可以帮助我解决这个问题.

Appreciate if someone could help me through this issue.

仅当在Web应用程序(托管在Apache)中运行cx_Oracle代码时，才会出现此问题.

This issue presents itself only when the cx_Oracle code is run inside the Web App (Hosted on Apache).

如果我在python命令行中使用命名参数运行相同的代码，则查询运行得很好.

If i run the same code with named parameters from within the python command line then the query runs just fine.

推荐答案

这是解决问题的方式.

我尝试将unicode类型转换为str，结果是肯定的.

I tried typecasting unicode to str and the results were positive.

以这个为例

T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE TAG_NBR = :TAG_NBR", {'TAG_NBR': str(TAG_NBR)})

实际上，Unicode通过被编码为潜在的非Unicode数据库字符集而受到破坏.

So in effect, unicode was getting mangled by getting encoded into the potentially non-unicode database character set.

要解决这个问题，这是另一个选择

To solve that, here is another option

import os
os.environ.update([('NLS_LANG', '.UTF8'),('ORA_NCHAR_LITERAL_REPLACE', 'TRUE'),])
import cx_Oracle

以上保证我们确实处于UTF8模式.

Above guarantees that we are really in UTF8 mode.

第二个环境变量不是绝对必要.而且AFAIK没有其他方法可以设置这些变量(除非在运行应用程序本身之前)，因为NLS_LANG是 由OCI库从环境中读取.

Second environment variable one is not an absolute necessity. And AFAIK there is no other way to set these variables (except before running app itself) due the fact that NLS_LANG is read by OCI libs from the environment.

这篇关于SQL查询中的命名参数-cx_Oracle-ORA-01460:请求未实现或不合理的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！