Flavio Casetta

Have you ever tried to retrieve a value using SYS_CONTEXT from a user-defined context accessed globally?
Chances are that you will only retrieve NULLs.

After some testing, following a series of inexplicable results, a bell rang in my head pointing to something dealing with a few required values that a procedure launched from an APEX application needed in order to process the data.

It turns out that APEX is setting the CLIENT_IDENTIFIER value in USERENV, that is the value that you can retrieve using function SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER').
If you have a context defined as ACCESSED GLOBALLY, there are some rules explained in the documentation about what you could get given certain conditions (link for Oracle 19c).

At the end of the discussion, the documentation says that if you are getting NULLs, then you might have a problem with the CLIENT_IDENTIFIER and you need to clear it using:

DBMS_SESSION.CLEAR_IDENTIFIER;

It turned out that after clearing the CLIENT_IDENTIFIER, the missing values started finally to appear because, indeed, APEX is setting the CLIENT_IDENTIFIER with a value composed like this:
<APP_USER>:<APP_SESSION>.

Now, in my case it was sound and safe to clear the CLIENT_IDENTIFIER because my process was running in background and the associated Oracle session is closed when the job terminates, but if you need to retrieve these values as part of an interactive process run inside an APEX page, I'd say that you would be better off creating a wrapper function for SYS_CONTEXT where you :

1. save the current CLIENT_IDENTIFIER in a variable
2. clear it using DBMS_SESSION.CLEAR_IDENTIFIER
3. assign the desired value retrieved from the global context to another variable
   
4. restore the original CLIENT_IDENTIFIER using DBMS_SESSION.SET_IDENTIFIER
5. return the desired value.
   

I didn't test this suggestion yet as I am busy with other urgent stuff, so caveat emptor!

Have you ever tried to execute DBMS_ASSERT.SQL_OBJECT_NAME passing a packaged function or procedure?

select dbms_assert.sql_object_name('<SCHEMA>.<PACKAGE>.<whatever>') from dual;

If <SCHEMA>.<PACKAGE> exists, you can write whatever you like and the query will return the input string as it is, that is a valid result.
It will only fail if the object <SCHEMA>.<PACKAGE> does not exist or the user calling the function does not hold sufficient privileges on that object.

ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 472
ORA-06512: at "SYS.DBMS_ASSERT", line 467
44002. 0000 -  "invalid object name"
*Document: Yes
*Cause:    The input parameter string was not a qualified
           SQL identifier of an existing SQL object.
*Action:   Make sure the string matches an existing SQL object that
           is accessible by the current schema.

So, the question is: why DBMS_ASSERT.SQL_OBJECT_NAME accepts fully qualified packaged procedure/function names but it does not check if they actually exist?

I mean, if this function would also verify that the packaged procedure or function exists, would it cause any trouble?

And in that case, why not adding an optional parameter whose default value makes the function work as usual but allows for an extended verification of the packaged procedures/functions?

So, in the end, is this a bug or a feature that I don't fully appreciate?

If you are getting this error while trying to execute DROP INDEX after unsuccessfully trying to create an index with the ONLINE clause:

DROP INDEX xyz;

ORA-08104: this index object 983257 is being online built or rebuilt

then you might need to call function DBMS_REPAIR.ONLINE_INDEX_CLEAN to be able to retry the DROP INDEX statement, however there are two caveats:

1. DBMS_REPAIR is not granted by default to all users, so you probably need to ask the DBA for the grant execute first;
2. When I tried the operation the DBA granted temporarily DBA role to my user, just in case, so I don't know if this is really necessary as the documentation doesn't mention it. 

The first parameter of the function call is the object ID mentioned in the error message.
The second optional parameter wait_for_lock (missing in the code below) enables a retry mechanism in case the underlying table is locked for a short time by some other sessions.

set serveroutput on
DECLARE
  b boolean;
BEGIN 
  b := sys.dbms_repair.online_index_clean(983257);
  if b then
    dbms_output.put_line('cleanup successful');
  else
    dbms_output.put_line('cleanup failed');
  end if;
END;
/

In my case after running the function, I could drop the index successfully.

The "link attributes" of an interactive report allows a developer to specify additional attributes for a column displayed as a link in a interactive report.

A tooltip that will be displayed when a user hovers over the link text with the pointer can be specified using the attribute TITLE, for instance: TITLE="open page xyz".

This column attribute however is not picked up by the translation process of APEX, so it's not something that can be be found in the translation repository.

An easy way to work around the problem if you need to translate the text is as follows:

1. add a new column to the IR report containing:

   APEX_LANG.MESSAGE('MSGOPENXYZ') as MSG1

2. Make the column hidden.
3. Update the LINK ATTRIBUTES column attribute adding TITLE="#MSG1#"
4. Add the message MSGOPENXYZ to the message repository for the main language and for the additional languages.
5. Repeat the steps 1-4 if you have more links needing this.
   
