Technotes

How to generate a xml blob from a record set

select extract(sys_xmlagg(sys_xmlgen(column_value)),’ROWSET/COLUMN_VALUE/ROW’) xmlcontent 
from ( TABLE(XMLSequence (Cursor (SELECT * FROM STG_DELIMITED_FILE_LOAD_01) ) ) ex )

Add comment March 30th, 2012

Oracle grant select for all tables views in one schema to another

SELECT ‘GRANT SELECT ON DIT_SOAINFRA.’ || table_name || ‘ TO RPT_VIEWS;’
FROM all_tables WHERE owner = ‘DIT_SOAINFRA’
UNION ALL
SELECT ‘GRANT SELECT ON DIT_SOAINFRA.’ || view_name || ‘ TO RPT_VIEWS;’
FROM all_views WHERE owner = ‘DIT_SOAINFRA’;

Add comment March 20th, 2012

Killing the zombie process flow process for oracle data warehouse builder

SELECT item_type, item_key, begin_date, end_date, activity_status FROM wf_item_activity_statuses 

WHERE activity_status != ‘COMPLETE’ AND item_type = ‘RCN_TST’; 

SELECT item_type, item_key, begin_date, end_date, activity_status FROM wf_item_activity_statuses 

WHERE activity_status != ‘COMPLETE’ AND item_type = ‘RCN_PKG’; 

SELECT item_type, item_key, begin_date, end_date, activity_status FROM wf_item_activity_statuses 

WHERE activity_status != ‘COMPLETE’ AND item_type = ‘PKG_FILE’; 

BEGIN 

WF_ENGINE.ABORTPROCESS(’RCN_PKG’, ‘WB_IK_20120308_134025_379326_RCN_PKG/PF_FILE_PROCESS_1′); 

END; 

/ 

COMMIT; 

Add comment March 8th, 2012

secure ftp script

— bat script to run the auto sftp — 

@echo off

ECHO “start ftp upload file at %Date% %Time%” >>d:\dts\logs\ftplog.txt

set path =”C:\Program Files (x86)\Ipswitch\WS_FTP 12\”

C:\”Program Files (x86)”\Ipswitch\”WS_FTP 12″\ftpscrpt.com -b true -f d:\dts\sftpuploadtxtfile.txt

move d:\DTS\files\files*.csv d:\DTS\files\archive\

echo “Finish ftp at %Date% %Time%”  >>d:\dts\logs\ftplog.txt

echo “exit code %errorlevel%”   >>d:\dts\logs\ftplog.txt
exit
—– file content for sftpuploadtxtfile—– 

TRACE Screen
LOG Screen
TRACE d:\dts\logs\Trace.txt
LOG d:\dts\logs\Log.txt
USER username
PASS password

CONNECT sftp://hostname.com
CD /home/incoming
LCD D:\dts\files
Mput D:\dts\files\fileer*.csv
Mput D:\dts\files\Canceledfilers*.csv
ONSUCCESS GOTO SUCCESS
ONERROR GOTO DISCONNECT
LABEL SUCCESS
LABEL DISCONNECT
CLOSE

Add comment February 22nd, 2012

Buiness objects checking changes in the CMS for universe

select * from detail_type where detail_type_description like ‘%Univer%’

select count(distinct objectid) from CMS_InfoObjects6

–select * into CMS_TEMP from CMS_InfoObjects6

select prev.* from

CMS_TEMP prev join CMS_InfoObjects6 cr on prev.objectid = cr.objectid

where prev.lastmodifyTime <> cr.lastmodifyTime

select * From CMS_TEMP where typeid=266 

SELECT [TableName] = so.name,[RowCount] = MAX(si.rows)  

FROM sysobjects so,sysindexes si  

WHERE so.xtype = ‘U’

AND si.id = OBJECT_ID(so.name)

GROUP BY so.name

ORDER BY 2 DESC

Add comment February 16th, 2012

How to set universe query limits?

setting universe default limit

default rows 5000

default timeout 10 pages

Add comment February 14th, 2012

OWB how to change prd user password for the target schema?

Here is how to change password in oracle data warehouse prd environment.

OMBCONNECT USER_TGT/password@host_name:1521:service_name

puts “Changing context to the production project”

OMBCC ‘USER’

puts “Connecting to the DEFAULT_CONTROL_CENTER”

OMBCONNECT CONTROL_CENTER

OMBCOMMIT

puts “setting connection details for USER_TGT_LOC location”

OMBALTER LOCATION ‘USER_TGT_LOC’ SET PROPERTIES (PASSWORD)  VALUES (’password’)

OMBCOMMIT

OMBREGISTER LOCATION ‘USER_TGT_LOC’

OMBCOMMIT
OMBDISCONNECT

Add comment February 3rd, 2012

Oracle PL/SQL how to create a db link?

Here is how to create a db link

CREATE DATABASE LINK
servicename@db_LOC_1
CONNECT TO user_name IDENTIFIED BY password
USING ‘(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) 
(HOST=db_host_name)(PORT=db_port)))
(CONNECT_DATA=(SERVICE_NAME=db_service_name)))’;

(run under sqlplus will work while in sql developer won’t) 

Add comment January 27th, 2012

Oracle data warehouse builder how to switch workspace?

Here is how you can switch workspace in oracle data warehouse builder if you have more than one workspace under the same user

exec OWBsys.wb_workspace_management.set_workspace(’WORKSPACE_NAME’);

Add comment January 17th, 2012

checking Oracle warehouse builder audit info in the owb audit table/view

here is how you can check the audit info in the owb audit table/view

this will give you the same info as the one provided by oracle warehouse builder repository browser tools

select * from owbsys.wb_rtv_audit_executions
where return_result like ‘OK_WITH_WARNINGS’

select * from OWBSYS.all_rt_audit_exec_messages
where execution_audit_id in (select audit_execution_id from owbsys.wb_rtv_audit_executions
where return_result like ‘OK_WITH_WARNINGS’ and return_code=2)

Add comment January 17th, 2012