PLSQL 10

From bd_en
Jump to: navigation, search

UTL_TCP

Acest pachet este definit in fisierul utltcp.sql (care este gasit in acelasi fisier in care se afla si utlfile.sql). Pachetul pune la dispozitie functionalitatile protocului TCP/IP pe partea de client prin deschiderea unui socket pentru a comunica cu un server prin orice protocol precum HTTP, SMTP, sau FTP. Cateva obiecte ce ne sunt la dispozitie in acest pachet sunt urmatoarele...

CONNECTION Record Type

This package is defined in the utltcp.sql (which is found in the same file as utlfile.sql). The package provides the TCP/IP functionalities on the client side by opening a socket to communicate with a server through any protocol such as HTTP, SMTP or FTP. Some objects which we can use in this package are...

TYPE connection IS RECORD (
    remote_host    VARCHAR2(255),
    remote_port    PLS_INTEGER,
    local_host     VARCHAR2(255),
    local_port     PLS_INTEGER,
    charset        VARCHAR2(30),
    newline        VARCHAR2(2),
    tx_timeout     PLS_INTEGER,
    private_sd     PLS_INTEGER);
/

  • REMOTE_HOST : this parameter contains the remote host's name for the duration of the connection. This field is NULL if the connection is not estabilished.
  • REMOTE_PORT : this parameter contains the remote host's port for the duration of the connection. This field is NULL if the connection is not estabilished.
  • LOCAL_HOST : this parameter contains the name of the local host used for the connection. This field is NULL if the connection is not estabilished. Currently, this parameter is not supported.
  • LOCAL_PORT : this parameter contains the port of the local host used for the connection. This field is NULL if the connection is not estabilished. Currently, this parameter is not supported.
  • CHARSET : this parameter is used for character conversion.
  • NEWLINE : this parameter contains the newline character sequence.
  • TX_TIMEOUT : this parameter contains the timeout time in seconds before giving up the connection in the attempt time.
  • PRIVATE_SD : this parameter is used for the internal use of the package and must not be manipulated.

OPEN_CONNECTION Function

This function opens a connection to the mentioned web service using the TCP/IP protocol. The connection opened by this function stays open and can be transffered between databases using shared server configuration. These connections must be explicitly closed. Leaving them open leads to undesired ressource usage. The functions prototype is as follows...

UTL_TCP.OPEN_CONNECTION (
   remote_host      IN VARCHAR2,
   remote_port      IN PLS_INTEGER,
   local_host       IN VARCHAR2 DEFAULT NULL,
   local_port       IN PLS_INTEGER DEFAULT NULL,
   in_buffer_size   IN PLS_INTEGER DEFAULT NULL,
   out_buffer_size  IN PLS_INTEGER DEFAULT NULL,
   charset          IN VARCHAR2 DEFAULT NULL,
   newline          IN VARCHAR2 DEFAULT CRLF,
   tx_timeout       IN PLS_INTEGER DEFAULT NULL,
   wallet_path      IN VARCHAR2 DEFAULT NULL,
   wallet_password  IN VARCHAR2 DEFAULT NULL)
 RETURN connection;

  • IN_BUFFER_SIZE : this parameter accepts the size of the input buffer to increase the performance of the execution of the information received from the server
  • OUT_BUFFER_SIZE : this parameter accepts the size of the output buffer to increase the performance of the execution of the information sent to the server
  • WALLET_PATH : this parameter accepts the wallet path for the stored certificates which are necessary to make a request for information from a secure connection. The format is file: <Directory_path>
  • WALLET_PASSWORD : this parameter accepts the password necessary to open the wallet. When the parameter AUTO_LOGIN is enabled, this parameter can be set to NULL.


SECURE_CONNECTION Procedure

This procedure secures the estabilished TCP/IP connection using the SSL/TLS configuration. This requires an Oracle wallet with a valid certificate which must be specified during the connection call using the OPEN_CONNECTION function. The prototype is...

UTL_TCP.SECURE_CONNECTION (c IN OUT NOCOPY connection);

  • C : this parameter accepts and returns the connection details from the server from which we receive information.


AVAILABLE Function

This function returns the number of available bytes for reading from the connected server without blocking after the connection is estabilished. On some platforms, this function can return 1, if there exists information that can be received from the server, or 0, otherwise. The function is used to perform safety checks to verify if the information are available or not, before reading and resource allocation for reading said information. The function prototype is...

