5 Replies Latest reply: Apr 30, 2013 5:25 AM by Colm Divilly RSS

    Restful service unable to insert data using PL/SQL.

    Zack.L
      Hi all,
      Am running: AL 2.01 standalone mode on OEL 4.8 in VM box A.
      Oracle database 10.2.0.4 with Apex 4.2.0.00.27 on OEL4.8 in VM box B.

      Able to performed oracle.example.hr Restful services with no problem.

      Unable to insert data using AL 2.0.1 but works on AL 1.1.4.
      which uses the following table (under schema: scott):
       
      create table json_demo ( title varchar2(20), description varchar2(1000) ); 
      grant all on json_demo to apex_public_user; 
      and below procedure ( scott's schema ):
      CREATE OR REPLACE
      PROCEDURE post(
          p_url     IN VARCHAR2,
          p_message IN VARCHAR2,
          p_response OUT VARCHAR2)
      IS
        l_end_loop BOOLEAN := false;
        l_http_req utl_http.req;
        l_http_resp utl_http.resp;
        l_buffer CLOB;
        l_data       VARCHAR2(20000);  
        C_USER_AGENT CONSTANT VARCHAR2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
      BEGIN
        -- source: http://awads.net/wp/2005/11/30/http-post-from-inside-oracle/
        -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
        -- rather than just returning the text of the error page.
        utl_http.set_response_error_check(false);
        -- Begin the post request
        l_http_req := utl_http.begin_request (p_url, 'POST', utl_http.HTTP_VERSION_1_1);
        -- Set the HTTP request headers
        utl_http.set_header(l_http_req, 'User-Agent', C_USER_AGENT);
        utl_http.set_header(l_http_req, 'content-type', 'application/json;charset=UTF-8');
        utl_http.set_header(l_http_req, 'content-length', LENGTH(p_message));
        -- Write the data to the body of the HTTP request
        utl_http.write_text(l_http_req, p_message);
        -- Process the request and get the response.
        l_http_resp := utl_http.get_response (l_http_req);
        dbms_output.put_line ('status code: ' || l_http_resp.status_code);
        dbms_output.put_line ('reason phrase: ' || l_http_resp.reason_phrase);
        LOOP
          EXIT
        WHEN l_end_loop;
          BEGIN
            utl_http.read_line(l_http_resp, l_buffer, true);
            IF(l_buffer IS NOT NULL AND (LENGTH(l_buffer)>0)) THEN
              l_data    := l_data||l_buffer;
            END IF;
          EXCEPTION
          WHEN utl_http.end_of_body THEN
            l_end_loop := true;
          END;
        END LOOP;
        dbms_output.put_line(l_data);
        p_response:= l_data;
        -- Look for client-side error and report it.
        IF (l_http_resp.status_code >= 400) AND (l_http_resp.status_code <= 499) THEN
          dbms_output.put_line('Check the URL.');
          utl_http.end_response(l_http_resp);
          -- Look for server-side error and report it.
        elsif (l_http_resp.status_code >= 500) AND (l_http_resp.status_code <= 599) THEN
          dbms_output.put_line('Check if the Web site is up.');
          utl_http.end_response(l_http_resp);
          RETURN;
        END IF;
        utl_http.end_response (l_http_resp);
      EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line (sqlerrm);
        raise;
      END;
      and executing in sqldeveloper 3.2.20.09 when connecting directly to box B as scott:
       
      SET serveroutput ON
      DECLARE
        l_url      VARCHAR2(200)   :='http://MY_IP:8585/apex/demo';
        l_json     VARCHAR2(20000) := '{"title":"thetitle","description":"thedescription"}';
        l_response VARCHAR2(30000);
      BEGIN
        post( p_url => l_url, p_message =>l_json, p_response => l_response);
      END;
      which resulted in :
       
      anonymous block completed 
      status code: 200
      reason phrase: OK 
      with data inserted. 
      Setup using 2.0.1
         Workspace : wsdemo
       RESTful Service Module:  demo/
                URI Template:      test
                      Method:  POST
                 Source Type:  PL/SQL
      and executing in sqldeveloper 3.2.20.09 when connecting directly to box B as scott:
       
      SET serveroutput ON
      DECLARE
        l_url      VARCHAR2(200)   :='http://MY_IP:8585//apex/wsdemo/demo/test';
        l_json     VARCHAR2(20000) := '{"title":"thetitle","description":"thedescription"}';
        l_response VARCHAR2(30000);
      BEGIN
        post( p_url => l_url, p_message =>l_json, p_response => l_response);
      END;
      which resulted in :
       
      status code: 500 
      reason phrase: Internal Server Error 
      
      Listener's log: 
      Request Path passes syntax validation
      Mapping request to database pool: PoolMap [_poolName=apex, _regex=null, _workspaceIdentifier=WSDEMO, _failed=false, _lastUpdate=1364313600000, _template=/wsdemo/, _type=BASE_PATH]
      Applied database connection info
      Attempting to process with PL/SQL Gateway
      Not processed as PL/SQL Gateway request
      Attempting to process as a RESTful Service
      demo/test matches: demo/test score: 0
      Choosing: oracle.dbtools.rt.resource.templates.jdbc.JDBCResourceTemplateDispatcher as current candidate with score: Score [handle=JDBCURITemplate [scopeId=null, templateId=2648625079503782|2797815111031405, uriTemplate=demo/test], score=0, scope=SecurityConfig [constraint=none, realm=NONE, logonConfig=LogonConfig [logonForm=null, logonFailed=null]], originsAllowed=[], corsEnabled=true]
      Determining if request can be dispatched as a Tenanted RESTful Service
      Request path has one path segment, continuing processing
      Tenant Principal already established, cannot dispatch
      Chose oracle.dbtools.rt.resource.templates.jdbc.JDBCResourceTemplateDispatcher as the final candidate with score: Score [handle=JDBCURITemplate [scopeId=null, templateId=2648625079503782|2797815111031405, uriTemplate=demo/test], score=0, scope=SecurityConfig [constraint=none, realm=NONE, logonConfig=LogonConfig [logonForm=null, logonFailed=null]], originsAllowed=[], corsEnabled=true] for: POST demo/test
      demo/test is a public resource
      Using generator: oracle.dbtools.rt.plsql.AnonymousBlockGenerator
      Performing JDBC request as: SCOTT
      Mar 28, 2013 1:29:28 PM oracle.dbtools.common.jdbc.JDBCCallImpl execute
      INFO: Error occurred during execution of: [CALL, begin
       insert into scott.json_demo values(/*in:title*/?,/*in:description*/?);
      end;, [title, in, class oracle.dbtools.common.stmt.UnknownParameterType], [description, in, class oracle.dbtools.common.stmt.UnknownParameterType]]with values: [thetitle, thedescription]
      Mar 28, 2013 1:29:28 PM oracle.dbtools.common.jdbc.JDBCCallImpl execute
      INFO: ORA-06550: line 1, column 6:
      PLS-00103: Encountered the symbol "" when expecting one of the following:
      
         begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe
      The symbol "" was ignored.
      ORA-06550: line 2, column 74:
      PLS-00103: Encountered the symbol "" when expecting one of the following:
      
         begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-id
      
      java.sql.SQLException: ORA-06550: line 1, column 6:
      PLS-00103: Encountered the symbol "" when expecting one of the following:
      
         begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe
      The symbol "" was ignored.
      ORA-06550: line 2, column 74:
      PLS-00103: Encountered the symbol "" when expecting one of the following:
      
         begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-id
      
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
              at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
              at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
              at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:505)
              at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:223)
              at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
              at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:205)
              at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1043)
              at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
              at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3612)
              at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3713)
              at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4755)
              at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:597)
              at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:242)
              at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke(PreparedStatementProxyFactory.java:124)
              at oracle.ucp.jdbc.proxy.CallableStatementProxyFactory.invoke(CallableStatementProxyFactory.java:101)
              at $Proxy46.execute(Unknown Source)
              at oracle.dbtools.common.jdbc.JDBCCallImpl.execute(JDBCCallImpl.java:44)
              at oracle.dbtools.rt.plsql.AnonymousBlockGenerator.generate(AnonymousBlockGenerator.java:176)
              at oracle.dbtools.rt.resource.templates.v2.ResourceTemplatesDispatcher$HttpResourceGenerator.response(ResourceTemplatesDispatcher.java:309)
              at oracle.dbtools.rt.web.RequestDispatchers.dispatch(RequestDispatchers.java:88)
              at oracle.dbtools.rt.web.HttpEndpointBase.restfulServices(HttpEndpointBase.java:412)
              at oracle.dbtools.rt.web.HttpEndpointBase.service(HttpEndpointBase.java:162)
              at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
              at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.doFilter(ServletAdapter.java:1059)
              at com.sun.grizzly.http.servlet.ServletAdapter$FilterChainImpl.invokeFilterChain(ServletAdapter.java:999)
              at com.sun.grizzly.http.servlet.ServletAdapter.doService(ServletAdapter.java:434)
              at oracle.dbtools.standalone.SecureServletAdapter.doService(SecureServletAdapter.java:65)
              at com.sun.grizzly.http.servlet.ServletAdapter.service(ServletAdapter.java:379)
              at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
              at com.sun.grizzly.tcp.http11.GrizzlyAdapterChain.service(GrizzlyAdapterChain.java:196)
              at com.sun.grizzly.tcp.http11.GrizzlyAdapter.service(GrizzlyAdapter.java:179)
              at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:849)
              at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:746)
              at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1045)
              at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:228)
              at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
              at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
              at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
              at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
              at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
              at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
              at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
              at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
              at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
              at java.lang.Thread.run(Thread.java:662)
      Error during evaluation of resource template: ORA-06550: line 1, column 6:
      PLS-00103: Encountered the symbol "" when expecting one of the following:
      
         begin case declare exit for goto if loop mod null pragma
         raise return select update while with <an identifier>
         <a double-quoted delimited-identifier> <a bind variable> <<
         close current delete fetch lock insert open rollback
         savepoint set sql execute commit forall merge pipe
      The symbol "" was ignored.
      ORA-06550: line 2, column 74:
      PLS-00103: Encountered the symbol "" when expecting one of the following:
      
         begin case declare end exception exit for goto if loop mod
         null pragma raise return select update while with
         <an identifier> <a double-quoted delimited-id
      Please advise.
      Regards
      Zack
        • 1. Re: Restful service unable to insert data using PL/SQL.
          AndyH
          It sounds like the REST interface hasn't split the JSON data correctly - it's failing during the insert?

          What does the source of the post call look like? Mine is similar to:
          begin
              rest.calculate_json(p_methane                   => :methane
                                           ,p_ethane              => :ethane
                                           ,p_propane             => :propane
                                           ,p_n_butane            => :n_butane
                                           ,p_result              => :result);
          end;
          The IN parameters are automatically bound from the JSON data by the listener, and the OUT parameters are bound through the use of the 'parameters' section as 'OUT' and 'RESPONSE' parameters. Can you 'log' your input parameters to see what they look like?
          • 2. Re: Restful service unable to insert data using PL/SQL.
            Zack.L
            Hi Andy,

            Sorry, forgot to post the Source that's use by both AL1.1.4 and AL2.0.1.

            Source
            begin
             insert into scott.json_demo values(:title,:description);
            end;
            it's failing during the insert?
            Yes, it failed during insert using AL2.0.1.

            Can you 'log' your input parameters to see what they look like?
            Will try it soonest possible and feedback results.

            So your data insert procedure is working normally when using AL2.0.1?

            Regards
            Zack
            • 3. Re: Restful service unable to insert data using PL/SQL.
              Zack.L
              Hi Andy,

              Had used Tyler D. Muth's excellent logger below to log data:
              begin
               logger.log('In template');
               insert into scott.json_demo values(:title,:description);
              end;
              but seems like it never got this far. No data in logger.

              Probably encountered Java's error below :
              INFO: Error occurred during execution of: [CALL, begin
               insert into scott.json_demo values(/*in:title*/?,/*in:description*/?);
              end;,
               [title, in, class oracle.dbtools.common.stmt.UnknownParameterType], 
              [description, in, class oracle.dbtools.common.stmt.
              UnknownParameterType]]with values: [thetitle, thedescription]
              Looks like behaviour is different from AL1.1.4 which execute with no problem.

              Regards
              Zack
              • 4. Re: Restful service unable to insert data using PL/SQL.
                AndyH
                Zack.L wrote:
                Hi Andy,

                Had used Tyler D. Muth's excellent logger below to log data:
                begin
                logger.log('In template');
                insert into scott.json_demo values(:title,:description);
                end;
                Don't understand why there's nothing in the log as that is called before the insert statement.

                Can the logger see the parameters if you comment out in the insert statement?
                Probably encountered Java's error below :
                INFO: Error occurred during execution of: [CALL, begin
                insert into scott.json_demo values(/*in:title*/?,/*in:description*/?);
                end;,
                [title, in, class oracle.dbtools.common.stmt.UnknownParameterType], 
                [description, in, class oracle.dbtools.common.stmt.
                UnknownParameterType]]with values: [thetitle, thedescription]
                Sounds like it is having problems parsing the PL/SQL before it has even accepted the REST call... strange...
                • 5. Re: Restful service unable to insert data using PL/SQL.
                  Colm Divilly
                  Zack.L wrote:
                  Hi Andy,

                  Sorry, forgot to post the Source that's use by both AL1.1.4 and AL2.0.1.

                  Source
                  begin
                  insert into scott.json_demo values(:title,:description);
                  end;
                  it's failing during the insert?
                  Yes, it failed during insert using AL2.0.1.
                  So the above statement produces the following error message:
                  The symbol "" was ignored.
                  ORA-06550: line 2, column 74:
                  PLS-00103: Encountered the symbol "" when expecting one of the following:
                  
                  begin case declare end exception exit for goto if loop mod
                  null pragma raise return select update while with
                  <an identifier> <a double-quoted delimited-id
                  This suggests to me that an unprintable character (notice how there is nothing between the double quotes - "") has worked its way into your PL/SQL Handler. Note how the error is reported to be a column 74 on line 2, yet line 2 of the above block should only have 58 characters, so at a pure guess somehow there's extra whitespace on line 2, that is confusing the PL/SQL compiler, I suggest re-typing the PL/SQL handler manually and seeing if that cures the problem.