Upper-case double quoted objectnames

This script might come in handy if you also didn’t want the free double quoted upper-case object names in the object scripts on the DB, which you get importing and exporting a schema.

The script goes trough the user_source view and reconstructs a file with scripts to create or replace your current objects (packages, types, functions, procedures and triggers).
While constructing the file the first line of each script is altered to the way I wanted it in this case:

 CREATE OR REPLACE <TYPE> <objectname>

The script assumes that the objects all have a certain prefix.

SET heading OFF
SET feedback OFF
SET VERIFY OFF
SET echo OFF
SET termout OFF
SET pages 0
SET linesize 225
SET define ON

COLUMN sort1 noprint
COLUMN sort2 noprint
COLUMN sort3 noprint

-->> ADJUST PREFIX
define schema_pfx="'UBS'"

spool old_sources.sql

prompt SET termout ON
prompt SET heading ON
prompt SET verify ON
prompt SET feedback ON
prompt SET trimspool ON
prompt SET define OFF
prompt
prompt spool old_sources.log
prompt

SELECT name sort1
,      type sort2
,      line sort3
,      CASE WHEN line = 1
            THEN 'PROMPT' || CHR (13)
              || 'PROMPT Create/alter ' || TYPE || ' ' || NAME || ' ...' || CHR (13)
              || 'CREATE OR REPLACE ' || text
            --
            WHEN line = last_line
            THEN CHR (13) || '/' || CHR (13)
            --
            ELSE text
       END new_text
FROM  (
        SELECT t.*
        ,      max(line) over(PARTITION BY name, type) last_line
        FROM   user_source t
        WHERE  NAME LIKE &schema_pfx || '_%'
        AND    TYPE IN ('TYPE', 'TYPE BODY', 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY')
      )
ORDER  BY sort1
        , sort2
        , sort3
/

prompt spool off

spool off

spool new_sources.sql

prompt SET termout ON
prompt SET heading ON
prompt SET verify ON
prompt SET feedback ON
prompt SET trimspool ON
prompt SET define OFF
prompt
prompt spool new_sources.log
prompt

SELECT NAME sort1
,      TYPE sort2
,      line sort3
,      CASE WHEN  line = 1
            THEN  'PROMPT' || CHR (13)
               || 'PROMPT Create/alter ' || TYPE || ' ' || NAME || ' ...' || CHR (13)
               || 'CREATE OR REPLACE '
               || CASE WHEN  quote1 > 0 -- in case of "SCHEMA"."OBJECTNAME"
                         AND quote4 > 0
                       THEN REPLACE( text
                                   , SUBSTR( text, quote1, (quote4-quote1)+1 )
                                   , LOWER( NAME ) )
                       --
                       WHEN  quote1 > 0 -- in case of "OBJECTNAME"
                         AND quote2 > 0
                       THEN REPLACE( text, SUBSTR( text, quote1, (quote2-quote1)+1 ), LOWER( name ) )
                       --
                       ELSE  -- no quotes, objectname maybe uppercase
                            REPLACE( text, name, LOWER( name ) )
                  END
            --
            WHEN line = last_line -- last line contains code
              AND LENGTH( TRIM( replace( replace( text, CHR (13), ''), CHR (10), '') )) > 0
            THEN ltrim(text) || CHR (13) || '/' || CHR (13)
            --
            WHEN line = last_line -- last line contains no actual code
            THEN '/' || CHR (13)
            --
            ELSE text
       END new_text
FROM  (
        SELECT r.*
        ,      max(line) over(PARTITION BY name, type) last_line
        FROM  (
                SELECT t.*
                ,      INSTR( text, CHR (34),1, 1 ) quote1
                ,      INSTR( text, CHR (34),1, 2 ) quote2
                ,      INSTR( text, CHR (34),1, 4 ) quote4
                FROM   user_source t
                WHERE  NAME LIKE &schema_pfx || '_%'
                AND    TYPE IN ('TYPE', 'TYPE BODY', 'PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY')
              ) r
      )
ORDER  BY sort1
        , sort2
        , sort3
/

prompt spool off

spool off

undefine schema_pfx

ApEx function wwv_flow.accept giving a 'page not found'

One of the applications I’ve build had the weirdest bug:
when navigating through the application the wwv_flow.accept sometimes gave a ‘page not found’ error.
I also had the error while developing the application navigating through apex itself.

The apache log showed:
mod_plsql: /pls/toolsdb/wwv_flow.accept HTTP-404 ORA-06550: line 22, column 3:\nPLS-00306: wrong number or types of arguments in call to ‘ACCEPT’\nORA-06550: line 22, column 3:\nPL/SQL: Statement ignored\n

Can’t figure out what it causes but the solution is to schedule a little job with this statement:
ALTER SYSTEM FLUSH SHARED_POOL

Change the field instead of the label when validation fails

Step 1. Adding javascript in the header of the page template

Change the highlighted line to whatever you want to do with the field on error and place this code in the header area of the page template(s) (either directly in the header or via an external file):

var errorItems=new Array();
var emptyArray=new Array();
var indexErrorItems=0;

function processErrorItems(){
  if(indexErrorItems == 0) return;
  for (x in errorItems){
    document.getElementById(errorItems[x]).className = 'InlineError';
  }
  indexErrorItems = 0;
  errorItems = emptyArray;
}

function addErrorItem(item){
    errorItems[indexErrorItems] = item;
    indexErrorItems++;
}


Step 2. Adding javascript in the footer of the page template

Add this code in the footer of the page template(s):

<script type='text/Javascript'>
    processErrorItems();
</script>


Step 3. Adding javascript in the label template

Place this code in the ‘On Error Before Label’ area of you label template(s) and your all set:

<script type="text/Javascript">
addErrorItem('#CURRENT_ITEM_NAME#');
</script>


Step 4. Using it

All you got to do now is
- use the label template on the field you want changed on error (properties of the item)
- choose the option ‘Inline with Field’ or ‘Inline with Field and in Notification’ in the properties of the validations on that field.

UTL_HTTP giving a nasty ORA-24247: network access denied

I got a question today from a colleague.

He had the following code:

declare
    l_response varchar2(4000);
begin
    l_response := UTL_HTTP.REQUEST('http://www.google.com');
end;

Executing this worked fine but incorporating it in a package function gave back an ORA-24247: network access denied by access control list (ACL).

I didn’t hear about ACLs before but listen to the oracle documentation:

“If you have upgraded from a release before Oracle Database 11g Release 1 (11.1), and your applications depend on PL/SQL network utility packages such as UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, and UTL_INADDR, the following error may occur when you try to run the application:
ORA-24247: network access denied by access control list (ACL)”

(http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#BABCBFHD)

The solution is simple:

DECLARE
    l_filenaam varchar2(100) := '<yourfilename>';
    l_host     varchar2(100) := '<the host of the URL your trying to request, in this case: www.google.com>';
BEGIN
    l_filenaam := replace( l_host, '.', '_' );

    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => l_filenaam,
    description  => '<your desctiption like: ACL voor host ' || l_host,
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => null,
    end_date     => null ); 

    DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl         => l_filenaam,
    host        => l_host,
    lower_port  => null,
    upper_port  => null );
