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?