Discussions
Categories
- 382.3K All Categories
- 2.1K Data
- 210 Big Data Appliance
- 1.9K Data Science
- 448K Databases
- 221K General Database Discussions
- 3.7K Java and JavaScript in the Database
- 25 Multilingual Engine
- 520 MySQL Community Space
- 467 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 492 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.2K SQL & PL/SQL
- 21K SQL Developer
- 293.3K Development
- 7 Developer Projects
- 128 Programming Languages
- 290.1K Development Tools
- 95 DevOps
- 3K QA/Testing
- 645.5K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 16 Java Essentials
- 144 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 198 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 270 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 166 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 230 Portuguese
How to return VARCHAR2 back to Python
Two part question here:
1. I need to assign VARCHAR2 datatype to return value. As it is right now, I am getting AttributeError: module 'cx_Oracle' has no attribute 'VARCHAR2'
2. Does my query look like it's going to work?
line_item_sku = 'Axt56_XT_87' div = cur.var(cx_Oracle.VARCHAR2) sql = (""" SELECT div FROM div_lookup WHERE fix = :x, x=substr('line_item_sku',1,1) """) cur.execute(sql) div, = cur.fetchone() print (div) Best Answer
-
I think you want something like:
line_item_sku = 'Axt56_XT_87'
sql = (""" SELECT div
FROM div_lookup
WHERE fix = substr(:s,1,1)
""")
cur.execute(sql, [line_item_sku])
div, = cur.fetchone()
print (div)
If I'm wrong, start at the beginning and explain what the table column datatypes are, and then what you are trying to pass in and fetch out.
More on binding is discussed in https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html
If you want to do some type mapping when fetching a query column, then try an Output Type Handler, see the doc in https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html#changing-query-results-with-outconverters
Most cx_Oracle discussion occurs on GitHub: https://github.com/oracle/python-cx_Oracle/issues
There are cx_Oracle samples in https://github.com/oracle/python-cx_Oracle/tree/master/samples
Answers
-
I think you want something like:
line_item_sku = 'Axt56_XT_87'
sql = (""" SELECT div
FROM div_lookup
WHERE fix = substr(:s,1,1)
""")
cur.execute(sql, [line_item_sku])
div, = cur.fetchone()
print (div)
If I'm wrong, start at the beginning and explain what the table column datatypes are, and then what you are trying to pass in and fetch out.
More on binding is discussed in https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html
If you want to do some type mapping when fetching a query column, then try an Output Type Handler, see the doc in https://cx-oracle.readthedocs.io/en/latest/user_guide/sql_execution.html#changing-query-results-with-outconverters
Most cx_Oracle discussion occurs on GitHub: https://github.com/oracle/python-cx_Oracle/issues
There are cx_Oracle samples in https://github.com/oracle/python-cx_Oracle/tree/master/samples