0

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:

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:

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.

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?

  • SQLPlus is a very different animal than a simple SQL cursor. This isn't an ArcGIS error, it's a database error. I doubt you can make this work through the ArcGIS data connection. – Vince Dec 30 '15 at 21:02
  • Yes, I understand that SQL is different. But, if I call the procedure with a single string as a parameter (and make the oracle package take a single string parameter) then the following call from the toolbox works fine. SQLexe = "call gis_pass_pkg.reset_pass('SCHEMA'); arcpy.AddMessage(SQLexe) sysConn.execute(SQLexe) Therein lies the confusion. I assume that something in the ArcSQLExecute is not passing things properly. – stratagem Dec 31 '15 at 4:01
  • I'm assuming the Oracle functions invoked by SE_stream_prepare_sql/SE_stream_execute cannot handle the strings they passed on as-is. If passing one parameter works, but two fails, then rewrite your package to take one parameter with a delimited string. – Vince Dec 31 '15 at 4:56
0

Thank you Vince for the direction regarding delimited string, it allowed me to find a workable solution.

I had to change the way the procedure operated using logic from the following post:
how do I split a comma delimited string into an array

The response in particular included the following code:

declare a dbms_utility.uncl_array;

len  pls_integer;   

begin

dbms_utility.comma_to_table('One,Two,Three,Four', len, a);

for i in 1..a.count loop
  dbms_output.put_line( a(i) );
end loop;   end;   /

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.