Skip Headers
Oracle® Database Administrator's Reference
10g Release 1 (10.1) for hp OpenVMS Alpha
Part No. B13738-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

7 Building and Running Demonstrations

This chapter describes how to build and run the SQL*Loader and PL/SQL demonstrations installed with Oracle Database 10g. It contains the following sections:


Note:

To use the demonstrations described in this chapter, you must install Oracle Database Examples included on the Oracle Database 10g Companion CD.

You must also unlock the SCOTT account and set the password before creating the demonstrations.


7.1 PL/SQL Demonstrations

PL/SQL includes a number of sample programs that you can load. The Oracle Database 10g database must be open and mounted to work with the sample programs.

This section contains the following topics:

7.1.1 PL/SQL Kernel Demonstrations

The following PL/SQL kernel demonstrations are available:

examp1.sql
examp2.sql
examp3.sql
examp4.sql
extproc.sql
examp5.sql
examp6.sql
examp7.sql
examp8.sql
examp11.sql
examp12.sql
examp13.sql
examp14.sql
sample1.sql
sample2.sql
sample3.sql
sample4.sql

To build and run the PL/SQL kernel demonstrations, enter the following commands:

  1. Run SQL*Plus and connect as SCOTT/TIGER:

    $ set default ora_root:[PLSQL.demo]
    $ sqlplus scott/tiger
    
    
  2. To load the demonstrations, enter the following command:

    SQL> @exampn.sql
    
    

    In this command, n denotes a unique integer value for each demonstration file.


    Note:

    Build the demonstrations as any Oracle user with sufficient permissions. Run the demonstrations using the same Oracle user account.

To run the extproc demonstration:

  1. Add the following lines to the tnsnames.ora file:

    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=plsff))(CONNECT_DATA=(SID=extproc)))
    
    
  2. Add the following line to the listener.ora file:

    SID_LIST_LISTENER=(SID_LIST=(SID_DESC= - (SID_NAME:extproc)(program=PIST03:[xyz.network.admin]extproc)))
    
    
  3. From SQL*Plus, enter the following commands:

    SQL> CONNECT SYSTEM/MANAGER
    Connected.
    SQL> GRANT CREATE LIBRARY TO SCOTT;
    Grant succeeded.
    SQL> CONNECT SCOTT/TIGER
    Connected.
    SQL> CREATE LIBRARY DEMOLIB AS 'ora_root:[bin]extproc.exe';
    Library created.
    
    
  4. To run the demonstration, enter the following command:

    SQL> @extproc
    

7.1.2 PL/SQL Precompiler Demonstrations

The following precompiler demonstrations are available:

examp9.pc
examp10.pc
sample5.pc
sampel6.pc

To build a single demonstration, perform the following steps for the examp9 example.

$ proc examp9.pc
$ define rt_includes ora_progint_incl,ora_progint_vms_hdrs,ora_progint_decc_hdrs
$ CC/nowarn/include=rt_includes examp9.c
$ lnproc examp9

To run the examp9 demonstration, enter the following command:

$ Run examp9

7.2 RDBMS Demonstrations

To build and run the RDBMS demonstrations:

  1. Ensure that the supported version of the C programming language compiler for this release is installed.

  2. Set the default directory to ora_rdbms_demo.

  3. Set up the ORA_OLB logical.

    define ora_olb ora_rdbms_demo,ora_rdbms,ora_olb32,ora_root:[rdbms.lib32]
    
    
  4. Set up the ORA_UTIL logical.

    define ora_util ora_olb
    
    
  5. Compile the C programming language file by using the following command::

    cc/prefix=all/includ=(sys$disk:[],ORA_ROOT:[RDBMS.public])/FLOAT=IEEE/IEEE_MODE=DENORM/GRAN=BYTE/arch=ev56-/opt=tune=ev6/exte=stri/pref=all/nostan/noans/name=(short,as_is)demo_file-+decc$library:[000000]DECC$RTLDEF.TLB/LIB
    
    

    In this command, replace demo_file with the name of the C programming language file that you want to build.

  1. Link the demonstration using lnocic:

  1. lnocic cdemo1.exe cdemo1.obj
    
    

    In cases where the command for building the demonstartion accepts command-line parameters, you can define a new symbol that is treated as the equivalent of the executable name. For example:

    $ oci19 := $ora_root:[rdbms.demo]oci19.exe
    oci19 4
    

