SQL syntax error in creating spatial network

Question

I am trying to create a spatial network from a shapefile (representing street centrelines) imported into an Oracle DB with FME Desktop. The 'CENTRELINES' spatial object contains a GEOM column that I'd like to use as the basis for a network analysis to allocate ambulance facilities (points) among retirement homes (points) based on route distance as a cost attribute. Any advice on methodology for approaching this morbid problem in Oracle Spatial would be welcome, but the main issue is that I am a beginner at SQL. I've used Oracle's documentation to compose the following SQL statement:

-- create an LRS geometry network
EXEC SDO_NET.CREATE_LRS_NETWORK(
  'LRS_net', -- network name
  'CENTRELINES', -- LRS geometry table name
  'GEOM', -- LRS geometry column name
  1, -- number of hierarchy levels
  FALSE, -- directed link?
  TRUE -- node with cost?
  );

The script outputs the following:

Error starting at line 2 in command:
EXEC SDO_NET.CREATE_LRS_NETWORK(
Error report:
ORA-06550: line 1, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

   ( ) - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   table continue avg count current exists max min prior sql
   stddev sum variance execute multiset the both leading
   trailing forall merge year month day hour minute second
   timezone_hour timezone_minute timezone_region timezone_abbr
   time timestamp interval date
   <a string literal with character set specification>
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

...

Error starting at line 9 in command:
)
Error report:
Unknown Command

I understand that line 2 is producing the error:

PLS-00103: Encountered the symbol ";" when expecting one of the following...

Given that semi-colons are required to end an SQL query, why is this a problem?

EDIT: The following script produced the network by adding begin/end:

begin
SDO_NET.CREATE_LRS_NETWORK(
  'LRS_net', 
  'CENTRELINES', 
  'GEOM', 
  1, 
  FALSE, 
  TRUE);
end;

Thank you for your help!

Bechuanaland

I added the begin/end syntax: – Bechuanaland

You need to add a semicolon to line 8. Change TRUE) to TRUE);. – Bomber

Answer 1

As noted in the documentation, the SQL*Plus execute command normally has to be entered on one line:

If your EXECUTE command cannot fit on one line because of the PL/SQL statement, use the SQL*Plus continuation character (a hyphen).

What it's actually trying to run under the hood is a translation as

BEGIN
    SDO_NET.CREATE_LRS_NETWORK(;
END;
/

... which is (maybe obviously when written like that) not valid PL/SQL. Nothing to do with the spatial call per se. If you do want to split it onto multiple lines, you can just use an explicit begin/end rather than the shorthand exec.

The second problem maybe suggests that you've run the short version more than once, though it isn't a feature I'm very familiar with; but is not related to the initial semi-colon error. (Also, a semi-colon isn't strictly needed to end a SQL statement, but that's a detail for another time...).

Cooke