sa_describe_shapefile System Procedure

Syntax

sa_describe_shapefile( 
    shp_filename 
    , srid 
[, encoding ] 
)

Arguments

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.

Result Set

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

Remarks

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.

Privileges

If the -gl database option is set to DBA, you requires one of the following:
- ALTER ANY TABLE system privilege
- ALTER ANY OBJECT system privilege
- LOAD ANY TABLE system privilege
- READ FILE system privilege
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.

Example

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;