Re: Named Parameters Issue - ORA-01460: unimplemented or unreasonable conversion requested (Anurag Chourasia)
Jani Tiainen <[email protected]>
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <[email protected]> |
10.4.2013 19:16, Anurag Chourasia kirjoitti:
> Hi Jani and Mark....I have some positive updates :-)
>
> Following Mark Harrison's suggestion, I tried typecasting unicode to str
> and the results were positive.
>
> This one worked for example
>
> T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
> TAG_NBR = :TAG_NBR", {'TAG_NBR': str(TAG_NBR)})
>
>
> Following Jani's suggestion, i added this just before my cx_Oracle
> import and removed the str() typecast conversion
>
> import os
> os.environ.update([('NLS_LANG',
> '.UTF8'),('ORA_NCHAR_LITERAL_REPLACE', 'TRUE'),])
>
>
>
> T_cursor.execute("SELECT DISTINCT(TAG_STATUS) FROM TAG_HIST WHERE
> TAG_NBR = :TAG_NBR", {'TAG_NBR': TAG_NBR})
>
>
> And it worked just fine
>
> Then i changed it to
>
> import os
> os.environ.update([('NLS_LANG', '.UTF8'),])
>
>
> And it worked fine as well.
>
> I will do more tests during the day.
>
> Jani : Is the variable ORA_NCHAR_LITERAL_REPLACE absolutely necessary?
ORA_NCHAR_LITERAL_REPLACE means that if using N'<stringhere>' string is
put to column as is, there is no mangling to database charset. It's OFF
by default and even you try to insert unicode it will be first
translated to database charset and inserted after that. Which leads to
broken data.
> Also, I could not find out the current value of NLS_LANG. . . When i
> query from sqlplus it is not set...And when i use os.environ to get the
> value, i get key error.
>
> SQL> HOST ECHO %NLS_LANG%
> %NLS_LANG%
On Windows you have two possible options, normally the NLS_LANG is set
in the registry, but it can also be set in the environment, however this
is not often done. The value in the environment takes precedence over
the value in the registry and is used for ALL Oracle_Homes on the
server. Also note that any USER environment variable takes precedence
over any SYSTEM environment variable (this is Windows behavior, and has
nothing to do with Oracle) if set.
if: SQL> HOST ECHO $NLS_LANG return nothing it means that registry is
used. Then do the following:
SQL>@.[%NLS_LANG%].
If you get something like:
Unable to open file.[ENGLISH_UNITED KINGDOM.WE8ISO8859P1].
The "file name" between the braces is the value of the registry parameter.
SELECT * FROM NLS_SESSION_PARAMETERS; gives all the rest session vars
regarding to NLS.
>
> Regards,
> Anurag
>
> On Wed, Apr 10, 2013 at 4:01 AM, Jani Tiainen <[email protected]
> <mailto:[email protected]>> wrote:
>
> Ok, I do following when working with cx_Oracle:
>
> import os
> os.environ.update([
> # Oracle takes client-side character set encoding from the
> environment.
> ('NLS_LANG', '.UTF8'),
> # This prevents unicode from getting mangled by getting encoded
> into the
> # potentially non-unicode database character set.
> ('ORA_NCHAR_LITERAL_REPLACE', 'TRUE'),
> ])
>
> import cx_Oracle
>
> Above guarantees that you really are in UTF8 mode. Second environment
> variable one is not necessity. And AFAIK there is no other way to set
> them (except before running app itself) due the fact that NLS_LANG is
> read by OCI libs from the environment.
>
>
> After doing that I'll do the following:
>
> db = cx_Oracle.Connection(runenv.USERNAME, runenv.PASSWORD,
> runenv.TNSENTRY)
> c = db.cursor()
> c.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD
> HH24:MI:SS' "
> "NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' "
> "NLS_TERRITORY = 'AMERICA'")
>
> Idea is to make sure that dateformats and decimal points are consistent.
> For example my locale likes to use comma as a decimal separator.
>
> Which means that python 10.2f must be '10,2' when inserting to db.
> Painful that is.
>
>
> 9.4.2013 12:57, Anurag Chourasia kirjoitti:
> > Hi Jani. The column type is varchar2
> >
> > Regards,
> > Anurag
> >
> > On 9 Apr 2013 01:14, "Jani Tiainen" <[email protected]
> <mailto:[email protected]>
> > <mailto:[email protected] <mailto:[email protected]>>> wrote:
> >
> > What is the type of the column in the table?
> >
> > There are few quirks I know of.
> >
> > 9.4.2013 1:35, Anurag Chourasia kirjoitti:
> > > Thanks. Will try that too and report back the findings.
> > >
> > > Regards,
> > > Anurag
> > >
> > > On Mon, Apr 8, 2013 at 6:00 PM, Mark Harrison
> <[email protected] <mailto:[email protected]>
> > <mailto:[email protected] <mailto:[email protected]>>
> > > <mailto:[email protected] <mailto:[email protected]>
> <mailto:[email protected] <mailto:[email protected]>>>> wrote:
> > >
> > > On 4/8/13 2:56 PM, Anurag Chourasia wrote:
> > > > Hi Tamas,
> > > >
> > > > Thanks for the response. Here is the result...
> > > >
> > > > TAG_NBR: u'2928982' <type 'unicode'>
> > > >
> > > > Please let me know if this gives any more ideas. I will
> > also try
> > > a explicit type conversion using str() to see if that
> makes a
> > > difference.
> > >
> > > If that doesn't work can you try using a non-unicode
> string
> > such as
> > >
> > > {'TAG_NBR': 'ABC'}
> > >
> > >
> > >
> > >
> >
> ------------------------------------------------------------------------------
> > > Minimize network downtime and maximize team effectiveness.
> > > Reduce network management and security costs.Learn how
> to hire
> > > the most talented Cisco Certified professionals. Visit the
> > > Employer Resources Portal
> > >
> http://www.cisco.com/web/learning/employer_resources/index.html
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> > <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>>
> > > <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> > <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>>>
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> > >
> > >
> > >
> > >
> >
> ------------------------------------------------------------------------------
> > > Minimize network downtime and maximize team effectiveness.
> > > Reduce network management and security costs.Learn how to hire
> > > the most talented Cisco Certified professionals. Visit the
> > > Employer Resources Portal
> > >
> http://www.cisco.com/web/learning/employer_resources/index.html
> > >
> > >
> > >
> > > _______________________________________________
> > > cx-oracle-users mailing list
> > > cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> > <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>>
> > > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> > >
> >
> >
> > --
> > Jani Tiainen
> >
> > - Well planned is half done and a half done has been sufficient
> > before...
> >
> >
> ------------------------------------------------------------------------------
> > Precog is a next-generation analytics platform capable of
> advanced
> > analytics on semi-structured data. The platform includes APIs for
> > building
> > apps and a phenomenal toolset for data science. Developers
> can use
> > our toolset for easy data analysis & visualization. Get a
> free account!
> > http://www2.precog.com/precogplatform/slashdotnewsletter
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> > <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>>
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> >
> ------------------------------------------------------------------------------
> > Precog is a next-generation analytics platform capable of advanced
> > analytics on semi-structured data. The platform includes APIs for
> building
> > apps and a phenomenal toolset for data science. Developers can use
> > our toolset for easy data analysis & visualization. Get a free
> account!
> > http://www2.precog.com/precogplatform/slashdotnewsletter
> >
> >
> >
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
>
>
> --
> Jani Tiainen
>
> - Well planned is half done and a half done has been sufficient
> before...
>
> ------------------------------------------------------------------------------
> Precog is a next-generation analytics platform capable of advanced
> analytics on semi-structured data. The platform includes APIs for
> building
> apps and a phenomenal toolset for data science. Developers can use
> our toolset for easy data analysis & visualization. Get a free account!
> http://www2.precog.com/precogplatform/slashdotnewsletter
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> <mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org>
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
--
Jani Tiainen
- Well planned is half done and a half done has been sufficient before...
------------------------------------------------------------------------------
Precog is a next-generation analytics platform capable of advanced
analytics on semi-structured data. The platform includes APIs for building
apps and a phenomenal toolset for data science. Developers can use
our toolset for easy data analysis & visualization. Get a free account!
http://www2.precog.com/precogplatform/slashdotnewsletter