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!

ORA-00900: invalid SQL statement when POSTing to ORDS 18

gizzardqueenMay 14 2018 — edited May 15 2018

I've stood up a couple docker containers using the Oracle-provided Docker files for ORDS 18.1 and RDBMS 12.2. Everything seems to be working there as I can perform simple GET requests against ORDS no problem. The trouble I'm having is when I try to create slightly-more complicated POST requests. Using a bit of the OE sample schema I've done the following:

create user OE identified by OE; 
GRANT "CONNECT" TO OE; 
GRANT "RESOURCE" TO OE;
grant unlimited tablespace to OE;
alter session set current_schema = OE;
 
CREATE TYPE cust_address_typ
  OID '82A4AF6A4CD1656DE034080020E0EE3D'
  AS OBJECT
    ( street_address     VARCHAR2(40)
    , postal_code        VARCHAR2(10)
    , city               VARCHAR2(30)
    , state_province     VARCHAR2(10)
    , country_id         CHAR(2)
    );
/
 
 
CREATE TYPE phone_list_typ
  OID '82A4AF6A4CD2656DE034080020E0EE3D'
  AS VARRAY(5) OF VARCHAR2(25);
/
 
 
CREATE TABLE customers
    ( customer_id        NUMBER(6)
    , cust_first_name    VARCHAR2(20) CONSTRAINT cust_fname_nn NOT NULL
    , cust_last_name     VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL
    , cust_address       cust_address_typ
    , phone_numbers      phone_list_typ
    , nls_language       VARCHAR2(3)
    , nls_territory      VARCHAR2(30)
    , credit_limit       NUMBER(9,2)
    , cust_email         VARCHAR2(40)
    , account_mgr_id     NUMBER(6)
    , cust_geo_location  MDSYS.SDO_GEOMETRY
    , CONSTRAINT         customer_credit_limit_max
                         CHECK (credit_limit <= 5000)
    , CONSTRAINT         customer_id_min
                         CHECK (customer_id > 0)
 
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
 
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'OE',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'oe',
                       p_auto_rest_auth => FALSE);
 
 
    commit;
 
 
END;
/
 
-- Enable rest on table
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 
 
    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'OE',
                       p_object => 'CUSTOMERS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'customers',
                       p_auto_rest_auth => FALSE);
 
 
    commit;
 
 
END;
/
 
CREATE OR REPLACE PROCEDURE create_customer(
  p_data IN varchar2 )
AS   BEGIN   INSERT INTO customers (customer_id, cust_first_name, cust_last_name, cust_address,
   phone_numbers, nls_language, nls_territory, credit_limit,
   cust_email, account_mgr_id, cust_geo_location)
   SELECT    customer_id,
   cust_first_name,
   cust_last_name,
   CAST(cust_address AS cust_address_typ) as cust_address,
   CAST(phone_numbers AS phone_list_typ) as phone_numbers,
   nls_language,
   nls_territory,
   credit_limit,
   cust_email,
   account_mgr_id,
  MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(cust_lat, cust_lon, NULL), NULL, NULL) as cust_geo_location
   FROM json_table(p_data FORMAT JSON, '$.customers[*].customer'    COLUMNS (
   customer_id NUMBER PATH '$.customer_id',
   cust_first_name VARCHAR2 PATH '$.cust_first_name',
   cust_last_name VARCHAR2 PATH '$.cust_last_name',
   cust_address VARCHAR2 PATH '$.cust_address',
   phone_numbers VARCHAR2 PATH '$.phone_numbers',
   nls_language VARCHAR2 PATH '$.nls_language',
   nls_territory VARCHAR2 PATH '$.nls_territory',
   credit_limit NUMBER PATH '$.credit_limit',
   cust_email VARCHAR2 PATH '$.cust_email',
   account_mgr_id NUMBER PATH '$.account_mgr_id',
   cust_lat NUMBER PATH '$.cust_lat',
   cust_lon NUMBER PATH '$.cust_lon'    ));
 
   EXCEPTION   WHEN OTHERS   THEN    htp.print('There is a problem with the sql ' || SQLERRM);
  END;
/
 
BEGIN   ORDS.define_template(
  p_module_name => 'Customer_Details',
  p_pattern => 'details/');
 
  ORDS.define_handler(
  p_module_name => 'Customer_Details',
  p_pattern => 'details/',
  p_method => 'POST',
  p_source_type => ORDS.source_type_plsql,
  p_source => '[BEGIN   create_customer(   p_data => :body   );   end;',
  p_items_per_page => 0);
 
  COMMIT;
END;
/
    ) ;

However, when I make a POST request via:

{

"customers": [{

"customer": {

"customer_id": 667,

"customer_first_name": "Mark",

"customer_last_name": "Twain",

"cust_address": "'8768 N State Rd 37','47404','Bloomington','IN','US'",

"phone_numbers": "'+1 812 123 4115'",

"nls_language": "us",

"nls_territory": "America",

"credit_limit": "234",

"cust_email": "FooBar@example.com",

"account_mgr_id": 139,

"cust_lat": -80.32,

"cust_lon": 39.849678

}

}]

}

