Oracle table to file geodatabase

[Pierre-LucBoivin]

Hi,

I'm a beginner in Python and trying to build a script to perform and extraction from a Oracle database to a file geodatabase with a SQL query. I've been able to etablish the connection to Oracle, select the data with the query, create a new table but I have not been able to successfully output the data to the file Geodatabase.

Here the error from the geoprocessing result.



Voici mon code :

#! /usr/bin/env python
#encoding:UTF-8


import cx_Oracle
#import csv
import arcpy
from arcpy import env

# Define workspace
env.workspace = "C:\Temp\Extraction_Toad\Scratch_Toad.gdb"

# Add connections setting
dsn = cx_Oracle.makedsn("XXXX","XXX","X")
orcl = cx_Oracle.connect('XXXXX','XXXXX',dsn)
cursor = orcl.cursor()

# Get the variables
arg1 = arcpy.GetParameterAsText(0)
out_path = arcpy.GetParameterAsText(1)
out_name = arcpy.GetParameterAsText(2)
template = "C:\Temp\Extraction_Toad\Template_vue.dbf"
out_workspace = "C:\Temp\Extraction_Toad\Scratch_Toad.gdb"

# Process the query
sql = arg1
cursor.execute(sql)

rows = cursor.fetchall()

cursor.close()
orcl.close()
 
# Create a new table
arcpy.CreateTable_management(out_path,out_name,template)

# Loading data from the cursor to file g
for row in rows:
    arcpy.CopyRows_management(row,out_workspace)
    

Thanks for you help

[David_JAnderson]

A few things jump out to me.
First, without seeing what is in the sql query it is rather hard to figure out the input side of the algorithm.  Also running raw sql from the command line is a big security hole.
Next your output location is a geodatabase, not a table. Change this statement:
out_workspace = "C:\Temp\Extraction_Toad\Scratch_Toad.gdb"
to
out_workspace = r"C:\Temp\Extraction_Toad\Scratch_Toad.gdb\" + out_name
or
out_workspace = "C:\\Temp\\Extraction_Toad\\Scratch_Toad.gdb\\" + out_name
or
out_workspace = "C:/Temp/Extraction_Toad/Scratch_Toad.gdb/" + out_name
Multiple versions because Python does not deal well with \ as a directory separator.

I would also probably wait to close out the Oracle cursor and connection until the end of the script.