Subject:  Oracle8 Objects PL/SQL External Procedures Hands-On Training Module 
Type:  BULLETIN 
Status:  PUBLISHED 
 Content Type:  TEXT/PLAIN 
Creation Date:  12-NOV-1998 

          Oracle 8.0.X Objects PL/SQL External Procedures Hands-On 
                   Training Module for Sun Solaris


Overview
--------

This article contains an example that creates an external procedure in 
PL/SQL named getvalp.  It also creates an external function in PL/SQL 
named getval.  Both of these are used in the cnormalize method of object 
type rational_type.  

The following is a list of necessary files and a description of each:

   runall       - UNIX script which must be run in order to compile 
                  the external procedures, start the callout listener, 
                  create the library, compile the PL/SQL wrapper procedure, 
                  and run the external procedure

   getval.c     - C getval function

   getvalp.c    - C getval procedure

   listener.ora - listener.ora file for the callout listener

   tnsnames.ora - tnsnames.ora file for the callout listener

   sqlnet.ora   - sqlnet.ora file for the callout listener

   getval.sql   - Creates the PL/SQL library and the PL/SQL wrapper      
                  procedures

   rungetval.sql- Creates the rational_type object type which uses the
                  getval and getvalp external procedures and then executes
                  the cnormalize method in rational_type

   runall.lst   - Output of the runall script which should be similar to 
                  your output


Procedure
---------

The following are the steps necessary to run an external procedure by 
invoking an object type method (cnormalize):


1. Change /u02/home/usupport/nliu/o8/modj to /yourcurrentdirectory
   in each of the following files:  

      - runall
      - getval.sql 
      - listener.ora

2. Change the userid nliu/nliu to youruser/yourpass in the runall file.

3. Change to the Oracle 8.0.4 database.

4. Run the runall script:

      % source runall

5. Compare the output you get with the runall.lst file.

 
runall      
======

# This tests callout functions and procedures
echo cc -c getval.c
cc -c getval.c
echo cc -c getvalp.c
cc -c getvalp.c
echo ld -G -h gv_clibs.so -o gv_clibs.so getval.o getvalp.o 
ld -G -h gv_clibs.so -o gv_clibs.so getval.o getvalp.o 
setenv TNS_ADMIN /u02/home/usupport/nliu/o8/modj 
$ORACLE_HOME/bin/lsnrctl stop callout_listener
$ORACLE_HOME/bin/lsnrctl start callout_listener
sqlplus nliu/nliu @getval
sqlplus nliu/nliu @rungetval


getval.c    
========

int cgetval(int pval, short *retind) {

        *retind=0;
        return(pval);

}


getvalp.c  
=========

void cgetvalp(int pval ,int *p2val) {

       *p2val=pval;
       return;

}


listener.ora 
============

CALLOUT_LISTENER =
 (ADDRESS_LIST =
  (ADDRESS =
   (PROTOCOL = ipc)
   (KEY = callout)))

log_directory_callout_listener = /u02/home/usupport/nliu/o8/modj  
trc_directory_callout_listener = /u02/home/usupport/nliu/o8/modj 

TRACE_LEVEL_CALLOUT_LISTENER = OFF   

SID_LIST_CALLOUT_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = callout)
   (ORACLE_HOME = /u02/app/oracle/product/8.0.4)
   ( PROGRAM = /u02/app/oracle/product/8.0.4/bin/extproc )
  )
)


tnsnames.ora 
============

extproc_connection_data =
 (DESCRIPTION=
  (ADDRESS=
   (PROTOCOL=ipc)
   (KEY=callout))
  (CONNECT_DATA=
  (SID=callout)))


sqlnet.ora   
==========

automatic_ipc = off

TRACE_LEVEL_CLIENT = ON
SQLNET.EXPIRE_TIME = 0


getval.sql   
==========

set echo on;

create or replace library GV_CLIBS is '/u02/home/usupport/nliu/o8/modj/gv_clibs.so';
/


drop function getval;
drop function getvalp;

create or replace function getval(
  p1 in binary_integer)
  return binary_integer is external
  name "cgetval"
  library "GV_CLIBS"
  parameters (
  p1 int,
  return indicator short);
/

create or replace procedure getvalp(
  p1 in binary_integer, p2 out binary_integer)
  is external
  name "cgetvalp"
  library "GV_CLIBS"
  parameters (
  p1 int,
  p2 int
  );
/
show errors;
exit;
    

rungetval.sql
=============

set echo on;
set serveroutput on;
drop table rat;
drop type body rational_type;
drop type rational_type;

create or replace type rational_type as object (
  numerator integer, denominator integer,
  member function normalize return integer,
     pragma restrict_references(normalize,wnds,wnps,rnds,rnps),
  member function cnormalize return integer);
/
show errors;


create or replace type body rational_type as
  member function normalize return integer is
    gcd integer := 4;
  begin
  return gcd;
  end;
  member function cnormalize return integer is
    pp number;
    pp2 number;
    gcd number;
  begin
  gcd := 1;
  pp  := 10;
  dbms_output.put_line('During call to getval, The value of gcd is '||gcd);
  gcd := getval(pp);
  dbms_output.put_line('During call to getval, The value of gcd is '||gcd);
  pp := 5;
  dbms_output.put_line('During call to getvalp, The value of pp is '||pp);
  getvalp(pp, pp2);
  dbms_output.put_line('During call to getvalp, The value of pp2 is '||pp2);
  return gcd;
end;
end;
/
show errors;

create table rat (
  ratid integer,
  ratnum rational_type);
insert into rat values (1, rational_type(3,5));