7.2.1 Extensible Indexing Demonstrations

To run the Extensible Indexing demonstrations:

  1. Use the same command for compiling the C file as described in the earlier procedure.

  2. For extdemo2, extdemo4 and extdemo5, you must create an opt file with the specific entry points for the shared image. The opt file contents for each demonstration are as follows:

    • extdemo.opt

      case_sensitive=YESsymbol_vector = (-Initialize=PROCEDURE,INITIALIZE/Initialize=PROCEDURE-,Iterate=PROCEDURE,ITERATE/Iterate=PROCEDURE-,Terminate=PROCEDURE,TERMINATE/Terminate=PROCEDURE-,Merge=PROCEDURE,MERGE/Merge=PROCEDURE-,Delete=PROCEDURE,DELETE/Delete=PROCEDURE-,WrapContext=PROCEDURE,WRAPCONTEXT/WrapContext=PROCEDURE-)case_sensitive=NO
      
      
    • extdemo2.opt

      case_sensitive=YESsymbol_vector = (-qxiqtbi=PROCEDURE,QXIQTBI/qxiqtbi=PROCEDURE-,qxiqtbd=PROCEDURE,QXIQTBD/qxiqtbd=PROCEDURE-,qxiqtbu=PROCEDURE,QXIQTBU/qxiqtbu=PROCEDURE-,qxiqtbs=PROCEDURE,QXIQTBS/qxiqtbs=PROCEDURE-,qxiqtbf=PROCEDURE,QXIQTBF/qxiqtbf=PROCEDURE-,qxiqtbc=PROCEDURE,QXIQTBC/qxiqtbc=PROCEDURE-)case_sensitive=NO
      
      
    • extdemo3.opt

      To build and run extdemo3 demonstrations, the classpath should be:

      $define classpath ".:/jdbc_lib/classes12.jar:/sqlj_lib/runtime12.jar:/ora_rdbms_jlib/xdb.jar:/ora_xdk_lib/xmlparserv2.jar:/jdbc_lib/jndi.jar:/jis_lib/jta.jar:/ora_rdbms_jlib/ODCI.jar:/ora_rdbms_jlib/CartridgeServices.jar:."
      
      
    • extdemo5.opt

      case_sensitive=YESsymbol_vector = (-qxiqtbpi=PROCEDURE,QXIQTBPI/qxiqtbpi=PROCEDURE-,qxiqtbpd=PROCEDURE,QXIQTBPD/qxiqtbpd=PROCEDURE-,qxiqtbpu=PROCEDURE,QXIQTBPU/qxiqtbpu=PROCEDURE-,qxiqtbps=PROCEDURE,QXIQTBPS/qxiqtbps=PROCEDURE-,qxiqtbpf=PROCEDURE,QXIQTBPF/qxiqtbpf=PROCEDURE-,qxiqtbpc=PROCEDURE,QXIQTBPC/qxiqtbpc=PROCEDURE-)case_sensitive=NO'
      
      
  3. Link the executable with loutl using the shared option along with the option file that corresponds to the demonstration you are building. For example:

    loutl EXTDEMO2 EXTDEMO2.opt/opt,EXTDEMO2.obj EXTDEMO2 I 
    
    
  4. Create the user for the demonstration and grant the necessary privileges to the user as follows:

    connect system/managerdrop user extdemo2 cascade;create user extdemo2identified by extdemo2default tablespace systemquota unlimited on system ;grant connect, resource to extdemo2 ;grant create library to extdemo2 ;grant create any directory to extdemo2 ;grant drop any directory to extdemo2 ;grant create any operator to extdemo2 ;grant create indextype to extdemo2 ;grant create table to extdemo2 ; 
    
    
  5. As directed in the SQL script, create the associated library in the user schema as follows:

    connect extdemo2/extdemo2CREATE OR REPLACE LIBRARY extdemo2l IS     'vqat5:[10ghome.rdbms.demo]extdemo2.exe' ;/
    
  6. Run the following SQL script:

    SQL>@extdemo2.sql
    
    
  7. For extended extdemo4.sql, replace the path in the create library command with the path for the generated executable.

