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.......
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
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;
create or replace PACKAGE BODY pk_kl_test IS
TYPE t_rec IS RECORD (
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
user_result kl_test_type := kl_test_type(NULL,NULL);
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');
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;
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.
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?
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)
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; /
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!)
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.
Member of the APEX development team
My Blog: http://www.inside-oracle-apex.com
APEX Plug-Ins: http://apex.oracle.com/plugins
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
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.