UTL_TCP.AVAILABLE (
   c            IN OUT NOCOPY connection,
   timeout      IN PLS_INTEGER DEFAULT 0)
 RETURN PLS_INTEGER;

  • TIMEOUT : this parameter accepts the timeout time in seconds before giving up on a connection during the attempt time


FLUSH Procedure

This procedure flushes all information to the server from the output buffer after estabilishing a connection. The prototype is...

UTL_TCP.FLUSH(c IN OUT NOCOPY connection);


GET_LINE, and GET_LINE_NCHAR Functions

These functions return the row of data from the database. The functions prototypes are...

UTL_TCP.GET_LINE (
   c                IN OUT NOCOPY connection,
   remove_crlf      IN BOOLEAN DEFAULT FALSE,
   peek             IN BOOLEAN DEFAULT FALSE)
 RETURN VARCHAR2;

UTL_TCP.GET_LINE_NCHAR (
   c                IN OUT NOCOPY connection,
   remove_crlf      IN BOOLEAN DEFAULT FALSE,
   peek             IN BOOLEAN DEFAULT FALSE)
 RETURN NVARCHAR2;

  • REMOVE_CRLF : this parameter removes trailing CRLF characters from the received message
  • PEEK : the parameter is set to a boolean value of true when we want to look-ahead at the information without losing it from the queue and it will be available

for reading at a future call. This parameter needs an input buffer to be created before the connection opening. This buffer will hold the information available to peek without conflicting with te information from the next call.


GET_RAW, GET_TEXT, and GET_TEXT_NCHAR Functions

These functions return raw data, text data and, respectively, text data in national character read from the server. The functions prototypes are...

UTL_TCP.GET_RAW (
   c                IN OUT NOCOPY connection,
   len              IN PLS_INTEGER DEFAULT 1,
   peek             IN BOOLEAN     DEFAULT FALSE)
 RETURN RAW;

UTL_TCP.GET_TEXT (
   c                IN OUT NOCOPY connection,
   len              IN PLS_INTEGER DEFAULT 1,
   peek             IN BOOLEAN     DEFAULT FALSE)
 RETURN VARCHAR2;

UTL_TCP.GET_TEXT_NCHAR (
   c                IN OUT NOCOPY connection,
   len              IN PLS_INTEGER DEFAULT 1,
   peek             IN BOOLEAN DEFAULT FALSE)
 RETURN NVARCHAR2;

  • LEN : this parameter accepts the number of bytes of information which will be received


READ_LINE Function

This function receives a line of text from the server in an open connection. A line feed, carriage return or a carriage return followed by a linefeed determines the line separator. The function return the number of characters of data received. The prototype is as follows... .

UTL_TCP.READ_LINE (
   c           IN OUT NOCOPY connection,
   data        IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
   peek        IN BOOLEAN DEFAULT FALSE)
 RETURN PLS_INTEGER;

  • DATA : this parameter accepts and returns the received information


READ_RAW, and READ_TEXT Functions

These function receive binary data, or, respectively, text data from the server with an open connection. The functions return data only if the specified number of bytes is read or if (End Of Input) is reached. The size of the VARCHAR2 buffer should be equal with the number of characters which wll be read, multiplied with the maximum number of bytes of a character from the set of characters from the database. This function returns the number of characters of data which have been received. The functions prototypes are...


UTL_TCP.READ_RAW (
   c                        IN OUT NOCOPY connection,
   data                     IN OUT NOCOPY RAW,
   len                      IN PLS_INTEGER DEFAULT 1,
   peek  IN                 BOOLEAN DEFAULT FALSE)
 RETURN PLS_INTEGER;

UTL_TCP.READ_TEXT (
   c                        IN OUT NOCOPY connection,
   data                     IN OUT NOCOPY VARCHAR2 CHARACTER SET ANY_CS,
   len  IN                  PLS_INTEGER DEFAULT 1,
   peek IN                  BOOLEAN DEFAULT FALSE)
 RETURN PLS_INTEGER;

WRITE_LINE, WRITE_RAW, and WRITE_TEXT Functions

These functions write a line of text, a binary message, and, respectively, a text mesagge to the server in an open connection. The functions return the number of characters of data received after writing the number of bytes specified to the server (in case of WRITE_RAW and WRITE_TEXT). The WRITE_LINE function adds a newline character to the message before sending it. The WRITE_TEXT function converts the information to a on-the-wire character set before sending it. The functions prototypes are...