7.3 RDBMS C++ File Demonstrations

To build and run the rdbms C++ file demonstrations:

  1. Ensure that the supported version of the C++ programming language compiler for this release is installed.

  2. Set the default directory to ora_rdbms_demo.

  3. Compile the C++ file by running the following command:

    cxx/nostandard/debug=trace/optimize/prefix=all/gran=long/names=(as_is,short)-
    /include=([])/noansi/extern=strict demo_file.cpp
    
    

    In this command, replace demo_file.cpp with the name of the file that you want to build.

  4. To link the OCCI C++ programming language demonstrations, use the following command:

    lnocic OCciblob.exe -OCciblob.obj,ora_olb:xaondy.obj,ora_olb:libocci10.olb/LIB CPP NS
    
    

    In this command, replace OCciblob.exe with the name of the file that you want to build.

7.4 RDBMS Java File Demonstrations

This section contains the following topics:

7.4.1 aqjms Demonstrations

To build and run the aqjms demonstrations:

  1. While performing the steps outlined in the aqjmsreadme.txt file, make the following changes:


    Note:

    The aqjmsreadme.txt can be found under ora_root:[rdbms.demo].

    1. Replace $ORACLE_HOME in the classpath specification with the corresponding OpenVMS logical or the abbreviated logical for the referenced directory. You must set up the JDK version before performing this step. For example:

      Replace $ORACLE_HOME/rdbms/jlib/aqapi13.jar

      with /ORACLE_HOME/rdbms/jlib/aqapi13.jar

      or use /ora_rdbms_jlib/aqapi13.jar

  2. In certain cases, the abbreviated form may have to be used to circumvent command line length limitations. Alternatively, the -V option may be used on OpenVMS java, and all parameters (including the classpath) can be placed in a DAT file.


    Note:

    In the current release, the following demonstrations do not support the oci8 driver on OpenVMS:
    • aqjmsdemo01.java

    • aqjmsdemo02.java

    • aqjmsdemo05.java

    • aqjmsdemo06.java

    • aqjmsdemo08.java


7.4.2 rmanpipe.sql Demonstrations

The rmanpipe.sql script uses vms_rman_pipe.com, the OpenVMS-specific COM file, to emulate the UNIX operator, which creates a separate, detached process to run the specified command. The COM file dynamically creates a vmspipe.com file. When it is run, the vmspipe.com file creates a corresponding log vmspipe.log in the ora_rdbms_demo directory.

7.4.3 JavaVM Demonstrations

The JavaVM demonstrations are available in the following directory:

ora_root:[javavm.demo.examples.jsproc.basic]

For each sample, scripts are provided with a name of the form build_run_*.com. When you run these scripts, the corresponding demonstrations are built and run. The output of all demonstrations is directed to sys$output.

The NCOMP JavaVM demonstrations are not currently supported on OpenVMS.

7.5 XDK Demonstrations

Java and C programming language demonstrations are available for XDK. This section describes how to build and run these demonstrations. It contains the following topics:

7.5.1 Java Demonstrations

To build and run the Java demonstrations, use the scripts provided in the subdirectories of the ora_root:[xdk.demo.java] directory. The names of these scripts are in the form of build_run_*.com. When you run these scripts, the corresponding demonstrations are built and run. You must set the default to the specific demonstration program directory before running any particular script. The result of running the demonstrations are directed to sys$output and are also saved in files that have names of the form *.out.

