ORACLE SQL TO_DATE FUNCTION FORMATS

SYNTAX

The syntax for the Oracle/PLSQL TO_DATE function is:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Parameters or Arguments

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

[ ...] -It indicates optionalto

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY	Last 3, 2, or 1 digit(s) of year.
YY
Y
IYY	Last 3, 2, or 1 digit(s) of ISO year.
IY
I
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year.
	A value between 0-49 will return a 20xx year.
	A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

APPLIES TO

The TO_DATE function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

ODI META DATA QUERY TO GET THE LIST OF PROCEDURES BEING USED IN DAILY RUNNING ETLS(ODI-10G)

--WORKS IN ODI-10G
select
spj.project_name PROjECT_NAME,
SF.FOLDER_NAME FOLDERNAME,
st.trt_name procedure_name,
stx.txt code
from snp_trt st,snp_folder sf,snp_project spj,snp_txt stx
where 1=1
and sf.i_folder=st.i_folder
and sf.i_project=spj.i_project
--and spj.project_name='RILO_FINANCIALS_ETL'
and stx.i_txt=st.i_txt_trt_txt

AND st.trt_name NOT IN

(SELECT DISTINCT
SSS.STEP_NAME procedure_name
--SSS.STEP_TYPE
from
snp_session ss,snp_sess_step sss,snp_session pss
where ss.sess_no=sss.sess_no
and pss.sess_no=ss.parent_sess_no
and TRUNC(SS.SESS_BEG)='12-JAN-15'
--and trunc(SS.SESS_END)='20-NOV-14'
AND SSS.step_type='T'
AND SS.PARENT_SESS_NO IN
(
164111002,
164112002,
164113002,
164114002,
164115002,
164116002,
164117002,
164118002,
164119002,
164120002,
164121002,
164122002,
164123002,
164124002,
164125002,
164126002)
)--CHANGE THE PARENT SESSION NUMBERS TO YOUR ETL SESSION NUMBERS
order by 1,2;

ODI METADATA QUERY TO GET THE LIST OF INTERFACES FOLDER WISE(ODI-10G)

--WORKS WITH ODI-10G
select
spj.project_name PROjECT_NAME,
SF.FOLDER_NAME FOLDERNAME,
sp.pop_name interface_name,
sst.lschema_name,
sst.table_name "source table",
stt.table_name "target table"
from snp_pop sp,snp_folder sf,snp_project spj,snp_source_tab sst,snp_table stt
where 1=1
and sf.i_folder=sp.i_folder
and sf.i_project=spj.i_project
and sst.i_pop=sp.i_pop
and stt.i_table=sp.i_table
--and spj.project_name='RISO_CRM_PROJECT'--PROJECT NAME
--AND sp.pop_name='RISO_SC_HEADER_DIM_INT'; --INTERFACE NAME
and stt.table_name=upper('riso_ra_cust_trx_all');--TARGET TABLE

CODE TO GET THE QUERY FORMED BY INTERFACE USING META DATA(ODI-10G)

--THIS WILL WORK WITH ODI-10G

declare
interface_name varchar2(40)
:='RILO_GL_JOURNALS_FACT_INT';--GIVE YOUR INTERFACE NAME HERE
cursor columns_temp is
select
sp.pop_name interface_name,sst1.table_name source_table_name_1,sst1.src_tab_alias,spc.col_name source_column_names,st.txt column_txt,st.txt_ord
from snp_pop sp ,snp_source_tab sst1,--snp_source_tab sst2,
snp_pop_col spc
,snp_src_set sss,snp_txt st
where 1=1
and sst1.i_pop=sp.i_pop
and spc.i_pop=sp.i_pop
and spc.i_source_tab=sst1.i_source_tab
and sss.i_pop=sp.i_pop
and sss.i_src_set=sst1.i_src_set
and sss.i_src_set=spc.i_src_set
and sp.pop_name=interface_name
and st.i_txt=spc.i_txt_map
group by sp.pop_name,sst1.table_name,sst1.src_tab_alias,spc.col_name,st.txt,st.txt_ord
order by 4,6;
cursor table_temp is
--only tables
select
sp.pop_name interface_name,sst1.table_name  source_table,sst1.src_tab_alias alias_name
from snp_pop sp ,snp_source_tab sst1
where 1=1
and sst1.i_pop=sp.i_pop
and sp.pop_name=interface_name
group by sp.pop_name,sst1.table_name,sst1.src_tab_alias--,sst2.table_name,st.txt
order by 1,2;

cursor where_temp is
--only tables and where clause
select
sp.pop_name interface_name,sst1.table_name  source_table,sst2.table_name  joined_with,st.txt where_clause
from snp_pop sp ,snp_source_tab sst1,snp_source_tab sst2,snp_pop_clause spcl,
snp_txt st
where 1=1
and sst1.i_pop=sp.i_pop
and sst1.i_source_tab=spcl.i_table1
and sst2.i_source_tab(+)=spcl.i_table2
and spcl.i_pop=sp.i_pop
and sp.pop_name=interface_name
and spcl.i_txt_sql=st.i_txt
group by sp.pop_name,sst1.table_name,sst2.table_name,st.txt  order by 1,2;


