Background:
I have an Oracle package with procedures to randomize and reset passwords for our SDE/GIS schemas.
These procedures work perfectly via SQL Plus command line commands.
My package looks like this:
And it looks like this in ArcToolbox:
It is confusing to me that the same call - formatted the same way as the SQL Plus command line would cause such an error. But obviously I'm missing something here.
As an addendum, these procedures were originally getting a single string as parameters in the command line and had worked via the ArcToolbox calls. And they have been reworked to receive multiple strings for input parameters.
The addition of "TYPE schema_name_var IS TABLE OF VARCHAR2(1000);" in the package facilitates the processing of those parameters from the SQL Plus command line.
Can anyone shed some light on what I am missing here to complete the functionality?
أكثر...
I have an Oracle package with procedures to randomize and reset passwords for our SDE/GIS schemas.
These procedures work perfectly via SQL Plus command line commands.
My package looks like this:
create or replace package gis_pass_pkg as
TYPE schema_name_var IS TABLE OF VARCHAR2(1000);
procedure randomize_pass(schema_name in schema_name_var); procedure reset_pass(schema_name in schema_name_var); end gis_pass_pkg; / Procedures omitted for brevity
My call to the procedure looks like this in SQL Plus:TYPE schema_name_var IS TABLE OF VARCHAR2(1000);
procedure randomize_pass(schema_name in schema_name_var); procedure reset_pass(schema_name in schema_name_var); end gis_pass_pkg; / Procedures omitted for brevity
exec gis_pass_pkg.randomize_pass(gis_pass_pkg.schema_name_var('SCHEMA1','SCHEMA2'));
I have created the python code in the toolbox that replicates this call.
And it looks like this in ArcToolbox:
SQLexe = "call gis_pass_pkg.reset_pass(gis_pass_pkg.schema_name_var('SCHEMA1','SCHEMA2')); arcpy.AddMessage(SQLexe) sysConn.execute(SQLexe)
Here is the error I receive when executing through the toolbox script:
Traceback (most recent call last): File "T:\DataCenter\Citrix\AppData01\clhays\Application Data\ESRI\Desktop10.2\ArcToolbox\My Toolboxes\SDE Manager Scripts\ResetPasswordsViaPackage.py", line 53, in sysConn.execute(SQLexe) File "c:\arcgis\desktop10.2\arcpy\arcpy\arcobjects\arcobjects.py", line 27, in execute return convertArcObjectToPythonObject(self._arc_object.Execute(*gp_fixargs(args))) AttributeError: ArcSDESQLExecute: SreamExecute ArcSDE Extended error 902 ORA-00902: invalid datatype
Failed to execute (resetpasswords).
I am assuming that the Oracle error of "ORA-00902: invalid datatype" is related to configuration of the procedure call from the toolbox.Failed to execute (resetpasswords).
It is confusing to me that the same call - formatted the same way as the SQL Plus command line would cause such an error. But obviously I'm missing something here.
As an addendum, these procedures were originally getting a single string as parameters in the command line and had worked via the ArcToolbox calls. And they have been reworked to receive multiple strings for input parameters.
The addition of "TYPE schema_name_var IS TABLE OF VARCHAR2(1000);" in the package facilitates the processing of those parameters from the SQL Plus command line.
Can anyone shed some light on what I am missing here to complete the functionality?
أكثر...