René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Reading from dbms_pipe with adpoci

The following code can be used to read messages sent with dbms_pipe.
In order to link the programm, adpoci is needed.
The programm can be compiled and linked like so
On Linux with gcc: 
gcc -I$ORACLE_HOME/rdbms/demo -I$ORACLE_HOME/rdbms/public select_table.c -o select_table -L. -L$ORACLE_HOME/lib -ladpoci -lclntsh
On Windows with mingw: 
gcc -Wall -I%ORACLE_HOME%/oci/include read_pipe.c adpoci.dll %ORACLE_HOME%/bin/oci.dll -o read_pipe.exe

Usage

./read_pipe username/password@db queue_name

#include "adpoci.h"

#include <stdio.h>
#include <stdlib.h>

int main(int argc, char* argv[]) {

         char           err_msg   [512];
         sword          r;
  struct oci_connection conn;
         OCIStmt*       sh; 

  if (argc < 3) {
    printf("usage %s username/password[@dbname] msg_name\n", argv[0]);
    exit (-1);
  }

  text  username[30];
  text  password[30];
  text  dbname  [30];

  int   s;
  text  chr     [100];

  strcpy(chr, 
       "          "
       "          "
       "          "
       "          "
       "          "
       "          "
       "          "
       "          "
       "          ");

  text  stmt          [] = 
   "  declare                                    " 
   "    s   integer;                             "
   "    chr varchar2(200);                       "
   "  begin                                      "
   "    chr := '';                               "
   "    s := dbms_pipe.receive_message(:1);      "
   "    if s = 0 then                            "
   "      dbms_pipe.unpack_message(chr);         "
   "    end if;                                  "
   "    :2 := s;                                 "
   "    :3 := chr;                               "
   "  end;                                       ";


  parse_connect_string(argv[1],username, password, dbname);

  if (!oci_connect(username, password, dbname,&conn, err_msg)) {
    printf(err_msg);
    goto clean_up;
  }

  if (! (sh=oci_parse(stmt, err_msg, &conn )))  {
    printf(err_msg);
    goto clean_up;
  }

  printf("Waiting for messages\n");
  for (;;) {
    if (!oci_execute(sh, err_msg, &conn, 3, SQLT_CHR, argv[2], SQLT_INT, &s, SQLT_CHR, chr)) {
       printf(err_msg);
       break;
    }

    if (s != 0)
        printf("Abnormal pipe status: %d\n\r", s);
    else
        printf("%s\n", chr);
  }

clean_up:;
  return 0;
}

Writing into the queue

declare
  status number;
begin
            dbms_pipe.pack_message('hello world');
  status := dbms_pipe.send_message('queue_name');
end;
/