Stored procedure

Sql data comes from EDepartment table.

You have to setup Oracle with the following sources (stored procedures):

The experimentations are the following:

How to setup Oracle ? (for newbies only):

 We assume that Oracle is setup on your system, and you have installed the classes12.zip file into the $TOMCAT_HOME/webapps/expershop/WEB-INF/lib directory.
Important: you need to move classes12.zip file into classes12.jar, Tomcat has some problem with .zip files ...

Now you can start Oracle with the following instructions. 
[oracle@toucan oracle]$ svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production

SVRMGR> connect internal;
Connected.
SVRMGR> startup;
ORACLE instance started.
Total System Global Area                         23437296 bytes
Fixed Size                                          69616 bytes
Variable Size                                     6418432 bytes
Database Buffers                                 16777216 bytes
Redo Buffers                                       172032 bytes
Database mounted.
Database opened.
SVRMGR> quit
Server Manager complete.
[oracle@toucan oracle]$ lsnrctl start

LSNRCTL for Linux: Version 8.1.6.0.0 - Production on 12-FEB-2003 11:25:53

(c) Copyright 1998, 1999, Oracle Corporation.  All rights reserved.

Starting /home/oracle/OraHome1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 8.1.6.0.0 - Production
System parameter file is /home/oracle/OraHome1/network/admin/listener.ora
Log messages written to /home/oracle/OraHome1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=2481)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 8.1.6.0.0 - Production
Start Date                12-FEB-2003 11:25:53
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /home/oracle/OraHome1/network/admin/listener.ora
Listener Log File         /home/oracle/OraHome1/network/log/listener.log
Services Summary...
  ExperLOG              has 1 service handler(s)
  PLSExtProc            has 1 service handler(s)
The command completed successfully

How to write a Stored Procedure with Oracle:

You can follow this url on the Oracle web site to understand how to create a stored procedure which return a ResultSet.
Select the name and the deptid from the EDepartment table and put the result in th p_cursor. 
create or replace package types
as
   type cursorType is ref cursor;
end;
/

/******STORED PROCEDURE ON ORACLE DATABASE************/
/*====================================================*/
create or replace procedure name_deptid ( p_cursor in out types.cursorType )
is
begin
       open p_cursor for  select name, deptid from EDepartment;
end;
/*****END OF STORED PROCEDURE****/
/

To enable and compile a stored procedure into Oracle database, you need to insert it (same way you load a sql file), in the following example, the file name is proc3.sql: 
[oracle@toucan /tmp]$ sqlplus login/password

SQL*Plus: Release 8.1.6.0.0 - Production on Wed Feb 12 17:03:20 2003

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


Connected to:
Oracle8i Release 8.1.6.1.0 - Production
JServer Release 8.1.6.0.0 - Production

SQL> @proc3;

Package created.


Procedure created.

SQL>

Important: Oracle SQL/Plus does not appreciate numeric to start procedure:
create or replace procedure 4you ( p_cursor in out types.cursorType )
is
This syntax is not valid.

How to setup that the stored procedure return a ResultSet:

 You need to specify the name of the class Types (OracleTypes) which defines specific types. In the Oracle case, you can add the following line to your ExperSHOP configuration file:

jdbc.ResultSet: oracle.jdbc.driver.OracleTypes

If you use a datasource (instead of the default jdbc.* properties), you can add the following line to your ExperSHOP configuration file:

datasource.<datasource_name>.ResultSet: oracle.jdbc.driver.OracleTypes
where <datasource_name> is the name of the datasource.

How to declare a stored procedure in your template and use it

 Just a simple case, I want to create myprocedure procedure, to return the a set of (name, deptid) from a specific table (EDepartment), 
$DefineSqlProc myprocedure {call name_deptid(?)}
$RegisterOutParameter myprocedure 1 CURSOR
$ExecSqlProc myprocedure
$LoopOnResults myprocedure1 myproc
   [ Name='$myproc:name$' Departement id='$myproc:deptid$' ]<br>
$EndLoop

Syntax

$DefineSqlProc <procedure_name> <procedure_call> [<datasource_name>]
Allow to declare a stored procedure into ExperShop, the procedure has not been yet executed.
<procedure_name> : the name of the stored procedure, this name will be used to add out parameters, execute the procedure, and display results into the template code. This name could be a variable and defined by $\$<aVariable>$\$
<procedure_call> : the call to the stored procedure. This call is dependant from your database system. Be carefull, Oracle prefers to have OUT parameters and then IN parameters. If OUT and IN parameters are mixed, it does not work !!!
<datasource_name> : the name of a datasource (declared in the configuration file of the application). 
$RegisterOutParameter <procedure_name> <parameter_index> <parameter_type>
Allow to register out parameters for the defined stored procedure. For each out parameter in your procedure call, you have to declare it, with its call index and its type.
<procedure_name> : the name of the stored procedure
<parameter_index> : the index of the parameter into the procedure call
<parameter_type> : the type of the parameter defined by its call index 
$ExecSqlProc <procedure_name>
Execute the defined stored procedure
<procedure_name> : the name of the stored procedure

How to read results:

First, if you have specified a CURSOR type for your parameter, you can read results with the $LoopOnResults tag as shown in the previous example. The name of the dataset is built with the name of the stored procedure and the index of the parameter. i.e. If the ResulSet (CURSOR) index is 5 and the name of the stored procedure is thisProcedure, the name of the dataset will be : 
$LoopOnResults thisProcedure5 proc
In the case your results are single elements like String (VARCHAR), you can read results like a variable: 
$myprocedure:1$

Examples:

 The following examples are shown here:

Stored procedure with 4 out parameters, 2 CURSORs and 2 VARCHARs:

 
$DefineSqlProc myprocedure {call fourout(?,?,?,?)}
$RegisterOutParameter myprocedure 1 CURSOR
$RegisterOutParameter myprocedure 2 VARCHAR
$RegisterOutParameter myprocedure 3 VARCHAR
$RegisterOutParameter myprocedure 4 CURSOR

$ExecSqlProc myprocedure

select name into aname from EDepartment where deptid='glasses';
name=$myprocedure:2$
select deptid into deptnum from EDepartment where name='Hardware';
deptid=$myprocedure:3$

open p_cursor for select name, deptid from EDepartment;
$LoopOnResults myprocedure1 myproc
   name=$myproc:name$
   deptid=$myproc:deptid$
$EndLoop

open pall for select * from EDepartment;
$LoopOnResults myprocedure4 myproc
   name=$myproc:name$
   deptid=$myproc:deptid$
   deptimage=$myproc:deptimage$
$EndLoop

Stored procedure with one out parameter (CURSOR) and one in parameter (VARCHAR) which is a DynHTML variable:

$DefVar avar 'glasses'

Execution of a stored procedure with Oracle database:
$DefineSqlProc myprocedure {call outin(?,$avar$)}
$RegisterOutParameter myprocedure 1 CURSOR

$ExecSqlProc myprocedure

open p_cursor for select name, deptid from EDepartment;
$LoopOnResults myprocedure1 myproc
   name=$myproc:name$
   deptid=$myproc:deptid$
   deptimage=$myproc:deptimage$
$EndLoop