END;
/

This is also a nice one:

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
  'power_users.xml'
, 'ACL that lets power users to connect to everywhere'
, 'APEX_030200'
, TRUE
, 'connect' );
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
'power_users.xml'
,'*' );

Pay attention to the fact that the apex schema has prefix “APEX_” now instead of the old ‘FLOWS_’.

Tweaking ApexLib_tabForm for regions wíth a condition

At the moment the package ApexLib_tabform (v2.0.3) skips regions with a condition other then the ‘item is null’ and ‘item is not null’ conditions . This means that the package can’t be used to process a report per record if you have a condition set on the report.

A little tweaking (until it’s in a new release of ApexLib) helps. Alter the package, by changing the following code (procedure init):

vProcess :=
CASE rREG.CONDITION_TYPE
WHEN ‘ITEM_IS_NULL’ THEN ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION1) IS NULL
WHEN ‘ITEM_IS_NOT_NULL’ THEN ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION1) IS NOT NULL
ELSE FALSE
END;

to

vProcess :=
CASE
WHEN rREG.CONDITION_TYPE = ‘ITEM_IS_NULL’
THEN ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION1) IS NULL

WHEN rREG.CONDITION_TYPE = ‘ITEM_IS_NOT_NULL’
THEN ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION1) IS NOT NULL

WHEN upper(rREG.CONDITION_TYPE) = ‘VALUE OF ITEM IN EXPRESSION 1 = EXPRESSION 2′
THEN nvl( ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION1), rREG.CONDITION_EXPRESSION1 )
= nvl( ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION2), rREG.CONDITION_EXPRESSION2 )

WHEN upper(rREG.CONDITION_TYPE) = ‘VALUE OF ITEM IN EXPRESSION 1 != EXPRESSION 2′
THEN nvl( ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION1), rREG.CONDITION_EXPRESSION1 )
!= nvl( ApexLib_Util.getStringSessionValue(rREG.CONDITION_EXPRESSION2), rREG.CONDITION_EXPRESSION2 )

ELSE FALSE
END;

This add’s the conditions ‘value of item in expression 1 = expression 2′ and ‘value of item in expression 1 != expression 2′.
Other conditions can be added in a similar way.