UTL_TCP.WRITE_LINE (
   c                        IN OUT NOCOPY connection,
   data                     IN VARCHAR2 DEFAULT NULL CHARACTER SET ANY_CS)
 RETURN PLS_INTEGER;

UTL_TCP.WRITE_RAW (
   c                        IN OUT NOCOPY connection,
   data                     IN RAW,
   len                      IN PLS_INTEGER DEFAULT NULL)
 RETURN PLS_INTEGER;

UTL_TCP.WRITE_TEXT (
   c                        IN OUT NOCOPY connection,
   data                     IN VARCHAR2 CHARACTER SET ANY_CS,
   len                      IN PLS_INTEGER DEFAULT NULL)
 RETURN num_chars PLS_INTEGER;


CLOSE_CONNECTION and CLOSE_ALL_CONNECTIONS Procedures

These procedures close a single connection, respectively all connections. A TCP/IP connecton remains open until these functions are called. Not doing this will lead to unwanted resource usage. The procedures prototypes are...


UTL_TCP.CLOSE_CONNECTION (c IN OUT NOCOPY connection);

UTL_TCP.CLOSE_ALL_CONNECTIONS;

Aside from functions and procedures, the UTL_TCP package also offers us some exceptions which can be useful to us.

buffer_too_small               EXCEPTION;  -- Buffer is too small for I/O
end_of_input                   EXCEPTION;  -- End of input from connection
network_error                  EXCEPTION;  -- Network error
bad_argument                   EXCEPTION;  -- Bad argument passed in API call
partial_multibyte_char         EXCEPTION;  -- A partial multi-byte char found
transfer_timeout               EXCEPTION;  -- Transfer time-out occurred
network_access_denied          EXCEPTION;  -- Network access denied

buffer_too_small_errcode       CONSTANT PLS_INTEGER:= -29258;
end_of_input_errcode           CONSTANT PLS_INTEGER:= -29259;
network_error_errcode          CONSTANT PLS_INTEGER:= -29260;
bad_argument_errcode           CONSTANT PLS_INTEGER:= -29261;
partial_multibyte_char_errcode CONSTANT PLS_INTEGER:= -29275;
transfer_timeout_errcode       CONSTANT PLS_INTEGER:= -29276;
network_access_denied_errcode  CONSTANT PLS_INTEGER:= -24247;
 

And now, we will create an iterative server in JAVA, and a client application in PL/SQL.

JAVA CODE

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.ServerSocket;
import java.net.Socket;

public class Main {

    public static void main(String[] args) throws IOException {
        ServerSocket listener = new ServerSocket(8010);
        try {
            while (true) {
                System.out.println("Waiting client...");
                Socket socket = listener.accept();
                System.out.println("Received client...");

                BufferedReader in = new BufferedReader(new InputStreamReader(socket.getInputStream()));
                try {
                    PrintWriter out = new PrintWriter(socket.getOutputStream(), true);

                    String inputLine = in.readLine();
                    if (null != inputLine) {
                        out.println(inputLine.toUpperCase());
                    }
                    out.flush();
                    out.close();
                } finally {
                    socket.close();
                }
            }
        } finally {
            listener.close();
        }
    }
}

PL/SQL CODE

SET SERVEROUTPUT ON
/
CLEAR SCREEN
/
DECLARE
    CONN            UTL_TCP.CONNECTION;
    RETVAL          BINARY_INTEGER;
    L_RESPONSE      VARCHAR2(1000) := '';
    L_TEXT          VARCHAR2(1000);    
BEGIN
    
    --We open the connection...
    CONN := UTL_TCP.OPEN_CONNECTION(
        REMOTE_HOST   => '127.0.0.1',
        REMOTE_PORT   => 8010,
        TX_TIMEOUT    => 10
    );
    
    L_TEXT := 'Hello there...';
    --We write in socket...
    RETVAL := UTL_TCP.WRITE_LINE(CONN,L_TEXT);
    UTL_TCP.FLUSH(CONN);
    
    --We check if we have received something and read the answer from the socket...
    BEGIN
        WHILE UTL_TCP.AVAILABLE(CONN, 10) > 0 LOOP
            L_RESPONSE := L_RESPONSE ||  UTL_TCP.GET_LINE(CONN,TRUE);
        END LOOP;
    EXCEPTION
        WHEN UTL_TCP.END_OF_INPUT THEN
            NULL;
    END;

    DBMS_OUTPUT.PUT_LINE('Response from Socket Server : ' || L_RESPONSE);
    
    --We close the connection because, otherwise, we would be wasting resources...
    UTL_TCP.CLOSE_CONNECTION(CONN);
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20101, SQLERRM);
        UTL_TCP.CLOSE_CONNECTION(CONN);
