Sybernet / Supplied Packages Reference Release 3.00 May 28, 2006

HTTP.OCI

The OCI package provides subprograms for invoking batch commands from external files. This may sound like an odd thing to do because I can already submit external files by myself without doing this from my PL/SQL stored procedure. That is certainly true, but in conjunction with the STDIO package my stored procedure can create this file dynamically and then invoke it with this package without worrying about the size limit of execute immediate.

If only to whet your appetite, consider the stored procedure Sybernet Table Compare. This procedure allows you to compare two tables between two different databases. If the table's DDL is small, this can probably be done with execute immediate, but once the number of columns start to reach 100 or more columns, you can easily reach the maximum string length that can be declared. This procedure instead writes this anonymous block to a disk file that is then invoked by this package.

Constants

OCI defines the following constants:

    OCI_SUCCESS                 CONSTANT NUMBER :=      0; -- maps to SQL_SUCCESS
    OCI_SUCCESS_WITH_INFO       CONSTANT NUMBER :=      1; -- maps to SQL_SUCCESS_WITH_INFO
    OCI_RESERVED_FOR_INT_USE    CONSTANT NUMBER :=    200; -- reserved for internal use
    OCI_NO_DATA                 CONSTANT NUMBER :=    100; -- maps to SQL_NO_DATA
    OCI_ERROR                   CONSTANT NUMBER :=     -1; -- maps to SQL_ERROR
    OCI_INVALID_HANDLE          CONSTANT NUMBER :=     -2; -- maps to SQL_INVALID_HANDLE
    OCI_NEED_DATA               CONSTANT NUMBER :=     99; -- maps to SQL_NEED_DATA
    OCI_STILL_EXECUTING         CONSTANT NUMBER :=  -3123; -- OCI would block error
    OCI_CONTINUE                CONSTANT NUMBER := -24200; -- Continue with the body of the OCI function

Summary of Subprograms

BATCH

EXECUTE

UPLOAD

Subprogram BATCH

This command executes a batch file. This command is different from the subprogram UPLOAD because your batch file may consist of one or more commands separated by either a / (Oracle convention) or go (Sybase convention). Normally, you would just submit such files with SQLPlus.

Syntax

Function BATCH
(
    PATHNAME                      VARCHAR2
)
RETURN BINARY_INTEGER;

Parameters

pathname

Example

The following example illustrates how to call BATCH:

http.oci.batch('/home/public/nightly_upload_batch_file.sql');

Subprogram EXECUTE

This command is similar to execute immediate except that is does not allow a using clause. It also does not raise an exception if your command fails. In this case it is up to you evaluate the return result. If not successful because the result was not OCI_SUCCESS, you can raise your own exception.

Syntax

Function EXECUTE
(
    CMD                           VARCHAR2
)
RETURN BINARY_INTEGER;

Parameters

cmd

Example

The following example illustrates how to call EXECUTE:

declare
    status binary_integer;
begin
    status:=http.oci.execute('UPDATE DUAL SET DUMMY = ''X''');
    if (status = http.oci.oci_success) THEN
        http.writeln('Command successful.');
    else
        http.writeln('Command failed.');
    end if;
end;

Subprogram UPLOAD

This command executes a batch file. This command is different from the subprogram BATCH because the entire file is submitted. Normally, you would just submit such files with SQLPlus.

Syntax

Function UPLOAD
(
    PATHNAME                      VARCHAR2
)
RETURN BINARY_INTEGER;

Parameters

pathname

Example

The following example illustrates how to call UPLOAD:

declare
    filename constant varchar(30) := 'example';
    status binary_integer;
    fildes binary_integer;
	
    procedure print(string in varchar2)
    is
        nbytes binary_integer;
    begin
        nbytes:=http.stdio.write(fildes,string||chr(10));
        return;
    end print;

begin
    fildes:=http.stdio.open(filename,'w');
    print('DECLARE');
    print('    STRING CONSTANT VARCHAR(30) := ''Hello World'';');
    print('BEGIN');
    print('    HTTP.WRITELN(STRING);');
    print('END;');
    status:=http.stdio.close(fildes);
    status:=http.oci.upload(filename);
    status:=http.stdio.unlink(filename);
end;

See Also