Wednesday, March 30, 2022

ORA-00902: invalid datatype Issue

 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\", line 53, in sysConn.execute(SQLexe) File "c:\arcgis\desktop10.2\arcpy\arcpy\arcobjects\", 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;   


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

No comments: