TED_APEX_ITEMS

package ted_apex_items is

​

​

​

type t_items_row is record

  ( name varchar2(30)

  , type varchar2(12)

  , line number

  , text varchar2(4000)

  , item_name varchar2(100)

  , item_position number

  )

;

​

type t_items_tab is table of t_items_row;

​

​

​

function user_source_items

  return t_items_tab

  pipelined

  deterministic

;

​

​

​

end ted_apex_items;

​

TED_APEX_ITEMS

package body ted_apex_items is

​

​

function user_source_items

  return t_items_tab

  pipelined

  deterministic

is

​

  cursor c1 is

    -- all user_source lines containing a v('%') construct

    select s.*

    from user_source s

    where regexp_count( s.text, 'v\( *''[^'']*'' *\)' ) > 0

      -- ### NOTE: This regexp_count misses lines like: v('P' || trim(p_page_id) || '_ID');

      -- ### You get those if you use this where clause: where regexp_like( s.text, 'v\( *''.*'' *\)' )

    and   not (s.name = $$PLSQL_UNIT and s.TYPE like 'PACKAGE%') -- skip the current package

    order by 1,2,3

  ;

​

  v_item_position_0 pls_integer;

  v_item_position   pls_integer;

  v_item_name       varchar2(1000);

​

  v_items_row       t_items_row;

​

  v_is_ok           boolean;

​

begin

​

  for c1_rec in c1 loop

​

    v_items_row := null;

    v_items_row.name := c1_rec.name;

    v_items_row.type := c1_rec.type;

    v_items_row.line := c1_rec.line;

    v_items_row.text := c1_rec.text;

​

    v_item_position_0 := 1;

​

    -- loop all v('%') constructs

    loop

​

      -- get the next v('%') construct position

      v_item_position := regexp_instr ( c1_rec.text, 'v\( *''[^'']*'' *\)', v_item_position_0 );

​

      -- exit loop if no v('%') construct is found

      if v_item_position = 0 then exit; end if;

​

      -- get the v('%') construct string

      v_item_name := regexp_substr( c1_rec.text, 'v\( *''[^'']*'' *\)', v_item_position );

​

      -- clean up v_item_name

      v_item_name := upper( replace( regexp_substr( v_item_name, '''[^'']*''' ), '''', '' ) );

​

​

      -- Determine v_is_ok

      v_is_ok := true;

      -- Global variables from APEX_APPLICATION package (Apex version 4.2)

      if v_item_name in

        ( 'G_USER'

        , 'G_FLOW_ID'

        , 'G_FLOW_STEP_ID'

        , 'G_FLOW_OWNER'

        , 'G_REQUEST'

        , 'G_BROWSER_LANGUAGE'

        , 'G_DEBUG'

        , 'G_HOME_LINK'

        , 'G_LOGIN_URL'

        , 'G_IMAGE_PREFIX'

        , 'G_FLOW_SCHEMA_OWNER'

        , 'G_PRINTER_FRIENDLY'

        , 'G_PROXY_SERVER'

        , 'G_SYSDATE'

        , 'G_PUBLIC_USER'

        , 'G_GLOBAL_NOTIFICATION'

        )

      then

         v_is_ok := false;

      end if;

      -- Built-in Substitution Strings (Apex version 4.2)

      if v_item_name in

        ( 'APEX$ROW_NUM'

        , 'APEX$ROW_SELECTOR'

        , 'APEX$ROW_STATUS'

        , 'APP_ID'

        , 'APP_ALIAS'

        , 'APP_DATE_TIME_FORMAT'

        , 'APP_IMAGES'

        , 'APP_NLS_DATE_FORMAT'

        , 'APP_NLS_TIMESTAMP_FORMAT'

        , 'APP_NLS_TIMESTAMP_TZ_FORMAT'

        , 'APP_IMAGES'

        , 'APP_PAGE_ID'

        , 'APP_SESSION'

        , 'SESSION' -- is an alias of APP_SESSION

        , 'APP_SESSION_VISIBLE'

        , 'APP_UNIQUE_PAGE_ID'

        , 'APP_USER'

        , 'AUTHENTICATED_URL_PREFIX'

        , 'BROWSER_LANGUAGE'

        , 'CURRENT_PARENT_TAB_TEXT'

        , 'DEBUG'

        , 'HOME_LINK'

        , 'IMAGE_PREFIX'

        , 'LOGIN_URL'

        , 'LOGOUT_URL'

        , 'PRINTER_FRIENDLY'

        , 'PROXY_SERVER'

        , 'PUBLIC_URL_PREFIX'

        , 'REQUEST'

        , 'SCHEMA OWNER'

        , 'SQLERRM'

        , 'SYSDATE_YYYYMMDD'

        , 'WORKSPACE_IMAGES'

        )

      then

         v_is_ok := false;

      end if;

      -- Other prefixes that look like "v"

      if substr( c1_rec.text, v_item_position-length('owa_util.get_cgi_env')+1, length('owa_util.get_cgi_env') ) = 'owa_util.get_cgi_env' then

         v_is_ok := false;

      end if;

​

​

      -- all is ok

      if v_is_ok = true then

        -- get the "exact" v_item_position

        v_item_position := regexp_instr ( c1_rec.text, v_item_name, v_item_position, 1, 0, 'i' );

        -- fill and pipe v_items_row

        v_items_row.item_name     := v_item_name;

        v_items_row.item_position := v_item_position;

        pipe row(v_items_row);

      end if;

​

      -- set the search position for the next iteration of the loop to the current position + 1

      v_item_position_0 := v_item_position + 1;

​

    end loop;

​

  end loop;

​

end;

​

​

​

end ted_apex_items;

​