Forum Stats

  • 3,734,237 Users
  • 2,246,918 Discussions
  • 7,857,196 Comments

Discussions

ORDS 18.4 Why am I getting an empty :body_text (CLOB)?

qskyhigh
qskyhigh Member Posts: 7

Hello,

Tell me, please, why does the empty value come?

To send a request, I use SoapUI 5.5.

But :body is not null.

Do I need to do something in the settings of ORDS?

DECLARE

    --b_body BLOB := :body;

    c_body CLOB := :body_text;

BEGIN

    if :body_text is null then

    htp.print('EMPTY');

    end if; 

END;

thatJeffSmith-Oracle

Best Answer

  • Capt. Egg
    Capt. Egg Member Posts: 262
    edited Mar 3, 2020 8:59PM Accepted Answer

    Hi Jeff,

    The problem is resolved for me by avoiding the RESTful Services GUI in SQL Workshop and just creating the services via SQL Developer.

    I can now access :body_text, it's fast and I no longer have to worry about the parsing errors when something was attempting to parse simple JSON key/value pairs in order to convert them to bind variables. I guess in my case it could be the old version of APEX we're running.

    Cheers!

Answers

  • EJ-Egyed
    EJ-Egyed Member Posts: 125 Blue Ribbon
    edited Feb 21, 2020 8:10AM

    :body or :body_text is being dereferenced after it is getting assigned to a variable so it will no longer contain any value.

    Try using this:

    DECLARE

        c_body CLOB := :body_text;

    BEGIN

        if c_body = empty_clob() then

        htp.print('EMPTY');

        end if;

    END;

    thatJeffSmith-Oracle
  • qskyhigh
    qskyhigh Member Posts: 7
    edited Feb 21, 2020 8:23AM

    No matter, :body_text is still empty, and :body is not empty.

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Feb 23, 2020 8:25PM

    Can you try ords 19.4?

    Or convert your blob to a clob...

  • qskyhigh
    qskyhigh Member Posts: 7
    edited Feb 25, 2020 1:52AM

    In fact, I can not try to 19.4, because it is a production and need additional coordination of the installation. I can convert  But I would not want to convert it again if there is a built-in feature=( I just can’t understand why the data doesn’t come in CLOB (:body_text) By the way, I'm reading an article on your site right now, thanks for your useful information =)

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Feb 25, 2020 6:51AM

    Share a complete scenario we can use to test/replicate what you're doing.

  • Capt. Egg
    Capt. Egg Member Posts: 262
    edited Mar 2, 2020 11:11PM

    I have the same problem with APEX 5.1 on ORDS 19.2. Here's how to reproduce it:

    1. Create a POST Resource Handler:
      declare<br/>  l_body CLOB := :body_text;<br/>begin<br/>  htp.p(l_body);<br/>end;
    2. Post some data to that thing, note the response is a newline and nothing more no matter what data you post or Content-Type you use.

    Based on the ORDS documentation, I can't see why it's always null. If you use Content-Type: application/json, then you can access simple key/value pairs via bind variables. But still always, :body_text is null while :body contains the raw body as a BLOB.

    I find the basic JSON parsing less than useful. On a side note, how do we switch that off so we can have rich JSON in a request with Content-Type: application/json without parsing errors so we can just parse it properly, using APEX_JSON or something like that?

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Mar 3, 2020 9:03AM

    Not able to reproduce...

    -- Generated by Oracle SQL Developer REST Data Services 19.4.0.351.1906

    -- Exported REST Definitions from ORDS Schema Version 19.4.0.b3501453

    -- Schema: HR   Date: Tue Mar 03 09:03:10 EST 2020

    --

    BEGIN

      ORDS.ENABLE_SCHEMA(

          p_enabled             => TRUE,

          p_schema              => 'HR',

          p_url_mapping_type    => 'BASE_PATH',

          p_url_mapping_pattern => 'hr',

          p_auto_rest_auth      => FALSE);   

      ORDS.DEFINE_MODULE(

          p_module_name    => 'body',

          p_base_path      => '/body/',

          p_items_per_page =>  25,

          p_status         => 'PUBLISHED',

          p_comments       => NULL);     

      ORDS.DEFINE_TEMPLATE(

          p_module_name    => 'body',

          p_pattern        => 'clob',

          p_priority       => 0,

          p_etag_type      => 'HASH',

          p_etag_query     => NULL,

          p_comments       => NULL);

      ORDS.DEFINE_HANDLER(

          p_module_name    => 'body',

          p_pattern        => 'clob',

          p_method         => 'POST',

          p_source_type    => 'plsql/block',

          p_items_per_page =>  0,

          p_mimes_allowed  => '',

          p_comments       => NULL,

          p_source         =>

    'declare

    the_page clob;

    begin

    the_page := :body_text;

    htp.p(the_page);

    end;'

          );

      COMMIT;

    END;

    /

    I posted up a simple HTML file that had some hockey data in it and some js to add a search form...

    pastedImage_0.png

  • Capt. Egg
    Capt. Egg Member Posts: 262
    edited Mar 3, 2020 6:30PM

    I used the SQL Workshop | RESTful Services interface in APEX to create it. Nothing shows up under Modules in the application schema in SQL Developer.

    body.png

  • Capt. Egg
    Capt. Egg Member Posts: 262
    edited Mar 3, 2020 8:59PM Accepted Answer

    Hi Jeff,

    The problem is resolved for me by avoiding the RESTful Services GUI in SQL Workshop and just creating the services via SQL Developer.

    I can now access :body_text, it's fast and I no longer have to worry about the parsing errors when something was attempting to parse simple JSON key/value pairs in order to convert them to bind variables. I guess in my case it could be the old version of APEX we're running.

    Cheers!

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Mar 3, 2020 9:41PM

    Weird, it shouldn't matter.

  • Capt. Egg
    Capt. Egg Member Posts: 262
    edited Mar 3, 2020 9:49PM

    I've tested it on SQL Workshop via apex.oracle.com which is currently APEX 19.2 and experienced no issues. I'd say there's an incompatibility with APEX 5.1, I can't even find where APEX 5.1 is creating it's ORDS modules in SQL Developer. Possibly they're getting created with some legacy API?

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,701 Employee
    edited Mar 4, 2020 8:04AM

    restful services in apex v5 go into their own repo

    apex 18/19 something and newer ords give you the option of migrating your apex based ords services to the ords repo

  • User_VKVA0
    User_VKVA0 Member Posts: 1 Green Ribbon

    I am using Apex version 19.2.0.00.18

    and I am facing the same issue blank body_text while passing JSON via a GUI is it a bug or a version issue ?

Sign In or Register to comment.