I get back a pretty (seemingly) generic error in the ORDS debug:

May 14, 2018 10:44:48 PM

INFO: Error occurred during execution of: [CALL, [BEGIN

create_customer(

p_data => /*in:body*/?

);

end;, [body, in, class oracle.dbtools.common.stmt.UnknownParameterType]]with values: {1=HttpInputOverHTTP@4fae429b[c=446,q=0,[0]=null,s=EOF]}

May 14, 2018 10:44:48 PM

INFO: ORA-00900: invalid SQL statement

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:223)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:56)

at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:907)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)

at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1300)

at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3865)

at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3845)

at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4253)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)

at sun.reflect.GeneratedMethodAccessor30.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:367)

at com.sun.proxy.$Proxy43.executeUpdate(Unknown Source)

at oracle.dbtools.common.stmt.jdbc.BindableCallImpl.execute(BindableCallImpl.java:85)

at oracle.dbtools.rest.resource.generator.plsql.PLSQLGenerator.service(PLSQLGenerator.java:85)

at oracle.dbtools.rest.resource.generator.ResourceGeneratorsProvider.generate(ResourceGeneratorsProvider.java:99)

at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:132)

at oracle.dbtools.rest.resource.modules.ModulesDispatcherDelegate.service(ModulesDispatcherDelegate.java:59)

at oracle.dbtools.rest.resource.jdbc.JDBCResourceDispatcher.service(JDBCResourceDispatcher.java:77)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at oracle.dbtools.http.servlet.DispatchableServletBase.service(DispatchableServletBase.java:58)

at oracle.dbtools.rest.resource.cache.CachedResourceDispatcher.service(CachedResourceDispatcher.java:59)

at oracle.dbtools.http.entrypoint.Dispatcher.dispatch(Dispatcher.java:125)

at oracle.dbtools.http.entrypoint.EntryPoint$FilteredServlet.service(EntryPoint.java:239)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:73)

at oracle.dbtools.http.forwarding.QueryFilteringRewrite.doFilter(QueryFilteringRewrite.java:90)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.forwarding.ForwardingFilter.doFilter(ForwardingFilter.java:68)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.cors.CORSPreflightFilter.doFilter(CORSPreflightFilter.java:66)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.cookies.auth.CookieSessionCSRFFilter.doFilter(CookieSessionCSRFFilter.java:71)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.auth.AuthenticationFilter.authenticate(AuthenticationFilter.java:100)

at oracle.dbtools.http.auth.AuthenticationFilter.doFilter(AuthenticationFilter.java:64)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.url.mapping.RequestMapperImpl.doFilter(RequestMapperImpl.java:161)

at oracle.dbtools.url.mapping.URLMappingBase.doFilter(URLMappingBase.java:96)

at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:127)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.auth.external.ExternalSessionFilter.doFilter(ExternalSessionFilter.java:59)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.cors.CORSResponseFilter.doFilter(CORSResponseFilter.java:83)

at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.filters.AbsoluteLocationFilter.doFilter(AbsoluteLocationFilter.java:65)

at oracle.dbtools.http.filters.HttpResponseFilter.doFilter(HttpResponseFilter.java:45)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.errors.ErrorPageFilter.doFilter(ErrorPageFilter.java:85)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.secure.ForceHttpsFilter.doFilter(ForceHttpsFilter.java:72)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.auth.ForceAuthFilter.doFilter(ForceAuthFilter.java:44)

at oracle.dbtools.http.filters.HttpFilter.doFilter(HttpFilter.java:47)

at oracle.dbtools.http.filters.FilterChainImpl.doFilter(FilterChainImpl.java:64)

at oracle.dbtools.http.filters.Filters.filter(Filters.java:65)

at oracle.dbtools.http.entrypoint.EntryPoint.service(EntryPoint.java:81)

at oracle.dbtools.http.entrypoint.EntryPointServlet.service(EntryPointServlet.java:102)

at oracle.dbtools.entrypoint.WebApplicationRequestEntryPoint.service(WebApplicationRequestEntryPoint.java:50)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:860)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:535)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)

at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1595)

at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1253)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:473)

at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1564)

at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1155)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)

at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:219)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:126)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)

at org.eclipse.jetty.server.Server.handle(Server.java:530)

at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:347)

at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:256)

at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)

at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:102)

at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:247)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.produce(EatWhatYouKill.java:140)

at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:131)

at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:382)

at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:708)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:626)

at java.lang.Thread.run(Thread.java:748)

Caused by: Error : 900, Position : 0, Sql = [BEGIN

create_customer(

p_data => :1

);

end;, OriginalSql = [BEGIN

create_customer(

p_data => ?

);

end;, Error Msg = ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)

... 104 more

May 14, 2018 10:44:48 PM

SEVERE: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

InternalServerException [statusCode=500, reasons=[]]

at oracle.dbtools.http.errors.jdbc.JDBCServletResponseExceptions.handle(JDBCServletResponseExceptions.java:54)

at oracle.dbtools.rest.resource.generator.plsql.PLSQLGenerator.service(PLSQLGenerator.java:116)