Thursday 28 February 2019

APEX configuration problem - empty stack trace

This post is just here to save someone a few hours debugging one day.

We were trying to launch a new Tomcat instance, but we were facing a strange error.

There was no stack trace, and the debug trace started with
[TE] url-mapping start:
[TE] get /ords/f?p=ABC start:


Turns out we had listed some hostnames in the workspace isolation feature, in preparation for deep linking.


Our new server was not listed.

Function based tables

You've probably seen this somewhere already, no doubt from Connor, though I couldn't find much beyond Tim's post on pipelined functions - I can't find the right keywords to find related content.

I like table functions, so this will help me remember we no longer need to specify the table() operator in 18c (12.2).

12c

select * 
from table(
  apex_string.split('A,B,C',',')
);

Result Sequence                                                                                                                      
---------------
A
B
C

12c> select * from apex_string.split('A,B,C',',');

ORA-00933: SQL command not properly ended

18c

select * from apex_string.split('A,B,C',',');

Result Sequence                                                                                                                      
---------------
A
B
C
This apex_string package is like the swiss army knife of string manipulation. I love it.
I think I'm surprised a few other posts haven't made it out of draft. This example felt a little like butchery, but it was an interesting play.

Here's a way to test it out with your own function, returning a supplied APEX collection type - a table/collection of strings.

create or replace function tf_test return apex_t_varchar2 is
  lt   apex_t_varchar2 := apex_t_varchar2(); -- ORA-06531: Reference to uninitialized collection
begin 
 for i in 1..12 loop
   lt.extend; -- ORA-06533: Subscript beyond count
   lt(lt.last) := add_months(trunc(sysdate,'yy'),i-1);
 end loop;

  return (lt);
end;
/ 

select column_value as dt 
from tf_test();

DT
---------
01/JAN/19
01/FEB/19
01/MAR/19
01/APR/19
01/MAY/19
01/JUN/19
01/JUL/19
01/AUG/19
01/SEP/19
01/OCT/19
01/NOV/19
01/DEC/19

12 rows selected
If you don't include the section with comments, you get the relevant error.

We need the trailing brackets even in the absence of actual parameters. We didn't in the old format, but I couldn't find the updated syntax diagram.

select * from tf_test;
-- ORA-04044: procedure, function, package, or type is not allowed here


We can subtract more code, just not the brackets ;p