7.5.2 C Programming Language Demonstrations

To build and run the C programming language demonstrations, use the generic scripts provided in the top-level ora_root:[xdk.demo.c] directory:

  • compile_sample.com

    This script compiles a sample C programming language source file and produces an object file, given the name of the script (without the file name extension) as parameter P1.

  • link_sample.com

    This script links a sample demonstration, given the name of the object file produced by the compile_sample.com script as parameter P1.

  • build_sample.com

    This script combines the actions of the compile_sample.com and link_sample.com scripts.

    To run the demonstration, you must run the executable produced by linking the sample. No arguments are required. The expected output is provided under each sample subdirectory with a file name extension of STD.

7.6 JDBC Demonstrations

The JDBC (DBJava) demonstrations are shipped as a Java .jar file demo.jar, which are located in the ORA_ROOT:[JDBC.DEMO] directory. To install the demonstrations, enter the following commands:

$ set default ORA_ROOT:[jdbc.demo]
$ jar xvf demo.jar

  1. After installing demo.jar, read the ORA_ROOT:[JDBC.DEMO]Samples-Readme.txt file.

  2. For any particular example, set the default to the directory where the example resides.

  3. Run the corresponding OpenVMS DCL command file (VDJDS*.COM) based on the first letters of the directory path, followed by the parameters requested demonstration name (file name without the .java file name extension) and connection method (OCI, OCITNS, or Thin). Modify the command file as required.

    For example, to run the (V)MS (D)b(J)ava (D)emo (S)ample (G)eneric SelectExample using OCI, use the following command:

    $ SET DEFAULT ORA_ROOT:[JDBC.DEMO.SAMPLES.GENERIC]$ @ORA_ROOT:[JDBC.DEMO.SAMPLES.GENERIC]VDJDSG.COM SelectExample oci
    
    

    The OpenVMS DCL command files VDJDS*.COM in each JDBC demonstration directory are analogous to their UNIX Makefile and Microsoft Windows RUNDEMO.BAT counterparts.

7.7 Running Oracle Text and Oracle Spatial Demonstrations

The following sections contains information about running the Oracle Text and Oracle Database 10g Spatial demonstrations:

7.7.1 Oracle Text

Refer to the ORA_ROOT:[ctx.sample.api]index.html file and Oracle Text Reference for information about the Oracle Text code samples.

7.7.2 Oracle Spatial

Refer to the ORA_ROOT:[md.doc]readme.txt file for information about the Oracle Database 10g Spatial demonstration. Refer to Oracle Spatial User's Guide and Reference (B10826-01) for information about Oracle Database 10g Spatial.

For the Spatial Motif demonstration, refer to ORA_ROOT:[md.demo.unix.motif]readme.

7.7.2.1 Running the Spatial Demonstration

The following is a sample Spatial run:

$! On OpenVMS at DCL to build SDO Motif demo, run:
$ sqlplus/nolog
SQL> connect MDSYS/MDSYS
SQL> @ORA_ROOT:[MD.ADMIN]SDOWIN.SQL
SQL> @ORA_ROOT:[MD.ADMIN]PRVTWIN.PLB
SQL> @ORA_ROOT:[MD.DEMO.UNIX.MOTIF.SRC.SQL_SCRIPTS]MY_WINDOW.SQL
SQL> @ORA_ROOT:[MD.DEMO.UNIX.MOTIF.SRC.SQL_SCRIPTS]MY_WIN.SQL
SQL> exit
$! OpenVMS Logicals and Symbols already setup for MD_VIEWER, XENVIRONMENT, motifdemo in:
$ @ORA_ROOT:[MD.PORT.VMS.INSTALL]DEMO_MOTIF.COM all

  1. On a workstation, start an X Window emulator. For example, on Microsoft Windows 2000, run the following command:

    W2K/Start/Programs/Hummingbird Connectivity V8.0/Exceed/Exceedright click Exceed/Tools Configuration/Screen Definition/Screen 0/Window Manager=Native
    
    
  2. Find the IP address for the workstation as follows:

    W2K/Start/Programs/Accessories/Command Prompt
    DOS> ipconfig
    IP Address 130.35.158.58
    DOS> exit
    
    $! Back on OpenVMS enter IP address from above:
    $ set display /create /transport=tcpip /node=130.35.158.58
    $ run sys$system:decw$clock  ! verify X Window emulator is running
    $ motifdemo  ! execute SDO Motif Demo
    Enter username: MDSYS
    Enter password: MDSYS
    Is database remote [N]: N
    

