Forum Stats

  • 3,825,482 Users
  • 2,260,519 Discussions
  • 7,896,547 Comments

Discussions

ORDS Issue in Oracle 12.1

user8632739
user8632739 Member Posts: 7 Green Ribbon

I have executed below with JSON on my 12.1.0.2 database but receiving below error. Looks like 12.1 doesn't support JSON. Could you please let me know what is this error code ? I am not able to get proper resources from internet regarding this.

Oracle Version 12.1 but the same one is working in Oracle 12.2 version

With content type SQL it is working perfectly 




[[email protected] ~]$ curl -k -X POST https://test:8445/ -u testapi:testapi -d "exec dbms_workload_repository.create_snapshot();" -H "Content-Type: Application/sql"|jq

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed

100 324 0 276 100 48 103 18 0:00:02 0:00:02 --:--:-- 104


 {

 "env": {

 "defaultTimeZone": "GMT+03:00"

 },

 "items": [

 {

 "statementId": 1,

 "statementType": "sqlplus",

 "statementPos": {

 "startLine": 1,

 "endLine": 1

 },

 "statementText": "exec 

  dbms_workload_repository.create_snapshot()",

  "response": [

  "\nPL/SQL procedure successfully completed.\n\n"

 ],

 "result": 0

 }

 ]

 }




###################### Not working with content type JSON ##########




[[email protected] ~]$ curl -k -X POST https://test:8445/ -u testapi:testapi -d "test_exec.json" -H "Content-Type: Application/json"|jq

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed

100 424 100 410 100 14 2042 69 --:--:-- --:--:-- --:--:-- 2146


 {

 "code": "BadRequest",

 "title": "Bad Request",

 "message": "An error occurred parsing the SQL script ",

 "o:errorCode": "ORDS-24000",

  "cause": "An error with the following message occurred: 

  Problem recognizing JSON",

 "action": "Ensure the SQL script is well-formed",

 "type": "tag:oracle.com,2020:error/BadRequest",

  

}

}


Validated the JSON file and it is properly formated.

[[email protected] ~]$ cat test_exec.json|jq

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,581 Employee

    JSON has evolved as a feature since it's debut in 12.1 all the way up to our upcoming next big release of the database.

    But, that error message means exactly what it says, it can't wrangle the JSON you've given us. If it doesn't work in 12.1 but does work in 12.2, well that's a great reason to upgrade to to 19c as soon as you can.

  • user8632739
    user8632739 Member Posts: 7 Green Ribbon
    edited Jun 22, 2022 6:16PM

    Thanks Jeff for your response. As of now, Our company don't want upgrade as it's tied with product and have sensitive data. What is the alternate option to use ORDS in this situation as we need expose lot stored procedure as RESP service? Please suggest.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,581 Employee

    For the best experience, upgrade your database.

    But

    If the SQL content type works, why. not just keep using it?

    Exposing Stored Procedures as a REST Service, that's an actual feature of ORDS, - AutoREST. Check that out vs you manually calling the stored procedures via anonymous blocks and using REST Enabled SQL.

  • user8632739
    user8632739 Member Posts: 7 Green Ribbon

    Hi Jeff

    JSON input and output works in Oracle12.1 version also. Thanks