sa_describe_shapefile system procedure

sa_describe_shapefile( 
shp_filename 
, srid 
[, encoding ] 
)

shp_filename A VARCHAR(512) parameter that identifies the location of the ESRI shapefile. The file name must have the extension .shp and must have an associated .dbf file with the same base name located in the same directory. The path is relative to the database server, not the client application.
srid An INTEGER parameter that identifies the SRID for the geometries in the shapefile. Specify NULL to indicate the column can store multiple SRIDs. Specifying NULL limits the operations that can be performed on the geometry values.
encoding An optional VARCHAR(50) parameter that identifies the encoding to use when reading the shapefile. The default is NULL. When encoding is NULL, the ISO-8859-1 character set is used.

Column name	Data type	Description
column_number	INTEGER	The ordinal position of the column described by this row, starting at 1.
name	VARCHAR(128)	The name of the column.
domain_name_with_size	VARCHAR(160)	The data type name, including size and precision (as used in CREATE TABLE or CAST functions).

The sa_describe_shapefile system procedure is used to describe the name and type of columns in an ESRI shapefile. This information can be used to create a table to load data from a shapefile using the LOAD TABLE or INPUT statements. Alternately, this system procedure can be used to read a shapefile by specifying the WITH clause for OPENSTRING...FORMAT SHAPEFILE.

If the -gl database option is set to DBA, you must have one of the following system privileges:
- ALTER ANY TABLE
- ALTER ANY OBJECT
- LOAD ANY TABLE
- READ FILE
If the -gl database option is set to ALL, no privileges are required.
If the -gl database option is set to NONE, you must have the READ FILE system privilege.

The following example displays a string that was used to create a table for storing shapefile data:

BEGIN
    DECLARE create_cmd LONG VARCHAR;
    SELECT 'create table if not exists esri_load( record_number int primary key, ' ||
         (SELECT list( name || ' ' || domain_name_with_size, ', ' ORDER BY column_number )
     FROM sa_describe_shapefile( 'c:\\esri\\tgr36069trt00.shp', 1000004326 )
     WHERE column_number > 1 ) || ' )' 
     INTO create_cmd;
     SELECT create_cmd;
     EXECUTE IMMEDIATE create_cmd;
END

You can load the shapefile data into the table using the following statement (provided that you have the LOAD ANY TABLE system privilege and that the -gl database option has not been set to NONE):

LOAD TABLE esri_load 
USING FILE 'c:\\esri\\tgr36069trt00.shp' 
FORMAT SHAPEFILE;

sa_describe_shapefile system procedure

Syntax

Arguments

Result set

Remarks

Privileges

See also

Example