To know which condition_type you have to add use this query:

SELECT ID
     , NAME
     , IGNORE_VALIDATION
     , CONDITION_TYPE
     , CONDITION_EXPRESSION1
     , CONDITION_EXPRESSION2
     , AUTHORIZATION_SCHEME
     , NOT_AUTHORIZATION
FROM   APEXLIB_V_PAGE_REGION REG
WHERE  APPLICATION_ID = <APPLICATION ID>
AND    PAGE_ID        = <PAGE ID>
AND    SOURCE_TYPE    IN ( 'SQL_QUERY'
                         , 'UPDATABLE_SQL_QUERY'
                         , 'FUNCTION_RETURNING_SQL_QUERY'
                         )
AND    USES_APEX_ITEM = ApexLib_Util.NO
ORDER BY
      DISPLAY_SEQUENCE;

Stop apex from processing a page

One in the category ‘too simple, iffff you know the solution’.

I’d made an apex application with an ‘before header’ application process that checked some stuff per page. I programmed my checks and on trouble made a call to owa_util.redirect to redirect to the homepage.

I called a page which had to redirect and strangly the redirect had no effect. The page was normally build and gave a big error I wanted to avoid by redirecting in the first place.

I tried a lot of things and after a couple of hours of frustration and finally a search on the forum I found the solution..
Just one line of code which made a lot of difference:

apex_application.g_unrecoverable_error:=true;

This will tell APEX to stop processing the rest of the page.

AJAX - depending selectlists

Step 1. Create an application item

Navigate to shared components > application items > create > and fill in the next fields:
name <NAME APPL ITEM>

Step 2. Create an application process

Navigate to shared components > application processes > create > and fill in the next fields:
process point “on demand: run this application process when requested by a page process”
name <NAME APPL PROCESS>
type “PL/SQL Anonymous Block”
process text Use the folowing code after altering the cursor c_options and variables l_fsp and if needed also variable l_default:

declare
    lv_checklist_list  varchar2(32000) := '';
    --
    cursor c_options( b_fsp number )
    is
        select <COLUMN DISPLAY VAL> display_value
        ,      <COLUMN RETURN VAL> return_value
        from   <PARENT TABLE> parent
        ,      <CHILD TABEL>     child
        where  child.<FK TO PARENT> = parent.<PK>
        and    parent.<PK> = b_fsp
    ;
    --
    type type_tab_options
    is
        table of c_options%rowtype
    ;
    --
    tab_options type_tab_options;
    --
    l_default varchar2(20) := 'always'; --other option: 'At no result'
    l_fsp     number       := to_number(:<NAME APPL ITEM>);
    --
begin
    open  c_options( l_fsp );
    fetch c_options bulk collect into tab_options;
    close c_options;

    if    l_default = 'always'
    or ( l_default = 'at no result' and tab_options.first is null )
    then
        -- Default value
        lv_checklist_list := ''||
                             '~colsep~'||
                             '-- option --';
    end if;
    --
    -- Determining values
    for i in 1..tab_options.count
    loop
      lv_checklist_list := lv_checklist_list || '~rowsep~' ||
                           tab_options(i).return_value || '~colsep~' ||
                           tab_options(i).display_value;
      --
    end loop;
    --
    -- remove the first ~rowsep~
    if substr(lv_checklist_list, 0, 8 ) like '~rowsep~'
    then
       lv_checklist_list := substr(lv_checklist_list, 9, length(lv_checklist_list));
    end if;
    --
    htp.p(lv_checklist_list);
end;


Step 3. changing the HTML Header of the page with selectlists

Add the folowing code:

<script language="JavaScript" type="text/javascript">
<!--
function getChildList (parent, child)
{
  var childObj = document.getElementById(child);
  var ajaxRequest  = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=<NAME APPL PROCESS>',0);
  ajaxRequest.add('<NAME APPL ITEM>',parent.value);
  ajaxResult = ajaxRequest.get();
  if(ajaxResult)
    {
      childObj.options.length = 0;
      var childArray = ajaxResult.split("~rowsep~");
      for(var i=0; i < childArray.length; i++) {
        var colArray = childArray[i].split("~colsep~");
        childObj.options[i] = new Option(colArray[1], colArray[0]);
      }
    }
    else
    {
     childObj.options.length = 0;
    }
  ajaxRequest = null;
}
//-->
</script>


Step 4. altering the parent selectlist

Add the folowing code to the field HTML Form Element Attributes:

 onChange="javascript:getChildList(this, '<NAME CHILD SELECTLIST>');"


Extra: How to debug if in the child selectlist ‘undefined’ is printed

In the javascript function from step 3, below the line

ajaxResult = ajaxRequest.get();

add the folowing code:

alert(ajaxResult);