1) SELECT NVL(LENGTH(TRANSLATE('&str','A0123456789','A')),0) AS num_chars FROM   DUAL
 
2) SELECT length(regexp_replace('1p9j99','[[:digit:]]','')) FROM dual;

result:

123 should return 0
12A3 should return 1
1p9j99 should return 2

What do 'Query Hits' and 'Query Options' mean in the context of a block in 

Oracle Forms? 

 

 

Use SET_BLOCK_PROPERTY(<block name or id>, QUERY_HITS, <number>);  

in an On-Count transactional trigger to tell Oracle Forms how  

many records the query (that you are handling yourself) will return. 

When the On-Count trigger completes execution, Oracle Forms issues the 

standard query hits message:  

 

   "FRM-#####: Query will retrieve <number> records." 

 

 

Use GET_BLOCK_PROPERTY(<block name or id>, QUERY_HITS); 

to return the number of records that have been retrieved by the query. 

 

GET_BLOCK_PROPERTY(.., QUERY_OPTIONS); lets your transactional trigger know 

whether it is supposed to be doing a normal query, a COUNT(*) type query, or a 

lock record query. 

 

 

How can you save the count of the number of records retrieved by an 

EXECUTE_QUERY in a variable? 

 

This value is contained in the following Forms message that is displayed on 

the message line after a COUNT_QUERY is executed: 

 

FRM-40355: Query will retrieve <number> records. 

 

   

For example, you have a base table block based on the 'dept' scott/tiger 

table.   

 

In a PRE-QUERY trigger for the block, include the following code: 

 

   BEGIN 

     /* Restore the query criteria */ 

     :dept.deptno := :GLOBAL.deptno; 

     :dept.dname  := :GLOBAL.dname; 

     :dept.loc    := :GLOBAL.loc; 

   END; 

 

 

In an ON-COUNT trigger for the block, include the following code:  

 

   BEGIN 

     /* ON-COUNT trigger replaces default COUNT-QUERY functionality, */ 

     /* so you must still call COUNT_QUERY in this trigger.          */  

     COUNT_QUERY; 

 

     :GLOBAL.rows_fetched := GET_BLOCK_PROPERTY('dept', QUERY_HITS); 

   END; 

 

 

In a KEY-EXEQRY trigger for the block, include the following code: 

        

   BEGIN 

 

     /* Save the query criteria before getting cleared by COUNT_QUERY */ 

     :GLOBAL.deptno := :dept.deptno; 

     :GLOBAL.dname  := :dept.dname; 

     :GLOBAL.loc    := :dept.loc; 

 

     /* Executing COUNT_QUERY will call the ON-COUNT trigger */ 

     COUNT_QUERY;  

 

     EXECUTE_QUERY; 

  END; 

  

  

COUNT_QUERY clears the block, and queries the database to count the number of  

records that will be retrieved.  Because the block is cleared when COUNT_QUERY 

is executed, the query criteria must be restored in the PRE-QUERY trigger.    

The solution example above will work for queries with or without query 

criteria.  Both EXECUTE_QUERY and COUNT_QUERY fire the PRE_QUERY trigger, thus 

the additional query criteria can be enforced by setting the values in the 

PRE-QUERY trigger. 

 

The query hits value can be assigned to a local variable, global variable or 

parameter using the GET_BLOCK_PROPERTY(QUERY_HITS) function, as in the above 

example.  Note that GET_APPLICATION_PROPERTY(QUERY_HITS) returns a character 

value.   

 

By default, COUNT_QUERY prints an FRM-40355 message with the number of hits on 

the message line.  To prevent this message, write an ON-MESSAGE trigger to 

trap FRM-40355. 

 

If the value of QUERY_HITS is set while records are being retrieved from a 

query ( like in the KEYEXE-QRY trigger for example ), QUERY_HITS specifies the 

number or records that have been retrieved.  This value may be set to 0 even 

though records are retrieved.  Thus, you should use the ON-COUNT trigger to 

set the value by calling COUNT_QUERY, and then assign the value using 

GET_APPLICATION_PROPERTY( QUERY_HITS ).