CODEWITHSUNDEEP
oracle-databaserestoracle-ords
AlexMI
AlexMI

Reputation: 986

Oracle ORDS remote procedure call : payload and debug

I am trying to develop some REST API and I am working with: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production, Oracle ORDS and SQL Developer 17.3.1.279.

I already develop and test a GET API in order to prove that the db is REST-Enabled.

I try to develop a POST API and I am reading this article

My POST handler is:

declare
    lrow accounts%rowtype := null;    
begin   
    lrow.id := :id;
    lrow.account := :account;

    /*added to inspect the current value*/
    Raise_Application_Error(-20000, '*'||:account||'*');

    lrow.category := :category;
    lrow.address := :address;
    lrow.zipcode := :zipcode;
    lrow.city := :city;
    lrow.county := :county;
    ASADMIN.INSERTACCOUNT(lrow);
    :error := null;
exception
    when others then
        :error := sqlerrm;
end;

For each bind variable I create a corresponding in/out parameter in the handler.

I test the API with POSTMAN passing this object:

{
"id" : "18092018"
,"account" : "Buster Keaton"
,"category" : "TEST CATEGORY"
,"address" : "TEST ADDRESS"
,"zipcode" : "12345"
,"city" : "TEST CITY"
,"county" : "TEST COUNTY"
,"error" : null
}

and the response was "Cannot insert NULL in ..." then I add the raise application error in order to check what is the value passed that results as null.

I would like to know which is the common (or best) practice to debug a REST API and the way to "monitor" bind variables.

Upvotes: 2

Views: 1338

Answers (2)

AlexMI
AlexMI

Reputation: 986

After commenting the response from thatjeffsmith I focus on service parameters and bind variables and I realize that my setup was completly wrong. I would like to share how I correct what was my mistake.

In the POST handler I use bind variable that I pass to the service in the body of the request but I define corresponding parameters as IN/OUT so the SOURCE can be only HEADER or URI and the bind variables result all null.

I found very useful theese articles: Parameters and binds and resultset.

I modify the handler deleting ALL the IN/OUT parameters because every variable (or JSON object) passed via request body are automatically "matched" with bind variables used in the handler.

Now I am working/studying on resultset in order to return the well formed json object with all the information needed to my app.

Upvotes: 0

thatjeffsmith
thatjeffsmith

Reputation: 22427

I try to run the anon block attached to the handler inside of SQL Developer to make sure it's going to work there first. So, does that work, in SQL Developer where you are doing the restful service definition?

For others reading this question, when you want to 'debug' and don't have access to the ORDS server logs: If you get a 500 response when calling it from ORDS, it can be helpful to run ORDS in 'debug' mode.

There are two properties you can enable, debug and print to screen. (docs)

This will show the stack dump from the back end in your browser - not something you ever want to do in 'prod' but since you're debugging, I'm assuming you're in a safe place.

Once that's on, make your call again -

enter image description here

Now you can see the ORA error code that's causing the 500 - and you can also probably see how ORDS is executing the anon plsql block - maybe you're not processing the inputs correctly...

I talk about this here.

For your case specifically, you either have a typo or you are neglecting a value when it comes to making your call to INSERTACCOUNT().

Without seeing the spec for INSERTACCOUNT(), we're left to guess what might be happening.

Upvotes: 2

Related Questions