7.7.3 Spatial Network Demonstrations

Before running Spatial Network demonstrations, read ORA_ROOT:[MD.DEMO.NETWORK...]README.TXT for each demonstration to be run.

Following are example runs for the PL/SQL, SQL*LoaderLogical, SQL*Loader Spatial, Java, and Network Editor demonstrations.

SDO Network Example PL/SQL Demonstration

To run the SDO Network Example PL/SQL demonstration, run the following at the DCL command prompt:

$ set default ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.PLSQL]$ SQLPLUS SCOTT/TIGER @CREATE_LOGICAL.SQL

SDO Network Example SQL*Loader Logical Demonstration

To run SDO Network Example SQL*Loader Logical demonstration, run the following commands at the DCL command prompt:

$ set default ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.SQLLDR.LOGICAL]$ @ORA_ROOT:[MD.PORT.VMS.INSTALL]LOAD_TEST_NET.COM

SDO Network Example SQL*Loader Spatial Demonstration

To run SDO Network Example SQL*Loader Spatial demonstration on OpenVMS, run the following commands at the DCL command prompt:

$ set default ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.SQLLDR.SPATIAL]$ @ORA_ROOT:[MD.PORT.VMS.INSTALL]LOAD_SPATIAL_NET.COM

SDO Network Example Java Demonstration

To run SDO Network Example Java demonstration on OpenVMS, run the following commands at the DCL command prompt:

$ @ORA_ROOT:[JDBC]JDBC_SETUP_JDK14.COM$ set default ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.JAVA.DATA]$ sqlplus/nologSQL> connect / as sysdbaSQL> create user MDNETWORK identified by MDNETWORK;

The following is for illustrative purposes only. Contact the Security Manager or DBA for information about the correct security settings.

SQL> grant all privileges to MDNETWORK with admin option;
SQL> exit
$ sqlplus mdnetwork/mdnetwork @remove_bi_test.sql
SQL> exit
$ sqlplus mdnetwork/mdnetwork @remove_un_test.sql
SQL> exit
$ imp mdnetwork/mdnetwork FILE=bi_test.dmp TABLES="'BI_TEST_NODE$'"
$ imp mdnetwork/mdnetwork FILE=bi_test.dmp TABLES="'BI_TEST_LINK$'"
$ sqlplus mdnetwork/mdnetwork @bi_test_meta.sql
SQL> exit
$ imp mdnetwork/mdnetwork FILE=un_test.dmp TABLES="'UN_TEST_NODE$'"
$ imp mdnetwork/mdnetwork FILE=un_test.dmp TABLES="'UN_TEST_LINK$'"
$ sqlplus mdnetwork/mdnetwork @un_test_meta.sql
SQL> exit
$ set default ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.JAVA]
$ edit ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.JAVA]LoadAndAnalyze.java

Change host, port, sid using values from the tnsnames.ora file.

$ ! Enter each java command as all one line
$ javac -classpath
.:'f$trnlnm("ORACLE_HOME_UNIX")'/jdbc/lib/classes12.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/lib/xmlparserv2.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdoapi.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdonm.jar LoadAndAnalyze.java
$ java -classpath .:'f$trnlnm("ORACLE_HOME_UNIX")'/jdbc/lib/classes12.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/lib/xmlparserv2.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdoapi.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdonm.jar "LoadAndAnalyze"
$ edit ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.JAVA]CreateAndStore.java

