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

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.

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;   /