10 Replies Latest reply on Jul 16, 2010 6:26 AM by eric henrard

    Problem executing Stored procedure with Apex Listener 1.10.179.10.43

    eric henrard
      Hi,

      I have some problem to run stored procedure with parameter (works without parameter)
      this works with APEX LISTENER version 0.10.110.10.57. (tested on tomcat and on Oracle GlassFish Server 3.0.1)

      Regards,

      call is done via url like :
      DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA?p_col_nam=RCH_MIG_DDL_DCT&p_MIG_SCH_SVC_REQ_IDN=1182&p_ext=.sql

      receive : HTTP Status 500

      APEX Listener version : 1.10.179.10.43

      ORA-06550: line 2, column 2:
      PL/SQL: Statement ignored
      init: # headers=45
      declare nm owa.vc_arr := ?;
      vl owa.vc_arr := ?;
      begin
      owa.init_cgi_env( ?, nm, vl );
      htp.init; htp.HTBUF_LEN := 63;
      ? := sys_context('USERENV','SID');
      end;
      SID:492
      CALL:
      begin
      DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
      p_col_nam=>?);
      commit;
      end;
      BINDS
      p_ext:.sql
      p_col_nam:RCH_MIG_DDL_DCT
      p_ext:.sql
      p_col_nam:RCH_MIG_DDL_DCT
      EXEC FAILED:ORA-06550: line 2, column 2:
      PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
      ORA-06550: line 2, column 2:
      PL/SQL: Statement ignored
      ORA-06550: line 2, column 2:
      PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
      ORA-06550: line 2, column 2:
      PL/SQL: Statement ignored



      [#|2010-07-05T14:21:54.613+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=23;_ThreadName=Thread-1;|_init_: # headers=45
      declare nm owa.vc_arr := ?;
      vl owa.vc_arr := ?;
      begin
      owa.init_cgi_env( ?, nm, vl );
      htp.init; htp.HTBUF_LEN := 63;
      ? := sys_context('USERENV','SID');
      end;
      SID:485
      CALL:
      begin
      DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
      p_col_nam=>?);
      commit;
      end;
      BINDS

      p_ext:.sql
      p_col_nam:CRO_MIG_DDL_DCT
      p_ext:.sql
      p_col_nam:CRO_MIG_DDL_DCT
      EXEC FAILED:ORA-06550: line 2, column 2:
      PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
      ORA-06550: line 2, column 2:
      PL/SQL: Statement ignored
      |#]

      [#|2010-07-05T14:21:54.617+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=23;_ThreadName=Thread-1;|ORA-06550: line 2, column 2:
      PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
      ORA-06550: line 2, column 2:
      PL/SQL: Statement ignored
      |#]
        • 1. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
          Kris Rice-Oracle
          The production version of the listener added validation on the args for a procedure. It checks between all_args and the query string to ensure only args present in the signature are bound in. The only thing I can think of is if this an overloaded proc there could be something. what does the signature of this proc look like ?
          -kris
          • 2. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
            eric henrard
            Kris,

            Here is the tag of the procedure (inside a package):

            PROCEDURE DL_DRATB_SCH_MIG_TEM_DTA
            Argument Name Type In/Out Default?
            P_COL_NAM VARCHAR2 IN
            P_MIG_SCH_SVC_REQ_IDN NUMBER IN
            P_EXT VARCHAR2 IN DEFAULT

            may be the problem is that this procedure is called trought a public synonym?

            Here also the output of dba_arguments for this proc:

            select * from dba_arguments where PACKAGE_NAME='DRAP_APX_PUB' and object_name ='DL_DRATB_SCH_MIG_TEM_DTA';

            OWNER OBJECT_NAME PACKAGE_NAME OBJECT_ID
            OVERLOAD SUBPROGRAM_ID ARGUMENT_NAME POSITION SEQUENCE DATA_LEVEL
            DATA_TYPE DEFAULT_VALUE
            DEFAULT_LENGTH IN_OUT DATA_LENGTH DATA_PRECISION DATA_SCALE RADIX CHARACTER_SET_NAME
            TYPE_OWNER TYPE_NAME TYPE_SUBNAME
            TYPE_LINK
            PLS_TYPE CHAR_LENGTH C

            DRAF02P DL_DRATB_SCH_MIG_TEM_DTA DRAP_APX_PUB 122449
            11 P_EXT 3 3 0
            VARCHAR2
            IN CHAR_CS


            VARCHAR2 B

            DRAF02P DL_DRATB_SCH_MIG_TEM_DTA DRAP_APX_PUB 122449
            11 P_MIG_SCH_SVC_REQ_IDN 2 2 0
            NUMBER
            IN 22 10


            NUMBER 0 0

            DRAF02P DL_DRATB_SCH_MIG_TEM_DTA DRAP_APX_PUB 122449
            11 P_COL_NAM 1 1 0
            VARCHAR2
            IN CHAR_CS


            VARCHAR2 B


            Regards,

            Eric
            • 3. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
              Kris Rice-Oracle
              Eric,
              Can you turn debugging on in the listener and post/send me the log from that. It's obviously not finding P_MIG_SCH_SVC_REQ_IDN for some reason. but I can't see a reason why from this.

              In the apex-config.xml add a property:
              <entry key="apex.debug.debugger">true</entry>



              -kris
              • 4. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                eric henrard
                Kris,

                Here the output, I hope this can help you.

                Thanks in advance.

                Regards,

                Eric


                SEVERE: A web application registered the JBDC driver [oracle.jdbc.OracleDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
                APEX Listener version : 1.10.179.10.43
                APEX Listener server info: Apache Tomcat/6.0.26
                Using Config file:/u01/app/apex/apache-tomcat-6.0.26/temp/apex/apex-config.xml
                -- listing properties --
                PropertyCheckInterval=60
                ValidateConnection=true
                MinLimit=1
                MaxLimit=10
                InitialLimit=3
                AbandonedConnectionTimeout=900
                MaxStatementsLimit=10
                InactivityTimeout=1800
                MaxConnectionReuseCount=50000
                ==== doGet()====
                isValidRequest(), procedure name:
                Validating:DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA
                *** Add procedure to cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTAAddProc: 390 ms*** Total number of arguments: 3SID:1099Parse: 0 ms
                -----
                begin
                DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
                p_col_nam=>?);
                commit;
                end;
                *** Found procedure in cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA*** Total number of arguments: 3p_ext=.sql
                p_col_nam=FNL_RCH_MIG_DDL_DCT
                #### Error occurred in Signature for procedure:DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA. Reloading...
                *** RELOADING procedure in cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTAReload: 194 ms*** Found procedure in cache: DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA*** Total number of arguments: 3p_ext=.sql
                p_col_nam=FNL_RCH_MIG_DDL_DCT
                init: # headers=45
                declare nm owa.vc_arr := ?;
                vl owa.vc_arr := ?;
                begin
                owa.init_cgi_env( ?, nm, vl );
                htp.init; htp.HTBUF_LEN := 63;
                ? := sys_context('USERENV','SID');
                end;
                SID:1099
                CALL:
                begin
                DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
                p_col_nam=>?);
                commit;
                end;
                BINDS
                p_ext:.sql
                p_col_nam:FNL_RCH_MIG_DDL_DCT
                p_ext:.sql
                p_col_nam:FNL_RCH_MIG_DDL_DCT
                EXEC FAILED:ORA-06550: line 2, column 2:
                PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
                ORA-06550: line 2, column 2:
                PL/SQL: Statement ignored
                ORA-06550: line 2, column 2:
                PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
                ORA-06550: line 2, column 2:
                PL/SQL: Statement ignored
                Got results length:NULL
                • 5. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                  Kris Rice-Oracle
                  Eric,
                  Can you try this? In the admin screens, on the Security tab. There is a Security Setting section and in there is a button to Clear Cache. I see the error that the loading the signature from the cache failed so I'm guessing that maybe that cache is invalid/corrupted. I'll take a closer look at the code but give this try and let me know.

                  -kris
                  • 6. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                    Liz Saunders-Oracle
                    Eric,

                    You mentioned that
                    "may be the problem is that this procedure is called through a public synonym".

                    What is your public synonym pointing to?

                    Liz
                    • 7. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                      eric henrard
                      Liz,

                      Here the synonym :
                      SQL> select * from dba_synonyms where synonym_name like 'DRAP_APX_PUB';

                      OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
                      DB_LINK
                      PUBLIC DRAP_APX_PUB DRAF02P DRAP_APX_PUB

                      Regards,

                      Eric
                      • 8. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                        eric henrard
                        Kris,

                        I see the button, but it is grised.

                        I put apex.cache.caching to true (+restart app) and same thing, the button is disabled. May be there are nothing to clear?

                        I tried also to give to complete name for the proc and ... always the same error message.

                        Regards,

                        Eric

                        ==== doGet()====

                        |#]

                        [#|2010-07-12T10:28:24.637+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|
                        isValidRequest(), procedure name: <DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA>

                        |#]

                        [#|2010-07-12T10:28:24.637+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Validating:DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA

                        |#]

                        [#|2010-07-12T10:28:24.651+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Add procedure to cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

                        [#|2010-07-12T10:28:24.731+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|AddProc: 80 ms|#]

                        [#|2010-07-12T10:28:24.731+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Total number of arguments: 3|#]

                        [#|2010-07-12T10:28:24.735+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|SID:493|#]

                        [#|2010-07-12T10:28:24.735+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Parse: 0 ms|#]

                        [#|2010-07-12T10:28:24.735+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|
                        -----
                        begin
                        DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
                        p_col_nam=>?);
                        commit;
                        end;

                        |#]

                        [#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Found procedure in cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

                        [#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Total number of arguments: 3|#]

                        [#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_ext=.sql

                        |#]

                        [#|2010-07-12T10:28:24.736+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_col_nam=RCH_MIG_DDL_DCT

                        |#]

                        [#|2010-07-12T10:28:24.743+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|#### Error occurred in Signature for procedure:DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA. Reloading...

                        |#]

                        [#|2010-07-12T10:28:24.744+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** RELOADING procedure in cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

                        [#|2010-07-12T10:28:24.826+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|Reload: 81 ms|#]

                        [#|2010-07-12T10:28:24.826+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Found procedure in cache: DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA|#]

                        [#|2010-07-12T10:28:24.826+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|*** Total number of arguments: 3|#]

                        [#|2010-07-12T10:28:24.827+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_ext=.sql

                        |#]

                        [#|2010-07-12T10:28:24.827+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|p_col_nam=RCH_MIG_DDL_DCT

                        |#]

                        [#|2010-07-12T10:28:24.834+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|_init_: # headers=44
                        declare nm owa.vc_arr := ?;
                        vl owa.vc_arr := ?;
                        begin
                        owa.init_cgi_env( ?, nm, vl );
                        htp.init; htp.HTBUF_LEN := 63;
                        ? := sys_context('USERENV','SID');
                        end;
                        SID:493
                        CALL:
                        begin
                        DRAF02P.DRAP_APX_PUB.DL_DRATB_SCH_MIG_TEM_DTA(p_ext=>?,
                        p_col_nam=>?);
                        commit;
                        end;
                        BINDS

                        p_ext:.sql
                        p_col_nam:RCH_MIG_DDL_DCT
                        p_ext:.sql
                        p_col_nam:RCH_MIG_DDL_DCT
                        EXEC FAILED:ORA-06550: line 2, column 2:
                        PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
                        ORA-06550: line 2, column 2:
                        PL/SQL: Statement ignored
                        |#]

                        [#|2010-07-12T10:28:24.834+0200|SEVERE|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|ORA-06550: line 2, column 2:
                        PLS-00306: wrong number or types of arguments in call to 'DL_DRATB_SCH_MIG_TEM_DTA'
                        ORA-06550: line 2, column 2:
                        PL/SQL: Statement ignored
                        |#]

                        [#|2010-07-12T10:28:24.836+0200|INFO|glassfish3.0.1|javax.enterprise.system.std.com.sun.enterprise.v3.services.impl|_ThreadID=24;_ThreadName=Thread-1;|
                        Got results length:NULL

                        |#]
                        • 9. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                          785310
                          Hi,

                          I'm testing the new apex listener to see if we can use it for our modplsql needs. I think I've come across the same problem as reported in this thread.

                          I think it's because the parameter name is given in uppercase letters. Below is my console output after I made a small debugging filter to investigate what the apex listener is doing. As one can see, when the parameter name is given in uppercase, the servlet gets it in uppercase from the call "getQuerysTring", but then later call "getParameterValues" with lowercase parameter name and that returns null.

                          If i do the same call with lowercase parameter name it succeeds.

                          ==========================
                          2010-07-16 07:43:31.734:INFO::jetty-7.1.4.v20100610
                          2010-07-16 07:43:31.765:INFO::Deployment monitor D:\jetty3\contexts at interval 5
                          2010-07-16 07:43:31.765:INFO::Deployable added: D:\jetty3\contexts\javadoc.xml
                          2010-07-16 07:43:31.796:INFO::Deployable added: D:\jetty3\contexts\apexlistener.xml
                          2010-07-16 07:43:32.374:INFO::Deployment monitor D:\jetty3\webapps at interval 5
                          2010-07-16 07:43:32.374:INFO::Deployable added: D:\jetty3\webapps\i
                          2010-07-16 07:43:32.452:INFO::Started SelectChannelConnector@0.0.0.0:9090

                          DEBUG JOSTEIN: lowercasefilter doFilter
                          APEX Listener version : 1.10.179.10.43
                          APEX Listener server info: jetty/7.1.4.v20100610
                          Using Config file:D:\jetty3\tmp\\webapps\apex-config.xml
                          -- listing properties --
                          PropertyCheckInterval=60
                          ValidateConnection=true
                          MinLimit=1
                          MaxLimit=10
                          InitialLimit=3
                          AbandonedConnectionTimeout=900
                          MaxStatementsLimit=10
                          InactivityTimeout=1800
                          MaxConnectionReuseCount=50000


                          ==== doGet()====


                          isValidRequest(), procedure name: <felles.pkg_testing.p_paramtest>


                          DEBUG JOSTEIN: getQueryString - TXT=hdDDfsalla
                          Validating:felles.pkg_testing.p_paramtest

                          *** Add procedure to cache: felles.pkg_testing.p_paramtestAddProc: 281 ms*** Total number of arguments: 1
                          ** checkRequestValidationFunction(), <felles.pkg_testing.p_paramtest> exclude=false


                          DEBUG JOSTEIN: getQueryString - TXT=hdDDfsalla

                          DEBUG JOSTEIN: getQueryString - TXT=hdDDfsalla
                          SID:80
                          DEBUG JOSTEIN: getParameterValues - txt = null!!!
                          Parse: 0 ms
                          -----
                          begin
                          felles.pkg_testing.p_paramtest;
                          commit;
                          end;

                          *** Found procedure in cache: felles.pkg_testing.p_paramtest*** Total number of arguments: 1#### Error occu
                          rred in Signature for procedure:felles.pkg_testing.p_paramtest. Reloading...

                          *** RELOADING procedure in cache: felles.pkg_testing.p_paramtestReload: 141 ms*** Found procedure in cache:
                          felles.pkg_testing.p_paramtest*** Total number of arguments: 1_init_: # headers=44
                          declare nm owa.vc_arr := ?;
                          vl owa.vc_arr := ?;
                          begin
                          owa.init_cgi_env( ?, nm, vl );
                          htp.init; htp.HTBUF_LEN := 63;
                          ? := sys_context('USERENV','SID');
                          end;
                          SID:80
                          CALL:
                          begin
                          felles.pkg_testing.p_paramtest;
                          commit;
                          end;
                          BINDS

                          EXEC FAILED:ORA-06550: linje 2, kolonne 2:
                          PLS-00306: feil antall eller type argumenter i kall til P_PARAMTEST
                          ORA-06550: linje 2, kolonne 2:
                          PL/SQL: Statement ignored
                          ORA-06550: linje 2, kolonne 2:
                          PLS-00306: feil antall eller type argumenter i kall til P_PARAMTEST
                          ORA-06550: linje 2, kolonne 2:
                          PL/SQL: Statement ignored


                          Got results length:NULL
                          ==========================


                          /Jostein.
                          • 10. Re: Problem executing Stored procedure with Apex Listener 1.10.179.10.43
                            eric henrard
                            Hi Jostein,

                            Thanks for the update, I tried to put all parameters in lowercase and it's work

                            I will use this workarround to put new version of Apex Listener.

                            Thanks for Help.

                            Regards,

                            Eric.