create or replace procedure test (no in integer ) as
x number;
z rational_type;
begin
   select ratnum into z from rat where ratid = 1;
   x:=no;
   dbms_output.put_line('Before call to cnormalize, The value of x is '||x);
   x:=z.cnormalize();
   dbms_output.put_line('After  call to cnormalize, The value of x is '||x);
end;
/
show errors;

exec test(1);


exit;


runall.lst
==========

/home/usupport/nliu/modj> source runall
cc -c getval.c
cc -c getvalp.c
ld -G -h gv_clibs.so -o gv_clibs.so getval.o getvalp.o

LSNRCTL for Solaris: Version 3.0.2.0.0 - Beta on 21-MAY-97 14:36:05

Copyright (c) Oracle Corporation 1994.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=callout))
TNS-12224: TNS:no listener
 TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
   TNS-00511: No listener
    Solaris Error: 2: No such file or directory

LSNRCTL for Solaris: Version 3.0.2.0.0 - Beta on 21-MAY-97 14:36:08

Copyright (c) Oracle Corporation 1994.  All rights reserved.

Starting /u08/app/oracle/product/8.0.2/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 3.0.2.0.0 - Beta
System parameter file is /home/usupport/nliu/modj/listener.ora
Log messages written to /home/usupport/nliu/modj/callout_listener.log
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=12)(KEY=callout))

Connecting to (ADDRESS=(PROTOCOL=ipc)(KEY=callout))
STATUS of the LISTENER
------------------------
Alias                     callout_listener
Version                   TNSLSNR for Solaris: Version 3.0.2.0.0 - Beta
Start Date                21-MAY-97 14:36:11
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /home/usupport/nliu/modj/listener.ora
Listener Log File         /home/usupport/nliu/modj/callout_listener.log
Services Summary...
  callout               has 1 service handler(s)
The command completed successfully

SQL*Plus: Release 4.0.2.0.0 - Beta on Wed May 21 14:36:12 1997

Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.


Connected to:
Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta

SQL>
SQL> create or replace library GV_CLIBS is '/home/usupport/nliu/modj/gv_clibs.so
';

Library created.

SQL> drop function getval;

Function dropped.

SQL> drop function getvalp;
drop function getvalp
*
ERROR at line 1:
ORA-04043: object GETVALP does not exist


SQL>
SQL> create or replace function getval(
  2    p1 in binary_integer)
  3    return binary_integer is external
  4    name "cgetval"
  5    library "GV_CLIBS"
  6    parameters (
  7    p1 int,
  8    return indicator short);
  9  /

Function created.

SQL>
SQL> create or replace procedure getvalp(
  2    p1 in binary_integer, p2 out binary_integer)
  3    is external
  4    name "cgetvalp"
  5    library "GV_CLIBS"
  6    parameters (
  7    p1 int,
  8    p2 int
  9    );
 10  /

Procedure created.

SQL> show errors;
No errors.
SQL> exit;
Disconnected from Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta

SQL*Plus: Release 4.0.2.0.0 - Beta on Wed May 21 14:36:12 1997

Copyright (c) Oracle Corporation 1979, 1994, 1996.  All rights reserved.


Connected to:
Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta

SQL> set serveroutput on;
SQL> drop table rat;

Table dropped.

SQL> drop type body rational_type;

Type body dropped.

SQL> drop type rational_type;

Type dropped.

SQL>
SQL> create or replace type rational_type (
  2    numerator integer, denominator integer,
  3    member function normalize return integer,
  4       pragma restrict_references(normalize,wnds,wnps,rnds,rnps),
  5    member function cnormalize return integer);

Type created.

SQL> show errors;
No errors.
SQL>
SQL>
SQL> create or replace type body rational_type (
  2    member function normalize return integer is
  3      gcd integer := 4;
  4    begin
  5    return gcd;
  6    end;
  7    member function cnormalize return integer is
  8      pp number;
  9      pp2 number;
 10      gcd number;
 11    begin
 12    gcd := 1;
 13    pp  := 10;
 14    dbms_output.put_line('During call to getval, The value of gcd is '||gcd);
 15    gcd := getval(pp);
 16    dbms_output.put_line('During call to getval, The value of gcd is '||gcd);
 17    pp := 5;
 18    dbms_output.put_line('During call to getvalp, The value of pp is '||pp);
 19    getvalp(pp, pp2);
 20    dbms_output.put_line('During call to getvalp, The value of pp2 is '||pp2);
 21    return gcd;
 22  end;);
 23  /

Type body created.

SQL> show errors;
No errors.
SQL>
SQL> create table rat (
  2    ratid integer,
  3    ratnum rational_type);

Table created.

SQL> insert into rat values (1, rational_type(3,5));

1 row created.

SQL>
SQL> create or replace procedure test (no in integer ) as
  2  x number;
  3  z rational_type;
  4  begin
  5     select ratnum into z from rat where ratid = 1;
  6     x:=no;
  7     dbms_output.put_line('Before call to cnormalize, The value of x is '||x)
;
  8     x:=z.cnormalize();
  9     dbms_output.put_line('After  call to cnormalize, The value of x is '||x)
;
 10  end;
 11  /

Procedure created.

SQL> show errors;
No errors.
SQL>
SQL> exec test(1);
Before call to cnormalize, The value of x is 1
During call to getval, The value of gcd is 1
During call to getval, The value of gcd is 10
During call to getvalp, The value of pp is 5
During call to getvalp, The value of pp2 is 5
After  call to cnormalize, The value of x is 10

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exit;
Disconnected from Oracle8 Server Release 8.0.2.0.0 - Beta
With the distributed, heterogeneous, replication, objects,
parallel query and Spatial Data options
PL/SQL Release 3.0.2.0.0 - Beta