7 SQL*Loader and PL/SQL Demonstrations

7.1 SQL*Loader Demonstrations

Run the ulcase.sh file to run the SQL*Loader demonstrations. To run an individual demonstration, read the information contained in the file to determine how to run it.

7.2 PL/SQL Demonstrations

PL/SQL includes a number of demonstration programs. You must build database objects and load sample data before using these programs. To build the objects and load the sample data:

1. Change directory to the PL/SQL demonstrations directory:

   $ cd $ORACLE_HOME/plsql/demo
   

2. Start SQL*Plus, and connect as JONES/password:

   $ sqlplus JONES/password
   

3. Run the following commands to build the objects and load the sample data:

   SQL> @exampbld.sql
   SQL> @examplod.sql
   

   Note:

   Build the demonstrations as any Oracle user with sufficient privileges. Run the demonstrations as the same Oracle user.

PL/SQL Kernel Demonstrations

The following PL/SQL kernel demonstrations are available with the software:

• examp1.sql to examp8.sql

• examp11.sql to examp14.sql

• sample1.sql to sample4.sql

• extproc.sql

To compile and run the exampn.sql or samplen.sql PL/SQL kernel demonstrations:

1. Start SQL*Plus, and connect as JONES/password:

   $ cd $ORACLE_HOME/plsql/demo
   $ sqlplus JONES/password
   

2. Run a command similar to the following to run a demonstration, where demo_name is the name of the demonstration:

   SQL> @demo_name
   

To run the extproc.sql demonstration:

1. If required, add an entry for external procedures to the tnsnames.ora file, similar to the following:

   EXTPROC_CONNECTION_DATA =
      (DESCRIPTION =
          (ADDRESS_LIST = 
             (ADDRESS=(PROTOCOL = IPC)( KEY = EXTPROC))
          )
          (CONNECT_DATA = 
             (SID = PLSExtProc)
          )
       )
   

2. If required, add an entry for external procedures to the listener.ora file, similar to the following:

   Note:

   The value that you specify for SID_NAME in the listener.ora file must match the value that you specify for SID in the tnsnames.ora file.

   • On HP-UX, Linux, Solaris, and Tru64 UNIX:

     SID_LIST_LISTENER = 
       (SID_LIST = 
          (SID_DESC=
             (SID_NAME=PLSExtProc)
             (ORACLE_HOME=oracle_home_path)
             (ENVS=EXTPROC_DLLS=oracle_home_path/plsql/demo/extproc.so,
                LD_LIBRARY_PATH=oracle_home_path/plsql/demo)
             (PROGRAM=extproc)
           )
         )
     

   • On AIX:

     SID_LIST_LISTENER = 
       (SID_LIST = 
          (SID_DESC=
             (SID_NAME=PLSExtProc)
             (ORACLE_HOME=oracle_home_path)
             (ENVS=EXTPROC_DLLS=oracle_home_path/plsql/demo/extproc.so,
                LIBPATH=oracle_home_path/plsql/demo)
             (PROGRAM=extproc)
           )
         )
     

   • On Mac OS X:

     SID_LIST_LISTENER = 
       (SID_LIST = 
          (SID_DESC=
             (SID_NAME=PLSExtProc)
             (ORACLE_HOME=oracle_home_path)
             (ENVS=EXTPROC_DLLS=oracle_home_path/plsql/demo/extproc.dylib,
                DYLD_LIBRARY_PATH=oracle_home_path/plsql/demo)
             (PROGRAM=extproc)
           )
         )
     

3. Change directory to $ORACLE_HOME/plsql/demo.

4. Run the following command to create the extproc.so shared library, build the required database objects, and load the sample data:

   Note:

   On Mac OS X systems, use the shared library name extproc.dylib in the following examples.

   $ make -f demo_plsql.mk extproc.so exampbld examplod
   

   Alternatively, if you have already built the database objects and loaded the sample data, then run the following command:

   $ make -f demo_plsql.mk extproc.so
   

5. From SQL*Plus, run the following commands:

   SQL> CONNECT SYSTEM/SYSTEM_password
   SQL> GRANT CREATE LIBRARY TO JONES;
   SQL> CONNECT JONES/password
   SQL> CREATE OR REPLACE LIBRARY demolib IS
     2  'oracle_home_path/plsql/demo/extproc.so';
     3  /
   

6. To start the demonstration, run the following command:

   SQL> @extproc
   

PL/SQL Precompiler Demonstrations

The following precompiler demonstrations are available:

• examp9.pc

• examp10.pc

• sample5.pc

• sample6.pc

To build the PL/SQL precompiler demonstrations, set the library path environment variable to include the $ORACLE_HOME/lib directory, and run the following commands:

$ cd $ORACLE_HOME/plsql/demo
$ make -f demo_plsql.mk demos

To build a single demonstration, run its name as the argument in the make command. For example, to build the examp9 demonstration, run the following command:

$ make -f demo_plsql.mk examp9

To start the examp9 demonstration, run the following command:

$ ./examp9

7.3 Calling 32-Bit External Procedures from PL/SQL

The 64-bit external procedure executable (extproc) and the 32-bit external procedure executable (extproc32) are installed in the $ORACLE_HOME/bin directory. By default, the extproc executable is enabled to run 64-bit external procedures on AIX, HP-UX, Solaris SPARC, and zSeries Linux systems. To enable 32-bit external procedures:

1. Set the value of the PROGRAM parameter in the listener.ora file as follows:

   (PROGRAM=extproc32)
   

2. Include the $ORACLE_HOME/lib32 directory in one of the following environment variables, depending on your platform:

   Platform	Environment Variable
   AIX	LIBPATH
   HP-UX	SHLIB_PATH
   Linux on POWER, zSeries Linux, and Solaris	LD_LIBRARY_PATH
   Apple Mac OS X (Intel)	DYLD_LIBRARY_PATH

   
   

3. Restart the listener.

   Note:

   You can configure a listener to run either 32-bit or 64-bit external procedures, but not both at the same time. However, you can configure two listeners if you have to support both 32-bit and 64-bit external procedures.