/*********************************************************************************
*                                                                                *
* The gateway_redo package contains the procedures that are used to manipulate   *
* the redo log information processed by Gateways.                                *
*                                                                                *
* Note: there will only be one message buffer used to retrieve information from  *
*       the cursor.                                                              *
*                                                                                *
*********************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE gate_redo 

CREATE OR REPLACE PACKAGE gate_redo AS
  error_message           VARCHAR2(512)       DEFAULT NULL;
  error_number            NUMBER              DEFAULT 0;

  CURSOR get_message_cur (p_buffer_identifier gateway_redo.buffer_identifier%TYPE) IS
    SELECT message_id, message_part1, message_part2
    FROM   gateway_redo
    WHERE  buffer_identifier = p_buffer_identifier
    ORDER BY message_id;

  get_message_rec get_message_cur%ROWTYPE;

PROCEDURE PutMessageInBufferSP (
  p_buffer_identifier  IN      gateway_redo.buffer_identifier%TYPE,
  p_message_part1      IN      gateway_redo.message_part1%TYPE,
  p_message_part2      IN      gateway_redo.message_part2%TYPE,
  p_message_id	       OUT     gateway_redo.message_id%TYPE,
  p_retcode            OUT     error_number%TYPE
);

PROCEDURE GetMessageFromBufferSP (
  p_buffer_identifier  IN      gateway_redo.buffer_identifier%TYPE,
  p_message_part1      OUT     gateway_redo.message_part1%TYPE,
  p_message_part2      OUT     gateway_redo.message_part2%TYPE,
  p_message_id	       OUT     gateway_redo.message_id%TYPE,
  p_retcode            OUT     error_number%TYPE
);

PROCEDURE DeleteMessageFromBufferSP (
  p_message_id	       IN      gateway_redo.message_id%TYPE,
  p_retcode            OUT     error_number%TYPE
);

PROCEDURE PurgeMessageBufferSP (
  p_buffer_identifier  IN      gateway_redo.buffer_identifier%TYPE,
  p_retcode            OUT     error_number%TYPE
);

END gate_redo;
/

PROMPT CREATE OR REPLACE PACKAGE BODY gate_redo 

CREATE OR REPLACE PACKAGE BODY gate_redo AS

        oracle_error_message    error_message%TYPE      DEFAULT NULL;
        oracle_error_number     error_number%TYPE       DEFAULT 0;

/*********************************************************************************
*                                                                                *
* This procedure will write a gateway message to the gateway_redo table.         *
*                                                                                *
*********************************************************************************/
PROCEDURE PutMessageInBufferSP (
  p_buffer_identifier  IN      gateway_redo.buffer_identifier%TYPE,
  p_message_part1      IN      gateway_redo.message_part1%TYPE,
  p_message_part2      IN      gateway_redo.message_part2%TYPE,
  p_message_id	       OUT     gateway_redo.message_id%TYPE,
  p_retcode            OUT     error_number%TYPE ) AS


BEGIN
  p_retcode := 0;
  p_message_id := 0;

  INSERT INTO gateway_redo (buffer_identifier, message_id, message_part1, message_part2)
         VALUES ( p_buffer_identifier,
		  gateway_redo_seq.NEXTVAL,
		  p_message_part1,
		  p_message_part2 );

  SELECT gateway_redo_seq.CURRVAL INTO p_message_id FROM DUAL;

  EXCEPTION
    WHEN OTHERS THEN
	RAISE;

END PutMessageInBufferSP;

/*********************************************************************************
*                                                                                *
* This procedure will select the first message from the gateway_redo table that  *
* is associated with the specified redo buffer identifier.                       *
*                                                                                *
*********************************************************************************/
PROCEDURE GetMessageFromBufferSP (
  p_buffer_identifier  IN      gateway_redo.buffer_identifier%TYPE,
  p_message_part1      OUT     gateway_redo.message_part1%TYPE,
  p_message_part2      OUT     gateway_redo.message_part2%TYPE,
  p_message_id	       OUT     gateway_redo.message_id%TYPE,
  p_retcode            OUT     error_number%TYPE ) AS


BEGIN

  p_retcode := 0;

  IF NOT get_message_cur%ISOPEN
  THEN
		OPEN get_message_cur (p_buffer_identifier);
  END IF;

  FETCH get_message_cur INTO get_message_rec;

  IF get_message_cur%FOUND
  THEN
  	  p_message_id := get_message_rec.message_id;
  	  p_message_part1 := get_message_rec.message_part1;
  	  p_message_part2 := get_message_rec.message_part2;
  ELSE
  	  /* No messages left in redo log */

		p_retcode := -20135;
  		CLOSE get_message_cur;
  END IF;

  EXCEPTION
	WHEN OTHERS THEN
		 RAISE;

END GetMessageFromBufferSP;

/*********************************************************************************
*                                                                                *
* This procedure will delete the first message from the gateway_redo table that  *
* is associated with the specified redo buffer identifier.                       *
*                                                                                *
*********************************************************************************/
PROCEDURE DeleteMessageFromBufferSP (
  p_message_id	       IN      gateway_redo.message_id%TYPE,
  p_retcode            OUT     error_number%TYPE ) AS

BEGIN

  p_retcode := 0;

  DELETE FROM gateway_redo
	 WHERE message_id = p_message_id;

  IF SQL%ROWCOUNT = 0
  THEN
  	  p_retcode := -20134;
  END IF;

  /* Add error exception when we have agreed an error code */

  EXCEPTION
    WHEN OTHERS THEN
		 p_retcode := SQLCODE;
		 RAISE;

END DeleteMessageFromBufferSP;

/*********************************************************************************
*                                                                                *
* This procedure will delete all rows from the gateway_redo table associated     *
* with the specified redo buffer identifier.                                     *
*                                                                                *
*********************************************************************************/
PROCEDURE PurgeMessageBufferSP (
  p_buffer_identifier  IN      gateway_redo.buffer_identifier%TYPE,
  p_retcode            OUT     error_number%TYPE ) AS

BEGIN

  p_retcode := 0;

  DELETE FROM gateway_redo
	 WHERE buffer_identifier = p_buffer_identifier;

  IF get_message_cur%ISOPEN
  THEN
  	  CLOSE get_message_cur;
  END IF;

  EXCEPTION
    WHEN OTHERS THEN
	RAISE;

END PurgeMessageBufferSP;

END gate_redo;
/


