Skip to Main Content
Forums

ORDS, SODA & JSON in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORDS performance for GET Handler | Source type - Media Resource

User_Z8YBQAug 30 2021

Hi,
I am generating a JSON as a Media Resource GET handler.
The GET handler use a function to get already created JSON from DB . It is written like below:
SELECT 'application/json',get_json_db FROM DUAL;
When i run this SQL in SQL Developer it takes 400 ms. However when the API endpoint is called by client or from POSTMAN it takes around 3.7 secs to receive the JSON.
What measures can be taken to improve the ORDS performance as the query is fine and as already JSON is generated so ORDS should not be doing any more JSON processing.? - please suggest.
Thanks,
A

Comments

Mr Peabody-Oracle

There might be some caveats because you did not specify what version of Documaker you are using. The initialization of DAL functionality is sufficient in later versions - say 12.5 on - but it is possible this might not work in some really old versions.

In your INI, under the TRN_FIELDS group, comment out the RUNDATE definition by placing a semicolon at the start. (This way you will be able to reverse it if this doesn't work.)

pastedImage_0.png

Next, look for - or add - the GenTrnDummyFields group.

pastedImage_1.png

This group is used to pre-initialize any GVM variables that you want set at the transaction level, but not necessarily coming from the extract. You see in this example, mine is setting the variables ONE, TWO, and now RUNDATE.

The definition expects to have a constant definition, but through the wizardry of INI built-in functions, you can call a DAL script that actually returns the value. The ~DalRun is a built-in that executes the named script that follows. Whatever that script returns becomes the value assigned into the RUNDATE GVM when the transaction begins. In my case, I called the script "adate" and therefore would be assumed "adate.dal" and be a standard resource.

The script can do whatever it wants to determine the date, but I think you want to have it return in the YYYYMMDD format.

User_QGE9P

I was able to call a DAL doing it this way but I had to hardcode a date to get it to work.

Is there anyway that the DAL can look at values in the extract file to determine how the date should be set.

I need to look at other trn_fields or other values in the extract file to determine the date.

This is documaker 12.6 Standard Edition running 3 steps.

Just let me know if this is possible.

Mr Peabody-Oracle
Answer

There is a caveat, but yes, you should be able to use DAL to query other data and information. The caveat is that this assignment occurs at the start of a transaction, so the question is whether the information you are seeking has been loaded and is available yet.

I'm pretty sure that the GentrnDummyFields are assigned after the TRN_FIELDS. That being the case, you should be able to use DAL to query those GVMs. Since you are talking about the 3-step process - GenTrn, Gendata, and GenPrint - I might question whether the rest of your extract rows are loaded at the point where  you need. You would have to determine this by using the GetData or SrchData functions from DAL to do your queries.

Marked as Answer by User_QGE9P · Sep 27 2020
3255688

Below works for us. Can you do this.

< TRN_FIELDS >

RunDate = !/xpath/to/my/rundate

Mr Peabody-Oracle

That would be the traditional way to map the RunDate - assuming you are using an XML extract. The question was whether you could call DAL to determine the Rundate in some other way than direct mapping in TRN_FIELDS.

1 - 5

Post Details

Added on Aug 30 2021
3 comments
382 views