1. Developer's Guide
  2. Oracle Examples

A Oracle Examples

The following examples demonstrate the Oracle functions that are run through the SQL*Plus client utility. These samples cover the general methods of function invocation. The Oracle login user requires the appropriate permission on the function invoked; normally, the login user is the owner of the object (for example, the SARM database user).

Example 1

A function is invoked with no input or output arguments.

SQL> var retval number;
SQL> exec :retval := SSP_del_csdl_defn

Example 2

A function is invoked with several input arguments which are specified positionally. Note that empty strings are denoted as a <space> character if the parameter is required. If the parameter is optional, the null string can be denoted by two consecutive single quotes without a <space> character.

SQL> var retval number;
SQL> exec :retval := SSP_new_csdl_defn('M-CREATE_BUS_LINE', 'Y', 82, ' ', ' ', ' ');

Example 3

This example illustrates how to add or delete information from a database. The following example clears out and then adds several rows into tbl_csdl_config.

SQL> set serveroutput on
SQL> var retval number

SQL> prompt Removing CSDL Definitions from the SARM

SQL> exec :retval := SSP_del_csdl_defn;

SQL> prompt Adding CSDL Definitions to the SARM

SQL> exec :retval := SSP_new_csdl_defn ('C_NEW_FLAT_LINE', 'Y', 60, '', '', 'Add flat-rate line');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_CIDB', 'Y', 65, '', '', 'Add Always-on CID Block');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_DNY_IC', 'Y', 65, '', '', 'Deny incoming calls');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_DNY_TOLL', 'Y', 65, '', '', 'Deny toll calls');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_CWT', 'Y', 80, '', '', 'Add Call Waiting');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_CID', 'Y', 80, '', '', 'Add Caller ID');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_ACB', 'Y', 80, '', '', 'Add Repeat Dial--*66');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_AR', 'Y', 80, '', '', 'Add Return Call--*69');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_SCS', 'Y', 80, '', '', 'Add Speed Call Short');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_3WC', 'Y', 80, '', '', 'Add 3-Way calling');

SQL> exec :retval := SSP_new_csdl_defn ('C_ADD_CFW', 'Y', 80, '', '', 'Add Call Forward');

Example 4

A function is invoked with input arguments that are bound by parameter name. Optional arguments are not passed. The order of the arguments is not relevant when binding by parameter name.

SQL> var retval number;
SQL> exec :retval := SSP_new_csdl_defn(csdl_cmd=>'M-CREATE_BUS_LINE', csdl_level=>82,rollback_req=>'Y');

Example 5

All the previous examples may be run within a PL/SQL block, as shown here:

SQL> declare retval number;
2> begin
3> retval := SSP_new_csdl_defn('M-CREATE_BUS_LINE', 'Y', 82, ' ', ' ', ' ');
4> end;
5> /

Example 6

A function with a cursor result set is invoked. This case is the most complex since the cursor must be processed before SQL*Plus can view the result set. You require the definition of the return cursor, defined in the database package object (in this example, SarmPkg). The results processing is performed within a PL/SQL block.

SQL> set serveroutput on
SQL> declare
  2     retcode integer;
  3     rc1     SarmPkg.SSP_list_csdl_defn_1;
  4     cur_rc1 SarmPkg.SSP_list_csdl_defn_rt1;
  5     csdl    varchar2(25) := '&csdl';
  6  begin
  7     retcode := SSP_list_csdl_defn ( rc1, csdl );
  8     dbms_output.put_line('Return code: ' || retcode);
  9     if rc1%isopen then
 10        loop
 11           fetch rc1 into cur_rc1;
 12           exit when rc1%notfound;
 13           dbms_output.put_line('csdl_cmd = '||cur_rc1.csdl_cmd);
 14           dbms_output.put_line('rollback_req = '||cur_rc1.rollback_req);
 15           dbms_output.put_line('csdl_level = '||cur_rc1.csdl_level);
 16           dbms_output.put_line('fail_event = '||cur_rc1.fail_event);
 17           dbms_output.put_line('complete_event = '||cur_rc1.complete_event);
 18           dbms_output.put_line('description = '||cur_rc1.description);
 19        end loop;
 20        close rc1;
 21     end if;
 22  end;
 23  /