begin
dbms_output.put_line('------------'||interface_name);
null;
dbms_output.put_line('select ');
for columns_temp_c in columns_temp
loop
dbms_output.put_line(columns_temp_c.column_txt||',');
end loop;
dbms_output.put_line('from ');
for table_temp_c in table_temp
loop
dbms_output.put_line(table_temp_c.source_table||' '||table_temp_c.alias_name||',');
end loop;
dbms_output.put_line('where ');
for where_temp_c in where_temp
loop
dbms_output.put_line(where_temp_c.where_clause||' and ');
end loop;

exception
when others then
dbms_output.put_line('some error in the anonymous block');
end;

/

--REMOVE THE EXTRA COMMAS,AND WHICH FROM THE RESULT

DBA TABLE TO KNOW THE SPACE OCCUPIED BY OBJECT

select sum(bytes/1024/1024/1024)||'$'||file_name from dba_data_files group by file_name;
 
  SELECT username,profile,default_tablespace,temporary_tablespace FROM dba_users;

  SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;
 
  SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

 select SEGMENT_NAME, SUM (bytes) / 1024 / 1024 / 1024 AS GB from dba_segments WHERE UPPER(segment_name) LIKE 'MISO_CAT%' AND SEGMENT_TYPE ='TABLE'  GROUP BY SEGMENT_NAME ORDER BY 2 DESC;--SEGMENT_NAME IS OBJECT NAME

OBIEE SERVER SIDE COMMON ERRORS AND SOLUTIONS

common errors:

If servers do not restart or start??

solution 1:

restart all the servers using em or command prompt

solution 2:

restart your system and then start all servers from start(may take some time this time)

If analytics page is not opened showing 404 or 500 error for lot of time???

solution 1:

open console >deployments> check all of them must be in active state if not start them

If weblogic credentials working for console and em but not working for analytics???

solution 1:

check the users DEV_BIPLATFORM/MDS are locked or open using

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS;

solution 2:

upload some old rpd which is consistent and restart all the servers.

OBIEE 11g - Important Path Directories

OBIEE 11g - Important Path Directories

    Start BI Services (parameter:  start_all)

..\\\instances\instance1\bifoundation\OracleBIApplication\coreapplication\StartStopServices.cmd start_all

    Stop BI Services (parameter:  stop_all)

..\\\instances\instance1\bifoundation\OracleBIApplication\coreapplication\StartStopServices.cmd stop_all

    Sample Application Data and Repository

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample

    BI Repository(RPD)

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

    Usage tracking and Marketing Segmentation Scripts

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema

    BI Server Cache file

..\\\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\cache

    BI Presentation Catalog

..\\\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog

    Clustered Component

..\\\instances\instance1\bifoundation\OracleBIClusterControllerComponent\coreapplication_obiccs1

    Scheduler Components (Scheduler schema etc.)

..\\\instances\instance1\bifoundation\OracleBISchedulerComponent\coreapplication_obisch1

    BI ODBC , Javahost and other components

..\\\instances\instance1\bifoundation

    Start Weblogic Server (startWebLogic.cmd)

..\\\user_projects\domains\bifoundation_domain

    BI Config Files

..\\\instances\instance1\config

    Repository Config files (NQSConfig.INI,DBFeatures.INI etc)

..\\\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

    Catalog config files(instanceconfig.xml,credentialstore.xml etc)

..\\\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

    Scheduler Catalog config files (instanceconfig.xml)

..\\\instances\instance1\config\OracleBISchedulerComponent\coreapplication_obisch1

    Cluster Config files

..\\\instances\instance1\config\OracleBIApplication\coreapplication

    OPMN Startup (parameter:  startall)

..\\\instances\instance1\bin opmnctl startall

    OPMN Stop (parameter:  stopall)

..\\\instances\instance1\bin opmnctl stopall

    BI PATH Initialisation

..\\\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup\bi-init.cmd

    Weblogic Admin and Managed Server start/stop scripts(startWebLogic.cmd,startManagedWebLogic.cmd etc.)

..\\\user_projects\domains\bifoundation_domain\bin

    BIPublisher repository and Config files

..\\\user_projects\domains\bifoundation_domain\config\bipublisher

    FMW Security Config files (system-jazn-data.xml)

..\\\user_projects\domains\bifoundation_domain\config\fmwconfig

    BI Components Log files

..\\\instances\instance1\diagnostics\logs

    BI Server Log files (NQserver.log , NQQuery.log etc.)

..\\\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1

    BI Presentation Server Log files (sawlog) 

..\\\instances\instance1\diagnostics\logs\OracleBIPresentationServicesComponent\coreapplication_obips1

    Installation Log files

..\\\logs

    Boot.properties: /apps/biadmin/Oracle/user_projects/domains/bifoundation_domain/servers/AdminServer/security

    Application Role:/apps/biadmin/OBIEE11g/user_projects/domains/bifoundation_domain/config/fmwconfig

    Custom Skin:/apps/biadmin/OBIEE11g/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res

    TNS path:

        C:\app\user\product\11.2.0\dbhome_1\NETWORK\ADMIN

        \Oracle_BI1\network\admin

        \oracle_common\network\admin