END;
/

Observation

If it all goes alright, you should see te following message...

Response from Socket Server : HELLO THERE...


PL/SQL procedure successfully completed.
 

If not...

  • Package Error
PLS-00201: identifier 'UTL_TCP' must be declared
PLS-00320: the declaration of the type of this
expression is incomplete or malformed
 

Explanation: The user tries to use the UTL_TCP package, but doesn't have the permission.

Solution: Open SQL PLUS and login as the admin using CONN / AS SYSDBA; Run this command: GRANT EXECUTE ON UTL_TCP TO STUDENT;

  • ACL Error
ORA-24247: network access denied by access control list (ACL)
 

Explanation: From Oracle 11g, Access Control List is introduced. We cand control who has access to packkages through GRANT, but now we can also control what recources the users can use. In the example above, we have access to the UTL_TCP package, but we can't connect because we don't have the permission to use the resources at connect.


Solutie: Deschideti SQL PLUS si intrati pe contul de admin folosind CONN / AS SYSDBA; Rulati scriptul:

 
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'example.xml',
                                    description => 'UTL TCP EXAMPLE',
                                    principal   => 'STUDENT',
                                    is_grant    => true,
                                    privilege   => 'connect');
 
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'example.xml',
                                       principal => 'STUDENT',
                                       is_grant  => true,
                                       privilege => 'resolve');
 
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'example.xml',
                                    host => '127.0.0.1');
END;
/
COMMIT;


Exercices

1. (3pt) HTTP Request

Create a stored procedure which will connect to a web server and will make a asimple HTTP reqest. The function will have as parameters the host name and port. The received result will be printed in the created function. If you can't connect to the host, it will only print 'FAIL'. Test in an anonymus block with a few hosts.


2. (4pt) SMTP Server

Create a stored procedure which will have the role of connecting to a SMTP server. The procedure will receive as arguments, the host of the mail serverand the respective port, with whom it will estabilish a connection. In case it can't connect, the message shown will be 'FAIL'. The created procedure will send a single message to a random e-mail address. The data necessary or the content of the mail are irrelevant and, so, it will be hardcoded. In case of an exception appearing while using the SMTP protocol, it will be announced and the procedure will end it's execution. At the end of the sending, the message shown will be 'MAIL SENT'. As a final check, we will see if the mail was received and read it's contents.

PS  : reread about the SMTP protocol, which was taught at the 'Computer Networks' course

PS2: you can se any program necessary for testing the SMTP server

Bonus (5pt): Whoever manages to login to gmail and send the e-mail from there as 5 bonus points.The e-mail will be sent to the laboratory professor.


3. (3pt) Chit-Chat

Create a client in PL/SQL and an iterative server in JAVA. The server will have to manage a chat between exactly 2 persons. The client is a stored procedure which has as arguments the host adress, the port it will connect to, the inputdirectory and the name of the input file. The respective input file contains the lines which the client must use(in the exact order as in the file, empty lines will be ignored). The way the chat must work is as follows: client 1 reads the line from the file, prints it in the console, transmits it to the server and the server will send it to client 2, which will print it in the console. This way, at the end of the conversation, each client's console will contain the complete dialogue. When a client doesn't have any more lines, it will print only the lines of the other user. Each user has at least one line. The format used is the one from the following model.

Example. Let there be the users ALLEN and MARK, and the input files F1.txt (ALLEN'S)

line1 line3 line5 line7 line9 line11

and F2.txt (MARK'S)

line2 line4 line6

Considering that ALLEN connected first, the 2 of them will have as the following as the output printed:

ALLEN: line1 MARK: line2 ALLEN: line3 MARK: line4 ALLEN: line5 MARK: line6 ALLEN: line7 ALLEN: line9 ALLEN: line11

PS: aside from checking the xml, you will have to add another user with connect privileges to solve the problem (exactly like in the model above)