Ask Question
Help Others
Home

Oracle

Count Occurrence of a Character in a String

Sun, 03/20/2011 - 9:39pm — Lepa

I had the need to count an occurrence of a specific character in a string and after tossing some ideas back and forth between a couple of my colleges, we came up with the below two SQLs. You pass a string and identify the character you are looking to count its occurrence. In both SQLs below, I'm trying to count how many times does ">>" exists in string "This is>>a string>>to look into>>". The answer of course is 3 times.

SQL Option 1:

SELECT COUNT (*)
 FROM (SELECT SUBSTR ('This is>>a string>>to look into>>', ROWNUM, 2 ) s,
              ROWNUM pos

Get Edit Tables Behind a Record Fields

Sun, 12/14/2008 - 4:05pm — Lepa

A straight forward SQL to get edit tables behind field(s) for a specific record. The SQL execludes any edit tables that start with "%" as those are dynamic edit tables with values populated by PeopleCode at run time and thus will not be of a good use in this query.

SELECT R.FIELDNAME 
 , R.EDITTABLE 
  FROM PSRECFIELDDB R 
  , PSDBFIELD F 
 WHERE R.RECNAME = :RecordName
   AND SUBSTR(R.EDITTABLE,1,1) <> '%'  
   AND R.EDITTABLE <> ' '  
   AND R.FIELDNAME = F.FIELDNAME
   AND F.FLDNOTUSED = 0;

PeopleSoft Permission List Queries

Wed, 09/17/2008 - 8:46am — Lepa

A section to collect and organize PeopleSoft permission lists related queries.

User IDs assigned to a Permission List

Tue, 09/16/2008 - 1:45pm — Lepa

A query to list all user IDs that are assigned to a specific permission list.

SELECT   c.roleuser AS USER_IDs
    FROM psclassdefn a, psroleclass b, psroleuser c
   WHERE a.classid = b.classid
     AND b.rolename = c.rolename
     AND a.classid = :permissionlist
GROUP BY c.roleuser;

Database Table Space Statistics (Free, Used, and Percent Used)

Wed, 09/03/2008 - 2:18pm — Lepa

An SQL to help gathering storage tablespace statistics. The SQL will list all tablespace names in a particular database along with available, used and percent used storage space in MB.

SELECT   a.tablespace_name, (a.BYTES / 1024) || 'MB' AS used,
         (b.BYTES / 1024) || 'MB' AS free,
         (b.largest / 1024) || 'MB' AS largest_block,
         ROUND (((a.BYTES - b.BYTES) / a.BYTES) * 100, 2) AS percent_used
    FROM (SELECT   tablespace_name, SUM (BYTES) BYTES
              FROM dba_data_files
          GROUP BY tablespace_name) a,

Oracle Database Version

Wed, 09/03/2008 - 2:07pm — Lepa

A Very simple SQL to return Oracle's database version you are currently in.

SELECT *
  FROM v$version
 WHERE ROWNUM = 1;

BANNER
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production

START WITH and CONNECT BY in Oracle

Thu, 04/10/2008 - 4:05pm — Lepa

Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.

Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?

SELECT emplid, supervisor_id
  FROM ps_employees
 WHERE supervisor_id = '1122456';

EMPLID  SUPERVISOR_ID

Search Strings REGEXP_SUBSTR

Mon, 01/07/2008 - 12:47am — Lepa

As of Oracle 10g, the SUBSTR has been enhanced to support searches for regular expressions. The new function is named REGEXP_SUBSTR. You can use the new function to perform advanced searches against a string.

Syntax:

REGEXP_SUBSTR(source_string, pattern
                            [,position
                               [, occurrence
                                   [, match_parameter]
                               ]
                             ]
                            )
Where:
source_string: is the text to search within