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!
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...).