Change host, port, sid using values from the tnsnames.ora file.

$ ! Enter each java command as all one line
$ javac -classpath .:'f$trnlnm("ORACLE_HOME_UNIX")'/jdbc/lib/classes12.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/lib/xmlparserv2.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdoapi.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdonm.jar CreateAndStore.java
$ java -classpath .:'f$trnlnm("ORACLE_HOME_UNIX")'/jdbc/lib/classes12.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/lib/xmlparserv2.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdoapi.jar:'f$trnlnm("ORACLE_HOME_UNIX")'/md/lib/sdonm.jar "CreateAndStore"

SDO Network Editor Demonstration

On OpenVMS at DCL to run SDO Network Editor demonstration:

  1. Prior to running the SDO Network Editor demonstration, set up an X Window emulator.

  2. Load the sample data by running commands similar to the following:

    ORA_ROOT:[MD.DEMO.NETWORK.EXAMPLES.JAVA.DATA] above.$ set default ORA_ROOT:[MD.DEMO.NETWORK.EDITOR]$ @ORA_ROOT:[MD.PORT.VMS.INSTALL]STARTNETWORKEDITOR.COM
    

7.7.4 Spatial Example Demonstrations

For information about running the Spatial example demonstrations, read the ORA_ROOT:[MD.DEMO.EXAMPLES]PARALLEL.DOC file. The following is an example of a Spatial demonstration:

  1. To run SDO Example Scripts demonstration, run the following at the at DCL command prompt:

    $ set default ORA_ROOT:[MD.DEMO.EXAMPLES.SCRIPTS]
    $ sqlplus /nolog
    SQL> connect / as sysdba
    SQL> create user SDO_USR identified by SDO_USR;
    
    

    The following is for illustrative purpose only:


    Note:

    Contact the site Security Manager or DBA for information about the appropriate security settings.

    SQL> grant all privileges to SDO_USR with admin option;
    
    SQL> create Tablespace SDO_DATA DATAFILE 'ORA_DB:SDO_DATA.F' SIZE 50M;
    SQL> connect sdo_usr/sdo_usr
    SQL> @ORA_ROOT:[MD.DEMO.EXAMPLES]PARTITION_POINTS.SQL
    
    
  2. To compile, link, and run SDO Example demonstrations, run the following commands at the DCL command prompt:


    Note:

    Before running the SDO Example demonstrations, set up an X Window emulator as described in Section 7.7.2.1, "Running the Spatial Demonstration".

    $ set default ORA_ROOT:[MD.DEMO.EXAMPLES]
    $ @ORA_ROOT:[MD.PORT.VMS.INSTALL]DEMO_SDO.COM
    $ readgeom := $ORA_ROOT:[MD.DEMO.EXAMPLES]readgeom.exe
    $ readgeom parameter_list
    $ writegeom := $ORA_ROOT:[MD.DEMO.EXAMPLES]writegeom.exe
    $ writegeom parameter_list
    $ run MIGCTL
    $ run MIGOCI
    

7.7.5 Spatial Georaster Demonstrations

Before running the Spatial Georaster demonstrations, read the ORA_ROOT:[MD.DEMO.GEORASTER...]README file for each demonstration to be run. The following is an example of how to run a Spatial Georaster demonstration:

  1. Run the following commands on OpenVMS at DCL to run the SDO Georaster PL/SQL demonstrations:

    $ set default ORA_ROOT:[MD.DEMO.GEORASTER.PLSQL]
    $ sqlplus/nolog @GEORASTER_DEMO.SQL
    SQL> exit
    
    
  2. After all Spatial Georaster demonstrations have been run, enter the following commands to remove the Georaster PL/SQL demonstrations:

    $ sqlplus herman/password @DROP_GEORASTER_TABLE.SQL
    SQL> exit
    
    

