7 Replies Latest reply: Oct 16, 2013 4:17 AM by klacey RSS

    apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV

    klacey

      Hi all.. I've got a bit of a problem using a pipelined function as the LOV query for the Plug-Ins mentioned above

       

      Using an LOV such as

      SELECT * from TABLE(my_ldap_package.my_name_search_function('search_value'))

      as the LOV search query (passing some value as the search_value)

      the plugins (both) hang

       

      Skillbuilders Super Lov 2.0.2

      After a number of runs (usually on the 4th run) we get in the "View Debug - Errors" link::

      Error in PLSQL code raised during plug-in processing. ORA-01007: variable not in select list

       

      This is mentioned in a previous post on the oracle forums but I was unsure about how the end user may have solved the problem - using a view for my scenario may not work due to the pipelined function call with a passed search parameter.......

      https://forums.oracle.com/thread/2217166

       

      Enkitec Modal LOV 1.1.0

      After a number of runs (usually on the 4th run) we get in the "View Debug - Errors" link:

      Error in PLSQL code raised during plug-in processing. ORA-20001: ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN_UTIL", line 1337 ORA-06512: at line 430

       

      When running the pipelined function in SQL - we can run it as many times as we want to without seeing an issue - and the errors point to the plug-in processing itself.

      Indeed the error happens during the call to apex_plugin_util.get_data2 when passing the base pipelined LOV query.

       

      I don't seem to be able to reproduce this on Apex 4.2.0.00.27 (which we just migrated our environments from!)

       

      I have created an example application on http://apex.oracle.com/pls/apex/f?p=24655

      Username: publicuser

      Password: Outth3re

       

      If you want to reproduce this error - in your apex workspace

       

      CREATE OR REPLACE TYPE "KL_TEST_TYPE" AS OBJECT

      ( staff_no       VARCHAR2(7),

      display_nm     VARCHAR2(200) )

      /

      CREATE OR REPLACE TYPE "KL_TEST_TAB_TYPE" AS TABLE OF KL_TEST_TYPE

      /

      create or replace PACKAGE pk_kl_test IS

       

        FUNCTION get_users(p_search VARCHAR2) RETURN kl_test_tab_type PIPELINED;

       

      END pk_kl_test;

       

      create or replace PACKAGE BODY pk_kl_test IS

         

        TYPE t_rec IS RECORD (

          sno VARCHAR2(7),

          name VARCHAR2(200) );

        --

        TYPE t_tab IS TABLE of t_rec INDEX BY BINARY_INTEGER;

       

        FUNCTION get_users(p_search VARCHAR2) RETURN kl_test_tab_type PIPELINED

        IS

      user_tab     t_tab;

          user_result kl_test_type := kl_test_type(NULL,NULL);

        BEGIN

          IF p_search IS NOT NULL THEN

            -- fill the test tab type with some test data

            FOR i IN 1..100 LOOP

      user_tab(i).sno := i;

      user_tab(i).name := 'SomeName'||TO_CHAR(i,'000');

            END LOOP;

            --

            FOR i IN 1..100 LOOP

      IF INSTR(user_tab(i).name, p_search) > 0 THEN

      user_result.staff_no   := user_tab(i).sno;

      user_result.display_nm := user_tab(i).name;

      PIPE ROW(user_result);

      END IF;

            END LOOP;

          END IF;

          --

        EXCEPTION

          WHEN no_data_needed THEN

            -- nothing to do in this example... if it were LDAP calls.

            -- this is where you would unbind the ldap session.

            NULL;

        END get_users;

       

      END pk_kl_test;

       

      Create a new Application with a simple HTML region and add a text item to the page

      Import the Enkitec (or Skillbuilders) LOV – use defaults

      Change the type of the text item to Enkitec Modal LOV [Plug-in]

      Set the Display and Return items to 2,2

       

      Set the LOV query of the text item to be:

      SELECT * FROM TABLE(pk_kl_test.get_users(‘1’))

       

      When you run the application – the 4th time you click the LOV – it hangs and the above errors are shown in the View Debug / Errors tab.

       

      Anybody have ideas why this error occurs on 4.2.2 and not 4.2.0?

      Regards

      Kelvin

        • 1. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
          Mike Kutz

          Try running it through DBMS_SQL and see what happens.

          If you have problems there, send everything to Oracle support.

           

          Actually, if you have a good test setup that uses the APEX_PLUGIN.get_data2(), I'd send that to support also.

          • 2. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
            klacey

            Hi Mike.

            Thanks for replying.

             

            Do you mean creating a SQL call to apex_plugin_util.get_data2 with the pipelined LOV query as the select statement in the call -

            in this case SELECT * FROM TABLE(pk_kl_test.get_users('1')) -

            and using this full call as the sql statement passed to dbms_sql?

             

            Would this mean setting up an apex session in a sqlplus session so that I am able to pass an Apex page component name through the call to apex_plugin_util - or would using the SQL Commands section of Apex builder suffice?

            (Sorry, not at work today - may get time to try setting this up at home later to investigate further)

            Regards

            Kelvin

            • 3. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
              Mike Kutz

              Kelvin,

              I was trying to say you should try out the pipelined function by running it through DBMS_SQL yourself.

              From what I've seen,the APEX_PLUGIN_UTIL.GET_DATA* functions are nothing more than specialized wrappers for DBMS_SQL

              So, from a debugging standpoint, let's remove all of the APEX stuff and try it with just DBMS_SQL.

               

              While writing the example code, I noticed that your example pipelined function does not have any RETURN statements.

              The last line of the function should be RETURN; while your EXCEPTION clause should be just RETURN; (and not NULL;)

              That might explain why you are seeing an "infinite loop problem"...

               

              However, I've tried what I was saying but didn't see a problem, so, the problem is NOT with DBMS_SQL

              (This example code is based on Oracle Docs -- DBMS_SQL  )

              declare
                c1  int;
                l_sql varchar2(4000) := 'select staff_no, display_nm from table(pk_kl_test.get_users( :X1 ))';
                l_search varchar2(40) := '10';
                ignore int;
                l_staff_no   varchar2(7);
                l_display_nm varchar2(200);
              begin
                c1 := dbms_sql.open_cursor;
                dbms_sql.parse( c1, l_sql, DBMS_SQL.NATIVE );
                dbms_sql.bind_variable( c1, ':X1', l_search );
                dbms_sql.define_column(c1,1,l_staff_no,7);
                dbms_sql.define_column(c1,2,l_display_nm, 200);
              
                ignore := dbms_sql.execute( c1 );
                loop
                  if dbms_sql.fetch_rows( c1 ) > 0
                  then
                      dbms_sql.column_value( c1, 1, l_staff_no );
                      dbms_sql.column_value( c1, 2, l_display_nm );
                      dbms_output.put_line( l_staff_no || ' -> ' || l_display_nm );
                  else
                     exit;
                  end if;
                end loop;
                dbms_sql.close_cursor( c1 );
              exception
                when others then
                  if dbms_sql.is_open( c1 )
                  then
                    dbms_sql.close_cursor(c1);
                  end if;
                  raise;
              end;
              /
              
              • 4. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
                klacey

                Thanks for the update Mike..

                 

                Bit of a clumsy error me not including the return.

                 

                 

                I see what you mean about dbms_sql now... I interpreted as running the apex_plugin_util function through dbms_sql as I described.

                 

                After incorporating the return in the pipelined function, the "hanging" issue still does occur - much as I wish it didn't.

                I've tried it again through Apex 4.2.0.00.27 where the calls still work... and Apex 4.2.2.00.11 where they don't.

                 

                 

                I think a question to Oracle through MySupport / Company Support Identifier may well be the way forward now as you suggested previously as this does _seem_ to be an issue related to apex_plugin_util.get_data2.

                 

                Thanks for your help (and if you do think of anything else please let me know!)

                Kelvin

                • 5. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
                  Patrick Wolf-Oracle

                  Hi Kelvin,

                   

                  this is indeed a regression in 4.2.2 introduced by a bug fix for bug# 16096118 - apex_plugin_util.get_data* returns apx$rownum column.

                   

                  I think you should still be able to use the VIEW workaround. Try to use the V function in your view to read session state from APEX. For example:

                   

                  create or replace view my_user_lov
                  as
                  SELECT * from TABLE(my_ldap_package.my_name_search_function(V('P1_SEARCH_VALUE')));
                  
                  

                   

                  The bug will be fixed in 4.2.4.

                  Regards

                  Patrick


                  Member of the APEX development team

                  My Blog: http://www.inside-oracle-apex.com

                  APEX Plug-Ins: http://apex.oracle.com/plugins

                  Twitter: http://www.twitter.com/patrickwolf

                  • 6. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
                    klacey

                    Hi Patrick.

                    Thanks for taking the time to reply.

                    I raised an SR with Oracle and there is now a bug raised for this.

                    Bug 17496428 - LOV HANGS AFTER "SOME" INVOCATIONS, DUE TO CALL TO APEX_PLUGIN_UTIL.GET_DATA2


                    I had seen the workaround that you have described.

                    There's usually a "but" though


                    I had tweaked the functionality to dynamically generate the SELECT statement passed to apex_plugin_util.get_data2.

                    I have multiple searched pipelined functions used as the basis for returning the data for the LOV and the choice of function depends on plugin component settings within individual fields - these can obviously be different between fields on the same Apex page.

                    To use the above workaround would require multiple plug-ins with static LOV statements (bar the search item which would be dynamic as you suggest).

                    This unfortunately has the side effect of meaning extra work in the future replacing multiple plug-ins with one plug-in - and amendments to many fields to use the "one" plug-in.


                    However, there is hope. I have come up with an alternative workaround.

                    It's not particularly pretty and there is an incurred visual delay.


                    The dynamic SELECT statement is pre-processed and rows are returned into a Global Temporary Table (preserve rows - delete before starting the query)

                    The static LOV - e.g. SELECT * FROM gtt - is then passed to apex_plugin_util.get_data2.


                    The hanging issue has been removed doing it this way and I can also continue to use a single plugin and dynamically create the SELECT statement based on the same plugin components at individual page item level.

                    In the future when the fix is deployed, I will be able to replace the single plugin - all uses of it within an application will be updated in place (hopefully speeding up testing of live apps)


                    The sooner the fix appears the better though!

                    (some of the searched pipelined functions return a fair bit of data - so sometimes we incur an "acceptable" delay in the LOV display - it's not exactly nice!)

                    Thanks for the information though.. much appreciated

                    Regards

                    Kelvin

                    • 7. Re: apex_plugin_util.get_data2 - Pipelined function Issue in Apex 4.2.2 - Enkitec Modal LOV / SkillBuilder Super LOV
                      klacey

                      Thought I had better close this thread.

                      Although not "answered" as it were, I have a workaround.

                       

                      The raised Oracle bug mentioned earlier (17496428) has been tagged fixed in Apex 4.2.4.00.01

                      So there is an answer - and we will be waiting for the fix to be released.

                       

                      Thanks for the replies.

                      Kelvin