Need help for calling PL/SQL Stored Proc with custom object as input

[goidhi]

Not able to call an Oracle stored procedure which takes a custom object as input. Sample here: PROCEDURE set_test_comments( p_test_comments_i IN TEST_COMMENTS_OBJECT, p_state_o OUT varchar2)

CREATE OR REPLACE TYPE TEST_O.TEST_COMMENTS_OBJECT FORCE AS OBJECT (user_id NUMBER, user_name VARCHAR2(20), comment_date DATE, comments VARCHAR2(4000) )

Tried some options but none of them helped, so would really appreciate any suggestions on how to move forward! Don't have a running code yet.

1. Get the object type, and then create a new object. But unable to set the input data - objType, err := goracle.GetObjectType(tx, "TEST_O.TEST_COMMENTS_OBJECT") inputObj, err := objType.NewObject()
2. Set each attribute in the above object by creating a Data variable and set bytes/bool (depending on input data type) but end up getting stack overflow error: var tmp goracle.Data tmp.SetBool(true) inputObj.SetAttribute("COMMENTS", &tmp)

[tgulacsi]

The easiest is to leave the object out:

db.ExecContext(ctx, "BEGIN set_test_comments(TEST_COMMENTS_OBJECT(
  user_id=>:1, user_name=>:2, comment_date=>:3, comments=>:4
)); END;",
  1, "name", time.Now(), "comment")

The harder is to test out the code related to object handling, and fix it. I don't really have time for this now, and I don't work with objects, so this is postponed.

[tgulacsi]

If you could provide the two test cases you developed, that may help the testing!

[goidhi]

We did think of the first option to leave the object out, but the current structure is a complex one, and it would be too cumbersome to expand all the parameters. And these are existing stored procs, so not much control on changing them right away! Would try to share a workable copy of those two test cases, but where i got stuck is how to copy the input data coming into the service, to the input object.

Library expects a type of "goracle.Object" and once i create an object type, i need to set the attributes via "goracle.Data" type. The problem is "goracle.Data" is expecting "C.dpiData" while setting any primitive type which i don't have access to initialize in my code. Any ideas around how to go about this? Can try out for few more days!

[tgulacsi]

For a workaround, what about after creating the object, you Get the attribute (to have a dpiData) and then Set on that data (and SetAttribute on the object, too)?

[goidhi]

Didn't work, program gets hung on the get attribute itself, trying to debug the issue, But the overall object structure is bit complex, and involves 3 such objects in one procedure call, so this would be a tedious path to take. Would leave it for now and wait when you are able to get some time for a concrete implementation of stored procs/objects! We have decided with an alternate platform for such use cases.

[tgulacsi]

Ok, but a concrete example of the object type (not-too-made-up, at least the used attr types and the needed hierarcy) would really help!

[tgulacsi]

It seems that we need to expose dpiConn_newVar (https://oracle.github.io/odpi/doc/functions/dpiConn.html#dpiConn_newVar), but the API is not trivial: it needs an Oracle- and a Native Type number, maybe the Object type pointer and if it is a slice, then the slice length.

Any suggestions?

[goidhi]

Hi, below is one object type example. So in the proc, i have 2 table types of similar objects as input. Requirement is to send a list of project task details and activity details to the proc.

PRJ_PKG.CREATE_TASK_ACTIVITY (p_create_task_i IN PRJ_TASK_TAB_TYPE, p_create_activity_i IN PRJ_ACTIVITY_TAB_TYPE, p_project_id_i IN NUMBER)

CREATE OR REPLACE TYPE PRJ_TASK_TAB_TYPE FORCE IS TABLE OF PRJ_TASK_OBJ_TYPE

CREATE OR REPLACE PRJ_TASK_OBJ_TYPE AS OBJECT ( PROJECT_NUMBER VARCHAR2(100) ,SOURCE_ID VARCHAR2(100) ,TASK_NAME VARCHAR2(300) ,TASK_DESCRIPTION VARCHAR2(2000) ,TASK_START_DATE DATE ,TASK_END_DATE DATE ,TASK_COST NUMBER ,SOURCE_PARENT_ID NUMBER ,TASK_TYPE VARCHAR2(100) ,QUANTITY NUMBER )

[goidhi]

It seems that we need to expose dpiConn_newVar (https://oracle.github.io/odpi/doc/functions/dpiConn.html#dpiConn_newVar), but the API is not trivial: it needs an Oracle- and a Native Type number, maybe the Object type pointer and if it is a slice, then the slice length.

Any suggestions?

Okay, this looks like it should work. So basically i need to first -> get the object type reference -> then use it to get handle of dpiData variable -> use it and set the values of each attribute via goracle.Data reference -> set the entire structure back into the goracle.Object which would be passed as an input to Exec I feel like it has some duplicate steps, any suggestions on what can be simplified?

Hopefully i get some time this week to test this out and would let you know.