Support AQ #186

sinys15 · 2019-07-31T05:05:35Z

Hi,
Could you please provide some functions for comfort work with Advanced Queue in Go?
I'm interested in UDT (User Defined Type) and SYS.AQ$_JMS_TEXT_MESSAGE (for move Java project to Go) data types.

tgulacsi · 2019-07-31T05:43:45Z

https://docs.oracle.com/cd/B14117_01/server.101/b10785/jm_exmpl.htm shows it with pl/sql. Show what you have, and I will try to help. On Wed, Jul 31, 2019 at 7:05 AM +0200, "sinys15" <[email protected]> wrote: Could you please provide some functions for comfort work with Advanced Queue in Go? I'm interested in UDT and SYS.AQ$_JMS_TEXT_MESSAGE (for move Java project to Go) data types. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

cjbj · 2019-07-31T05:58:41Z

@sinys15 There is an example of PL/SQL usage in the context of node-oracledb at: https://github.com/oracle/node-oracledb/blob/v3.1.2/doc/api.md#aq

@tgulacsi we recently added native support to node-oracledb 4: https://oracle.github.io/node-oracledb/doc/api.html#aq and updated the AQ API in cx_Oracle to match: https://cx-oracle.readthedocs.io/en/latest/aq.html Yell out if you want to talk about the implementation.

sinys15 · 2019-07-31T06:09:27Z

@tgulacsi

https://docs.oracle.com/cd/B14117_01/server.101/b10785/jm_exmpl.htm shows it with pl/sql.

The part is showing enqueue example for sys.aq$_jms_text_message:
Example 16-8 Enqueuing Through the Oracle JMS Administrative Interface

The part is showing dequeue example for sys.aq$_jms_bytes_message (not the same sys.aq$_jms_text_message but just show the example):
Example 16-3 Dequeuing and Retrieving JMS Bytes Message Data

@cjbj
node.js, python - is fine but I need it in Go. It is already realized in Java a long time ago.
Example Dequeue from one of Java project:

// Dequeue 
Message message = queueConsumer.receive(queueReceiveTimeout);
TextMessage txtMsg = (TextMessage) message;

SourceMessage sourceMsg = new SourceMessage()
		.withId(txtMsg.getJMSMessageID())
		.withAppId(txtMsg.getStringProperty("JMSXAppID"))
		.withType(txtMsg.getJMSType())
		.withData(txtMsg.getText());

Enqueue and Dequeue will be enough, Queue Subscribers is not in priority.

sinys15 · 2019-08-01T03:43:45Z

@tgulacsi
Hi, I see your sketches in Queue.go and it's cool to get some code very soon.
Did you translate this code from other projects like @cjbj put references or did you do it from zero?

