Oracle's SDO_NET.FIND_CONNECTED_COMPONENTS fails with 'wrong number or types of arguments in call'

Question

I am attempting to create an Oracle Network Model to analyze a fibre optic communications network using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production. All the steps are successful up until I call SDO_NET.FIND_CONNECTED_COMPONENTS which always fails with the generic 'wrong number or types of arguments in call'.

I'm hoping someone can point out how to correct this.

Below are steps to reproduce the problem.

-- Create a network
EXEC SDO_NET.CREATE_SDO_NETWORK('TEST2',1,FALSE,FALSE);

-- verify metadata created
SELECT * FROM USER_SDO_NETWORK_METADATA;

-- verify Node, Link and Path tables created
DESCRIBE TEST2_NODE$;

DESCRIBE TEST2_LINK$;

DESCRIBE TEST2_PATH$;

-- Insert spatial metadata for each
INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'TEST2_NODE$',
  'GEOMETRY',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', 200000.0, 1900000.0, 0.0005),
    MDSYS.SDO_DIM_ELEMENT('Y', 300000.0, 1800000.0, 0.0005)
  ),
  3005
);

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'TEST2_LINK$',
  'GEOMETRY',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', 200000.0, 1900000.0, 0.0005),
    MDSYS.SDO_DIM_ELEMENT('Y', 300000.0, 1800000.0, 0.0005)
  ),
  3005
);

INSERT INTO USER_SDO_GEOM_METADATA VALUES (
  'TEST2_PATH$',
  'GEOMETRY',
  MDSYS.SDO_DIM_ARRAY(
    MDSYS.SDO_DIM_ELEMENT('X', 200000.0, 1900000.0, 0.0005),
    MDSYS.SDO_DIM_ELEMENT('Y', 300000.0, 1800000.0, 0.0005)
  ),
  3005
);

-- validate
SELECT SDO_NET.validate_network('TEST2') FROM DUAL;
-- TRUE

-- Create minimal network example
--node1
INSERT INTO TEST2_NODE$ (node_id, node_name, geometry) VALUES (2,'BL-OR',MDSYS.SDO_GEOMETRY(2001, 3005, MDSYS.SDO_POINT_TYPE(1208829.98094467, 483100.114700435, NULL), NULL, NULL));

--node2
INSERT INTO TEST2_NODE$ (node_id, node_name, geometry) VALUES (6,'BL-OR',MDSYS.SDO_GEOMETRY(2001, 3005, MDSYS.SDO_POINT_TYPE(1208390.49330847, 479562.751714209, NULL), NULL, NULL));

--node3
INSERT INTO TEST2_NODE$ (node_id, node_name, geometry) VALUES (10,'BL-OR',MDSYS.SDO_GEOMETRY(2001, 3005, MDSYS.SDO_POINT_TYPE(1210090.56995981, 478616.028058535, NULL), NULL, NULL));


--link1 (node1 - node2)
INSERT INTO TEST2_link$ (link_id, link_name, start_node_id, end_node_id, COST, LINK_LEVEL , geometry) VALUES
(2,'BL-OR',2,6,1,1,
MDSYS.SDO_GEOMETRY(2006, 3005, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(1208829.98094467, 483100.114700435, 1208390.49330847, 479562.751714209)));

--link2 (node2- node3)
INSERT INTO TEST2_link$ (link_id, link_name, start_node_id, end_node_id, cost, geometry) VALUES
(6,'BL-OR',6,10,0,
MDSYS.SDO_GEOMETRY(2006, 3005, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(1208390.49330847, 479562.751714209, 1210090.56995981, 478616.028058535)));


-- verify network
SELECT SDO_NET.validate_network('TEST2') FROM DUAL; 
-- TRUE

SELECT SDO_NET.GET_NO_OF_NODES('TEST2') FROM DUAL;
-- 3

SELECT SDO_NET.GET_NO_OF_LINKS('TEST2') FROM DUAL; 
-- 2

SELECT SDO_NET.GET_ISOLATED_NODES('TEST2') FROM DUAL;
-- null

SELECT SDO_NET.GET_INVALID_LINKS('TEST2') FROM DUAL;
-- null

SELECT SDO_NET.GET_NODE_DEGREE('TEST2', 6) FROM DUAL;
-- 2

-- Find connected components T
EXECUTE SDO_NET.FIND_CONNECTED_COMPONENTS('TEST2');

/* THIS FAILS WITH THE FOLLOWING ERROR

Error starting at line : 2 in command -
BEGIN SDO_NET.FIND_CONNECTED_COMPONENTS('TEST2'); END;
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'FIND_CONNECTED_COMPONENTS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

*/

UPDATE 2024/01/22 Working with our DBA I have directory to write the log file which the procedure requires. Thanks @Littlefoot

Answer 1

Peter, You probably have figured it out by now. You simply create a directory and grant READ/WRITE privileges to your DB user.

create directory CC_LOG_DIR as '<folder_path>'; grant read, write on directory CC_LOG_DIR to <db_user>;

The resulting table looks then as follows:

LINK_LEVEL NODE_ID COMPONENT_ID

---

     1          2            1
     1          6            1
     1         10            1

Answer 2

Looks like SDO_NET.FIND_CONNECTED_COMPONENTS documentation is misleading if you don't read it all.

It says that syntax is

SDO_NET.FIND_CONNECTED_COMPONENTS(network IN VARCHAR2);

but further reading (as well as example) reveals some more parameters, e.g.

EXECUTE SDO_NET.FIND_CONNECTED_COMPONENTS(
  network              => 'SDO_PARTITIONED', 
  link_level           => 1,
  component_table_name => 'sdo_partitioned_conn_comp_tab',
  log_loc              => 'LOG_DIR', 
  log_file             => 'sdo_partitioned.log',
  open_mode            => 'a');

which means that error you got isn't about wrong type, but wrong number of arguments.

Have a look at documentation, it describes what parameters mean and then try to fix statement you're about to execute.

Answer 3

Beside the Spatial function SDO_NET.FIND_CONNECTED_COMPONENTS, you can also consider using the Property Graph support for the Oracle Database with built-in algorithms to detect connected components. Have a look at this Github repo. If you are not familiar with Oracle Property Graph, you can start here.