To run SDO Georaster Java demonstrations, follow the instructions in the ORA_ROOT:[MD.DEMO.GEORASTER.JAVA]README file. Before running the SDO Georaster Java demonstrations, set up an X Window emulator as described in Section 7.7.2.1, "Running the Spatial Demonstration". The GeoRasterExporter portion of the GeoRaster demo is unsupported.

7.8 SQL*Loader Demonstrations

The following sections describe how to build and run the SQL*Loader demonstrations installed with Oracle Database 10g.

Review the ulcase.sh file for an example of how to run all of the SQL*Loader demonstrations. To run an individual demonstration, read the information contained in the file to determine how to run it.

The following SQL*Loader demonstration files are included with Oracle Database 10g in the ora_rdbms_demo directory. Run the demonstrations in numerical order:

ulcase1
ulcase2
ulcase3
ulcase4
ulcase5
ulcase6
ulcase7

Run demonstrations while logged in as the user SCOTT/TIGER.

Creating and Running a Demonstration


Note:

  • The SCOTT/TIGER user has CONNECT and RESOURCE privileges.

  • The EMP and DEPT tables exist.


In the following steps, n represents the demonstration number, listed in the preceding section. To create and run a demonstration:

  1. Run the ulcasen.sql script corresponding to the demonstration you want to run:

    $ sqlplus SCOTT/TIGER @ulcasen.sql
    
    
  2. Load the demonstration data into the database by running the following command:

    $ sqlldr SCOTT/TIGER ulcasen.ctl 
    
    

    The following list provides additional information about the ulcase2, ulcase6, and ulcase7 demonstrations:

    • For the ulcase2 demonstration, you do not have to run the ulcase2.sql script.

    • For the ulcase6 demonstration, run the ulcase6.sql script, and run the following command:

      $ sqlldr SCOTT/TIGER ulcase6 DIRECT=true
      
      
    • For the ulcase7 demonstration, run the ulcase7s.sql script, and run the following command:

      $ sqlldr SCOTT/TIGER ulcase7.ctl
      
      

      After running the demonstration, run the ulcase7e.sql script to drop the trigger and package used by this demonstration.

7.8.1 Administering SQL*Loader

SQL*Loader is used by both database administrators and Oracle Database 10g users. It loads data from standard operating system files into Oracle Database tables.

The SQL*Loader control file includes the following additional file processing option strings, the default being str, which takes no argument:

[ "str" | "fix n" | "var n" ]

Table 7-1 describes the processing options used in the preceding example:

Table 7-1 SQL*Loader Processing Option String

String Description
str Specifies a stream of records, each terminated by a newline character, which are read in one record at a time

This string is the default.

fix Indicates that the file consists of fixed-length records, each of which is n bytes long, where n is an integer.
var Indicates that the file consists of variable-length records, with the length of each record specified in the first n characters

If you do not specify a value for n, then SQL*Loader assumes a value of 5.


If you do not select the file processing option, then the information is processed by default as a stream of records (str). You may find that fix mode gives better performance than the default str mode because it does not scan for record terminators.

7.8.1.1 Newline Characters in Fixed-Length Records

When using the fix option to read a file containing fixed-length records, where each record is terminated by a newline character, include the length of the newline character (1 character) when specifying the record length to SQL*Loader.

For example, to read the following file, specify fix 4 instead of fix 3 to include the additional newline character:

AAA<cr>
BBB<cr>
CCC<cr>

If you do not terminate the last record in a file of fixed-length records with a newline character, then do not terminate the other records with a newline character. Similarly, if you terminate the last record with a newline character, then terminate all records with a newline character.


Caution:

Certain text editors, such as edt, automatically terminate the last record of a file with a newline character. This leads to inconsistencies if the other records in the file are not terminated with newline characters.

7.8.1.2 Removing Newline Characters

Use the position(x:y) function in the control file to remove newline characters from fixed length records, instead of loading them. For example, enter the following in the control file to remove newline characters from the fourth position:

load data
infile xyz.dat "fix 4"
into table abc
( dept position(01:03) char )

When this is done, newline characters are removed because they are in the fourth position in each fixed-length record.