I just want to note several moments:
1)
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_AQ.html#GUID-56E78CA6-3EB0-44C9-AEB7-F13A5A077D73
Absent constants for WAIT parameter (we offten use it in our projects), it can be constants FOREVER, NO_WAIT or value in seconds.
2)
// Dequeues messages into the given slice.
func (Q Queue) Dequeue(messages []Message) (int, error) {
Oracle separate enqueue and dequeue operations for single and batch messages. It looks like: DBMS_AQ.Enqueue and DBMS_AQ.Dequeue functions for single message and DBMS_AQ.ENQUEUE_ARRAY / DBMS_AQ.DEQUEUE_ARRAY for set of messages.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_AQ.html#GUID-A0E01434-EF7A-425D-BDC5-55262E98010B
I do not insist but perhaps it would be better to repeat the Oracle way because %_ARRAY functions have specific parameters.

tgulacsi · 2019-08-01T05:34:52Z

It is what the underlying ODPI-C exposes.
Plus, I didn't want to have separate functions for one and for many enq/deq.

If you check what are the numeric values of those constants, I can add them.
Of course it uses different underlying functions based on the length of the slice: if you use a 1 length messages slice, it uses enqOne / deqOne, otherwise the Many version.

As I don't have too much free time right now, the "queue" branch contains a "proposal".
If you can write tests for it that would help a lot!

sinys15 · 2019-08-01T06:26:20Z

If you check what are the numeric values of those constants, I can add them.

In dbms_aq it looks like
FOREVER CONSTANT BINARY_INTEGER := -1;
FOREVER_UNLESS_SHUTDOWN CONSTANT BINARY_INTEGER := -2;
NO_WAIT CONSTANT BINARY_INTEGER := 0;
but how it looks in ODPI-C I don't know.

FOREVER_UNLESS_SHUTDOWN - undocumented, support is not required
FOREVER - as far I remember it is default

cjbj · 2019-08-01T08:30:44Z

Regarding constants, search for "AQ" and "advanced queuing" in https://github.com/oracle/odpi/blob/master/include/dpi.h
The same values were used in node-oracledb: https://oracle.github.io/node-oracledb/doc/api.html#oracledbconstantsaq

sinys15 · 2019-08-01T08:40:23Z

@cjbj

declare
  x BINARY_INTEGER := dbms_aq.FOREVER;
begin
  dbms_output.put_line('result is ' || to_char(x));
end;
/

result is -1

but in your links it is
#define DPI_DEQ_WAIT_FOREVER ((uint32_t) -1)
and
oracledb.AQ_DEQ_WAIT_FOREVER | 4294967295 | Wait forever if no message is available

it looks like just big value in seconds, it is not original -1 value, but of course it will work and looks like wait infinity (not really).

@cjbj

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi · 2019-08-01T09:04:31Z

That FOREVER is uint32(-1) = uint32(0) - 1 = 2^32 - 1 = 4294967295 because it cycles from 0 to the max number.

Either way, a big number is just perfect for waiting forever...

sinys15 · 2019-08-01T09:41:26Z

I see but is this parameter uint32 data type or why it can't be honest int32 = -1?

tgulacsi · 2019-08-02T09:24:43Z

7b411ab passes the simple TestQueue test in queue_test.go.

sinys15 · 2019-08-07T04:22:23Z

Looks good! But what about more complicated data types?
From simple

create or replace type MY_ARRAY AS TABLE OF varchar2(1000)
/

create or replace type MY_STRUCT AS OBJECT (
    Field_NUM  number,
    Field_VCHR varchar2(100),
    Field_ARR  MY_ARRAY
)
/

   ...
DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'MY_QUEUE_TABLE', queue_payload_type => 'MY_STRUCT');
  ...

To more complicated sys.aq$_jms_text_message data type with methods.

I don't know how to work with Object Types with goracle.

tgulacsi · 2019-08-07T05:00:02Z

They're there, just not tested thoroughly yet. See TestObject... tests! On Wed, Aug 7, 2019 at 6:22 AM +0200, "sinys15" <[email protected]> wrote: Looks good! But what about more complicated data types? From simple create or replace type MY_ARRAY AS TABLE OF varchar2(1000) / create or replace type MY_STRUCT AS OBJECT ( Field_NUM number, Field_VCHR varchar2(100), Field_ARR MY_ARRAY ) / ... DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'MY_QUEUE_TABLE', queue_payload_type => 'MY_STRUCT'); ... To more complicated sys.aq$_jms_text_message data type with methods. I don't know how to work with Object Types with goracle. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or mute the thread.

@cjbj

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi · 2019-08-14T20:21:27Z

I've started a TestQueueObject test, but

the nested array would need a nested table, which I don't know how ti specify to DBMS_AQADM.CREATE_QUEUE_TABLE
Something is fishy with the object creation, as now it exists with "ORA-03135: connection lost contact". Maybe we have to attach the object to the connection somehow?
I'll have to check that cx_Oracle how does this.

@cjbj

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi · 2019-08-15T16:00:34Z

OCI error ORA-03135: connection lost contact
Process ID: 15178
Session ID: 190 Serial number: 13335 (dpiQueue_enqOne / enqueue message)

I'd love to get some help from @anthony-tuininga or @cjbj - I don't know what am I doing wrong.

