Posts Tagged ‘Oracle Query’

Select Number And Convert Number From English To Arabic In Oracle

April 23rd, 2018, posted in Oracle Queries
Share
CREATE FUNCTION numToEasternArabic(
  in_value IN NUMBER
) RETURN NVARCHAR2 DETERMINISTIC
IS
  p_num  VARCHAR2(100) := TO_CHAR( in_value );
  p_char CHAR(1);
  o_str  NVARCHAR2(100);
BEGIN
  FOR i IN 1 .. LENGTH( p_num ) LOOP
    p_char := SUBSTR( p_num, i, 1 );
    o_str := o_str
          || CASE p_char
             WHEN '.'
             THEN N'.'
             ELSE UNISTR(
                    '\' || TO_CHAR(
                             TO_NUMBER( p_char ) + 660,
                             'FM0000'
                           )
                  )
             END;
  END LOOP;
  RETURN o_str;
END;
/


Query 1:

SELECT numToEasternArabic( 1438 )
FROM   DUAL

Results:

| NUMTOEASTERNARABIC(1438) |
|--------------------------|
|                     ١٤٣٨ |
 
Share

Database Log Switch Intervals

December 4th, 2017, posted in Oracle Queries
Share

Oracle Database Log Switch Intervals :

 

Hourly Log Switch breakdown :

 

SELECT * FROM (
SELECT * FROM (
SELECT   TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0)), '99') "00:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0)), '99') "01:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0)), '99') "02:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0)), '99') "03:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0)), '99') "04:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0)), '99') "05:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0)), '99') "06:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0)), '99') "07:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0)), '99') "08:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0)), '99') "09:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0)), '99') "10:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0)), '99') "11:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0)), '99') "12:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0)), '99') "13:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0)), '99') "14:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0)), '99') "15:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0)), '99') "16:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0)), '99') "17:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0)), '99') "18:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0)), '99') "19:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0)), '99') "20:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0)), '99') "21:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0)), '99') "22:00"
       , TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0)), '99') "23:00"
    FROM V$LOG_HISTORY
    WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
) WHERE ROWNUM < 30
/

Archive, Archive Log, Archive Log Mode, Check Oracle Archived, Check Oracle Archived Redo Log, Check Oracle Archiving, Check Oracle Redo Log, How to disable Archive Log Mode, How to Enable Archive Log, Log Mode, Oracle, Oracle Archived Redo, Oracle Archiving, Oracle Archiving Log, Oracle Database 10g, Oracle Database 11g, Oracle DBA, Oracle DBA Task, Redo Lob, Redo Log, Steps To Configure Archive Log Mode in Oracle Database 10g and 11g, Steps To Configure Archive Log Mode in Oracle Database 11g
Share

To find the ROW Counts of tables that belongs to a Particular Schema

September 12th, 2017, posted in Oracle Queries
Share
select table_name,num_rows counter from dba_tables where owner=’Schema_name’ order by table_name;
Share

How to check Oracle EBS/APPS Version

May 22nd, 2016, posted in Oracle Queries
Share

To check Oracle EBS/APPS Version

Run following SQL from apps user :Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

select RELEASE_NAME from fnd_product_groups; 

You should see output like :

RELEASE_NAME
-----------------------
11.5.10.2
Share