6. Seed you application
7. Publish the application in the additional languages
8. Enjoy the translated tooltip.

 

I was puzzled when I got an error message allegedly occurring at a line containing an "end loop" statement and it took me a while to figure out that this occurs when either bound of the loop is NULL.

In my case both the initial and final bounds are variables and they were supposed to be not null or so I thought...

Here is a code snippet reproducing the error:

begin
  for i in null..100
  loop
    null;
  end loop;
end;
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

So, if you see this error reported at this unusual location, you know what you are up against.

There are at least two ways of configuring facets in a faceted report in a hierarchical fashion, using different attributes for the child facet, one is straightforward, the other one more convoluted, let's find out in this video I prepared for the #JoelKallmanDay to remember a mentor and friend.

 

If you landed here is because you hit the problem of dealing with LONG columns.

There are some good articles elsewhere about how to tackle this old problem, my contribution in this case consists in advising about some limitations that apparently have been overlooked when using the same technique explained below.

It's a mystery to me why after so many years we can't rid of this annoyance once for good, why not "simply" adding a CLOB column equivalent at least in the case of data dictionary columns?
Come on!

I needed to extract the content of the TEXT column from DBA_VIEWS and DBA_MVIEWS, possibly without having to pass through an INSERT into a table (using function TO_LOB), which is the best workaround in case you deal with static data, for one-off operations.

I stumbled upon an old video of Connor McDonald showing how to extract the content of a LONG column exploiting the XML API DBMS_XMLGEN.GETXMLTYPE. This trick seemed to save the day after some adaptation for my case, and actually I was almost ready to celebrate when I started hitting some errors while doing further tests.

To cut a long story short, eventually I encountered the following problems:

1. API documentation for version 19c of DBMS_XMLGEN.SETCONVERTSPECIALCHARS is incorrect as it mentions a parameter "conv" but the real parameter name is "replace". This typo is still present in the latest version of the documentation of 23ai.
   
   
2. DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML won't perform special characters escaping via DBMS_XMLGEN.SETCONVERTSPECIALCHARS if the column type is LONG.
   I was getting parsing errors when using Connor's EXTRACTVALUE technique because the XML document contained < or > as spare characters in the source (as in WHERE conditions inside the query source).
   
   
3.  DBMS_XMLGEN.GETXMLTYPE and DBMS_XMLGEN.GETXML will truncate the content to the first 32K for LONG columns.
   

Problem #1 was easily solved, problem #2 was solved extracting the data using REGEXP_SUBSTR instead of EXTRACTVALUE, but this was possible because I was working on a XML document containing a single ROW tag at a time. For multiple rows this solution will not work.

  FUNCTION long2clob
     ( p_qry in clob,  -- must return a single row!
       p_col in varchar2)
  RETURN CLOB
  IS
    c        CLOB;
  BEGIN
    c := regexp_substr(
           dbms_xmlgen.getxml(p_qry),
           '(<ROW>.*<' || p_col || '>(.*)</' || p_col || '>.*</ROW>)',
           1,
           1,
           'cn'
           ,2 
         );
    return c;

  END long2clob;

Problem #3 remains, unless LONG columns are less than 32K.
Unfortunately we do have some views exceeding 32K of source, but considering the usage of this function I'll probably live with this limitation for the moment.
By the way, SQLDeveloper won't allow you to edit a view larger than 32K, and to me this sounds like an invitation to avoid such situations.

Finally, I also tried to see what happens when you supply a LONG column to function JSON_OBJECT, unfortunately it returns the exception:

ORA-40654: Input to JSON generation function has unsupported data type.

That's all folks!
(quote)

As you probably know, the lines shown in views DBA_ERRORS/ALL_ERRORS/USER_ERRORS in the case of triggers are wrong.

But they are not totally wrong, they are just shifted by the amount of lines between the line containing the keyword TRIGGER and either DECLARE or BEGIN, whichever occurs first.

See the example below for an error reported by the dba_errors view on line 2 caused by missing grants on the table used in the variable declaration:

TRIGGER GAGREA2.TR_ASSEGNAZIONE_AI
   AFTER INSERT ON ASSEGNAZIONE
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
DECLARE
   LI_ID_ASSEGNAZIONE_DOM_RICGEN             GAGREA2.DOMANDA_ASG_SPECIFICHE.ID_ASSEGNAZIONE_DOM_RICGEN%TYPE;
BEGIN

   GAGREA2.PKG_TRIGGER.SET_ID_ASSEGNAZIONE_DOM_RICGEN ( :NEW.ID_DOMANDA, LI_ID_ASSEGNAZIONE_DOM_RICGEN );

END TR_ASSEGNAZIONE_AI;

So, the real line number can be obtained adding the number of the line containing DECLARE (or BEGIN if DECLARE is missing) minus 1, that is 2 + 5 - 1 = 6.