Get the object type, create an object, fill its arguments, then Enqueue a message:

        oTyp, err := goracle.GetObjectType(conn, qTypName)
        if err != nil {
                t.Fatal(err)
        }
        defer oTyp.Close()
        obj, err := oTyp.NewObject()
        if err != nil {
                t.Fatal(err)
        }
        defer obj.Close()
        if err = obj.Set("F_DT", time.Now()); err != nil {
                t.Error(err)
        }
        if err = obj.Set("F_VC20", "árvíztűrő tükörfúrógép"); err != nil {
                t.Error(err)
        }
        if err = obj.Set("F_NUM", 3.14); err != nil {
                t.Error(err)
        }
        t.Log("obj:", obj)
        if err = q.Enqueue([]goracle.Message{goracle.Message{Object: obj}}); err != nil {
                t.Fatal("enqueue:", err)
        }

With DPI_LOG_LEVEL=28:

ODPI [12250] 2019-08-15 17:35:13.592: fn start dpiConn_getObjectType(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.635: fn end dpiConn_getObjectType(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.635: fn start dpiObjectType_getInfo(0x2491fa0)
ODPI [12250] 2019-08-15 17:35:13.635: fn end dpiObjectType_getInfo(0x2491fa0) -> 0
ODPI [12250] 2019-08-15 17:35:13.635: fn start dpiObjectType_getAttributes(0x2491fa0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectType_getAttributes(0x2491fa0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiObjectAttr_getInfo(0x2492100)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectAttr_getInfo(0x2492100) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiObjectAttr_getInfo(0x2492160)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectAttr_getInfo(0x2492160) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiObjectAttr_getInfo(0x24921c0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiObjectAttr_getInfo(0x24921c0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiConn_newQueue(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiConn_newQueue(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiQueue_getEnqOptions(0x2492220)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiQueue_getEnqOptions(0x2492220) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiEnqOptions_getTransformation(0x2484f50)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiEnqOptions_getTransformation(0x2484f50) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiEnqOptions_getVisibility(0x2484f50)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiEnqOptions_getVisibility(0x2484f50) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiQueue_getDeqOptions(0x2492220)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiQueue_getDeqOptions(0x2492220) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiDeqOptions_getTransformation(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.648: fn end dpiDeqOptions_getTransformation(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.648: fn start dpiDeqOptions_getCondition(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getCondition(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getConsumerName(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getConsumerName(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getCorrelation(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getCorrelation(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getMode(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getMode(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getMsgId(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getMsgId(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getNavigation(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getNavigation(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getVisibility(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getVisibility(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiDeqOptions_getWait(0x23df2e0)
ODPI [12250] 2019-08-15 17:35:13.649: fn end dpiDeqOptions_getWait(0x23df2e0) -> 0
ODPI [12250] 2019-08-15 17:35:13.649: fn start dpiConn_getObjectType(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiConn_getObjectType(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectType_getInfo(0x24922a0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectType_getInfo(0x24922a0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectType_getAttributes(0x24922a0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectType_getAttributes(0x24922a0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectAttr_getInfo(0x249d830)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectAttr_getInfo(0x249d830) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectAttr_getInfo(0x249d8b0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectAttr_getInfo(0x249d8b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectAttr_getInfo(0x249d930)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectAttr_getInfo(0x249d930) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObjectType_createObject(0x24922a0)
ODPI [12250] 2019-08-15 17:35:13.662: fn end dpiObjectType_createObject(0x24922a0) -> 0
ODPI [12250] 2019-08-15 17:35:13.662: fn start dpiObject_setAttributeValue(0x249d9b0)
ODPI [12250] 2019-08-15 17:35:13.677: fn end dpiObject_setAttributeValue(0x249d9b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.678: fn start dpiObject_setAttributeValue(0x249d9b0)
ODPI [12250] 2019-08-15 17:35:13.689: fn end dpiObject_setAttributeValue(0x249d9b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.689: fn start dpiObject_setAttributeValue(0x249d9b0)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiObject_setAttributeValue(0x249d9b0) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiConn_newMsgProps(0x2473ec0)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiConn_newMsgProps(0x2473ec0) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiMsgProps_setPriority(0x249da00)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiMsgProps_setPriority(0x249da00) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiMsgProps_setPayloadObject(0x249da00)
ODPI [12250] 2019-08-15 17:35:13.701: fn end dpiMsgProps_setPayloadObject(0x249da00) -> 0
ODPI [12250] 2019-08-15 17:35:13.701: fn start dpiQueue_enqOne(0x2492220)
ODPI [12250] 2019-08-15 17:35:13.724: OCI error ORA-03135: connection lost contact
Process ID: 15178
Session ID: 190 Serial number: 13335 (dpiQueue_enqOne / enqueue message)

@cjbj

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi · 2019-08-16T09:27:54Z

Ok, that was a strange error: the object's F_vc20 was filled with more than its length, and that materialized in such an error. Fixing that, the object is queued successfully!

@cjbj

Thanks for @cjbj for pointing those out in dpi.h For #186

cjbj mentioned this issue Jul 31, 2019

Support AQ mattn/go-oci8#348

Open

tgulacsi added a commit that referenced this issue Jul 31, 2019

Queue compiles (no tests yet) - for #186

11120f7

tgulacsi added a commit that referenced this issue Aug 1, 2019

queue: add WaitForever and NoWait constants

27c78ed

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi added a commit that referenced this issue Aug 14, 2019

Queue compiles (no tests yet) - for #186

dbed990

tgulacsi added a commit that referenced this issue Aug 14, 2019

queue: add WaitForever and NoWait constants

fcc1885

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi added a commit that referenced this issue Aug 15, 2019

Queue compiles (no tests yet) - for #186

4c114d7

tgulacsi added a commit that referenced this issue Aug 15, 2019

queue: add WaitForever and NoWait constants

2647f3f

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi added a commit that referenced this issue Aug 16, 2019

Queue compiles (no tests yet) - for #186

6bf4922

tgulacsi added a commit that referenced this issue Aug 16, 2019

queue: add WaitForever and NoWait constants

5b2271c

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi added a commit that referenced this issue Aug 18, 2019

Queue compiles (no tests yet) - for #186

a391c11

tgulacsi added a commit that referenced this issue Aug 18, 2019

queue: add WaitForever and NoWait constants

16336f1

Thanks for @cjbj for pointing those out in dpi.h For #186

tgulacsi closed this as completed in 34af0fc Aug 19, 2019

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Support AQ #186

Support AQ #186

sinys15 commented Jul 31, 2019 •

edited

Loading

tgulacsi commented Jul 31, 2019 via email

cjbj commented Jul 31, 2019

sinys15 commented Jul 31, 2019 •

edited

Loading

sinys15 commented Aug 1, 2019 •

edited

Loading

tgulacsi commented Aug 1, 2019

sinys15 commented Aug 1, 2019 •

edited

Loading

cjbj commented Aug 1, 2019

sinys15 commented Aug 1, 2019 •

edited

Loading

tgulacsi commented Aug 1, 2019

sinys15 commented Aug 1, 2019

tgulacsi commented Aug 2, 2019

sinys15 commented Aug 7, 2019

tgulacsi commented Aug 7, 2019 via email

tgulacsi commented Aug 14, 2019

tgulacsi commented Aug 15, 2019

tgulacsi commented Aug 16, 2019

Support AQ #186

Support AQ #186

Comments

sinys15 commented Jul 31, 2019 • edited Loading

tgulacsi commented Jul 31, 2019 via email

cjbj commented Jul 31, 2019

sinys15 commented Jul 31, 2019 • edited Loading

sinys15 commented Aug 1, 2019 • edited Loading

tgulacsi commented Aug 1, 2019

sinys15 commented Aug 1, 2019 • edited Loading

cjbj commented Aug 1, 2019

sinys15 commented Aug 1, 2019 • edited Loading

tgulacsi commented Aug 1, 2019

sinys15 commented Aug 1, 2019

tgulacsi commented Aug 2, 2019

sinys15 commented Aug 7, 2019

tgulacsi commented Aug 7, 2019 via email

tgulacsi commented Aug 14, 2019

tgulacsi commented Aug 15, 2019

tgulacsi commented Aug 16, 2019

sinys15 commented Jul 31, 2019 •

edited

Loading

sinys15 commented Jul 31, 2019 •

edited

Loading

sinys15 commented Aug 1, 2019 •

edited

Loading

sinys15 commented Aug 1, 2019 •

edited

Loading

sinys15 commented Aug 1, 2019 •

edited

Loading