Use SDO_CS.ADD_PREFERENCE_FOR_OP to create a preference that does what you want, then use SDO_CS.TRANSFORM in the third format listed which allows you to specify a Use Case. Specify the Use Case you set earlier as preference. That should enable you to ensure your transformation is correct.
That gives me something to try, but now I'm getting an ORA-03113 end-of-file on communication channel error. I have logged a service request with Oracle support. Thanks again for your help.
That's an odd error message to receive, because that usually signals a broken connection to your database, and should not be caused by the functions I recommended.
Can you show exactly what you did and what happenend? Usually ORA-03113 is accompanied by one or more other ORA-####, which one did you see?
First I created a preferred ops use case: SDO_CS.ADD_PREFERENCE_FOR_OP(1804,4203,4283,'AGD84 to GDA94')
Then I used SDO_CS.TRANSFORM in the form you specified: select sdo_cs.transform(SDO_GEOMETRY(2001,4203,SDO_POINT_TYPE(119.006177201804, -32.2452521646147, NULL),NULL,NULL),'AGD84 to GDA94',4283) from dual;
The grid file I am using was converted to ASCII using GDAit and loaded into the appropriate parameter value for the coord_op and method I am using.
There aren't any other error messages, just a process, and session id, and serial numbers (different each time).
I am getting the same:
SQL> exec SDO_CS.ADD_PREFERENCE_FOR_OP(1804,4203,4283,'AGD84 to GDA94'); PL/SQL procedure successfully completed. SQL> select sdo_cs.transform(SDO_GEOMETRY(2001,4203,SDO_POINT_TYPE(119.006177201 804, -32.2452521646147, NULL),NULL,NULL),'AGD84 to GDA94',4283) from dual; select sdo_cs.transform(SDO_GEOMETRY(2001,4203,SDO_POINT_TYPE(119.006177201804, -32.2452521646147, NULL),NULL,NULL),'AGD84 to GDA94',4283) from dual * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 7956 Session ID: 73 Serial number: 366 SQL> SQL> select * from v$version; ERROR: ORA-03114: not connected to ORACLE SQL> connect ******/****** Connected. SQL> select * from v$version; BANNER ---------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 18.104.22.168.0 - Production PL/SQL Release 22.214.171.124.0 - Production CORE 126.96.36.199.0 Production TNS for 32-bit Windows: Version 188.8.131.52.0 - Production NLSRTL Version 184.108.40.206.0 - Production SQL>
It seems to me you are running into a bug. I noticed that on my system COORD_OP_ID 1804 exists, and that both SRID's that you use also exist in my DB, so I gave this a try and came up with the same result. Is there anybody else willing to give this a try? I think it's a bug in Oracle. My alert-logfile tells me that on pid='7956' an Access Violation occurred. You should probably add this information to your SR.
Stefan, I think you may be getting the error because even though 1804 is a valid operation, you have no grid loaded to support it. By default Oracle does not come with any NTv2 grids.
Even with a NTv2 grid file loaded I am getting the ora-03113 error and the following allert log. I have an SR 3-7464872451 open
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7F8BF7086248] [PC:0x450A7C8, mdtxepsg_read_epsg_params2()+15652] [flags: 0x0, count: 1]
Errors in file /app/oracle/diag/rdbms/cpdev/cpdev/trace/cpdev_ora_15174.trc (incident=45136):
ORA-07445: exception encountered: core dump [mdtxepsg_read_epsg_params2()+15652] [SIGSEGV] [ADDR:0x7F8BF7086248] [PC:0x450A7C8] [Address not mapped to object] 
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
With or without the grid file loaded, this is still a bug. If it wasn't a bug, the error message should have said something about a missing file, or an invalid operation, or whatever. But it doesn't, and if you check the logfiles you can actually see that when you try this it really pulls the database down with quite a severe exception.
So in this case it does not matter that I didn't have the grid file. Oracle should still look into it, because they should give an appropriate error message instead of crashing so bad that your connection is broken off - that's far more serious in some areas where I work than having invalid coord_ops.