Example 7

In the following example, a Korn shell wrapper enables you to set values before running the script. The following example sets the diag level for all of the servers in one place. 

# CTRL Tables:  tbl_appl_proc
#  tbl_component
#
#        File:  svr_cfg
#
#     Purpose:  To define the ASAP servers, and allow use of the 
#  Class A start scripts.
#
# Stored Procedure Parameter Format:
# CSP_del_appl:
#    *Application Code (Server Name)
# CSP_new_appl:
#     Start Sequence
#     Server Type (M-Master Control, S-Server, C-Client)
#     Application Code (Server Name)
#     Control Server
#     Auto-Start (Y/N)
#     Program (name of executable in $PROGRAMS)
#     Diagnostic level
#     Diagnostic file name
#     Description of server
#    *Server type (ADM, CTRL, MASTER, SARM, OTHER)
# CSP_del_component:
#    *ASAP Territory
#    *ASAP System
#    *Server Name
# CSP_new_component:
#     ASAP Territory
#     ASAP System
#     Server Name
#     
# * indicates an optional parameter
#
#

scr=$(whence $0)

# Get database password
CTRL_PASSWORD=$(GetPassword $CTRL_USER 2)

# Define Local Servers
LOC_SRPC=SRPC$ASAP_ENV

# Define default diagnostic level
DIAG_LEVEL=LOW


sqlplus -s $CTRL_USER/$CTRL_PASSWORD <<HERE | grep -v "successfully completed"
set serveroutput on
var retval number


prompt Removing the ASAP Applications

exec :retval := CSP_del_appl;


prompt Defining the ASAP Applications in Territory $ASAP_TERRITORY, Local System $ASAP_SYSTEM

exec :retval := CSP_new_appl (1, 'M', '$CTRL', '$CTRL', 'N', 'ctrl_svr', '$DIAG_LEVEL', '${CTRL}.diag', 'Master Control Server', 'MASTER');
exec :retval := CSP_new_appl (2, 'S', '$SARM', '$CTRL', 'Y', 'sarm', '$DIAG_LEVEL', '${SARM}.diag', 'SARM Server','SARM');
exec :retval := CSP_new_appl (3, 'S', '$SRP', '$CTRL',  'Y', 'srp_emul', '$DIAG_LEVEL', '${SRP}.diag', 'SRP Emulator','SRP');
exec :retval := CSP_new_appl (5, 'S', '$NEP', '$CTRL',  'Y', 'asc_nep', '$DIAG_LEVEL', '${NEP}.diag', 'NEP Server','NEP');
exec :retval := CSP_new_appl (10, 'C', '$DAM', '$CTRL',  'N', 'run_asapd', '$DIAG_LEVEL', '${DAM}.diag', 'ASAP Daemon','daem');
exec :retval := CSP_new_appl (13, 'C', '$JSRP', '$CTRL', 'N', 'srp.ear', '$DIAG_LEVEL', '${JSRP}.diag', 'Java SRP Server','SRP');
exec :retval := CSP_new_appl (14, 'C', '$LOC_SRPC', '$CTRL', 'Y', 'runSrpClient', '$DIAG_LEVEL', '${LOC_SRPC}.diag', 'Java SRP Client','SRP');


prompt Removing the ASAP Components

exec :retval := CSP_del_component;


prompt Defining the ASAP Components for Territory $ASAP_TERRITORY, Local System $ASAP_SYSTEM

exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$CTRL');
exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$SARM');
exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$ADM');
exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$NEP');
exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$DAM');
exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$JSRP');
exec :retval := CSP_new_component ('$ASAP_TERRITORY', '$ASAP_SYSTEM', '$LOC_SRPC');


HERE