Use Oracle AQ to enqueue a CLOB from a Pro*C program back to an Oracle stored procedure

43 views Asked by At

I have a working prototype that

  • From an Oracle stored procedure, uses dbms_aq.enqueue to enqueue an object type (with varchar2/number types in the object)
  • I have a Pro*C program that reads the same queue with dbms_aq.dequeue which receives the object and displays the values of the varchar2/number types that were enqueued
  • From the Pro*C program, I then use dbms_aq.enqueue to enqueue a response object (with a varchar2 type)
  • In the previous Oracle stored procedure, I then use dbms_aq.dequeue to dequeue the response message from Pro*C and display the response

I can't figure out how to send the response (enqueue) message from Pro*C to Oracle using CLOB parameters instead of VARCHAR2. Can anyone help? I believe I will have to use OCIClobLocator, and EXEC SQL OBJECT SET but there are very few working examples out there.

Here is my prototype Pro*C Program

main(){
    trace_query_typ     *trace_query = (trace_query_typ*)0;  /* from Oracle */
    trace_response_typ     *trace_response = (trace_response_typ*)0;  /* to Oracle */

    char user[60]="uid/pwd";  /* user logon password */
    char param1[6] = "";  
    int param2 = 0;      
    char response[100] = "RESPONSE";

    /*  Connect to database: */
    EXEC SQL CONNECT :user;  
    
    /* On an oracle error print the error number :*/
    EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error :");

    /* Allocate memory for the host variable from the object cache : */
    EXEC SQL ALLOCATE :trace_query;

    /* Allocate memory for the host variable from the object cache : */
    EXEC SQL ALLOCATE :trace_response;

    /* Dequeue */
    while (1){
        printf("Waiting for message from Oracle on oracle_to_proc_queue\n");
        /* Embedded PLSQL call to the AQ dequeue procedure : */
        EXEC SQL EXECUTE
            DECLARE
                message_properties  dbms_aq.message_properties_t;
                dequeue_options     dbms_aq.dequeue_options_t; 
                msgid               RAW(16);
            BEGIN
                /* Return the payload into the host variable 'trace_query':  */
                dbms_aq.dequeue(queue_name => 'oracle_to_proc_queue',
                message_properties => message_properties,
                dequeue_options => dequeue_options,
                payload => :trace_query,
                msgid => msgid);
            END;
        END-EXEC;

        /* Commit work :*/
        EXEC SQL COMMIT;
                                            
        /* Extract the components of trace_query: */
        EXEC SQL OBJECT GET param1,param2 FROM :trace_query INTO :param1,:param2;
        
        printf("Dequeued Message \n");
        printf("Param1: %s\n",param1);
        printf("Param2: %d\n",param2);
        
        /* now put the message back into another queue to go back to Oracle
        /* ENQUEUE */

        /* Initialize the components of trace_response : */
        EXEC SQL OBJECT SET response OF :trace_response TO :response;
        
        /* Embedded PLSQL call to the AQ enqueue procedure : */
        EXEC SQL EXECUTE
            DECLARE
                message_properties   dbms_aq.message_properties_t;
                enqueue_options      dbms_aq.enqueue_options_t;
                msgid                RAW(16);
            BEGIN
                /* Bind the host variable 'trace_response' to the payload: */
                dbms_aq.enqueue(queue_name => 'proc_to_oracle_queue',
                message_properties => message_properties,
                enqueue_options => enqueue_options,
                payload => :trace_response,
                msgid => msgid);
            END;
        END-EXEC;

        /* Commit work */
        EXEC SQL COMMIT;

        printf("Enqueued Message \n");
    }
}
0

There are 0 answers