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");
}
}