This discussion is archived
7 Replies Latest reply: Oct 16, 2013 2: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 Newbie
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Employee ACE
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points