Tech Notes

How to generate a xml blob from a record set

techguru — Fri, 30 Mar 2012 09:12:02 +0000

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 )

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

techguru — Tue, 20 Mar 2012 05:27:26 +0000

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’;

Killing the zombie process flow process for oracle data warehouse builder

techguru — Thu, 08 Mar 2012 11:52:35 +0000

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;

secure ftp script

techguru — Thu, 23 Feb 2012 02:06:20 +0000

— 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

Buiness objects checking changes in the CMS for universe

techguru — Thu, 16 Feb 2012 06:52:18 +0000

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

How to set universe query limits?

techguru — Tue, 14 Feb 2012 09:15:00 +0000

setting universe default limit

default rows 5000

default timeout 10 pages

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

techguru — Fri, 03 Feb 2012 16:40:55 +0000

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

Oracle PL/SQL how to create a db link?

techguru — Fri, 27 Jan 2012 07:46:21 +0000

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)

Oracle data warehouse builder how to switch workspace?

techguru — Tue, 17 Jan 2012 09:02:00 +0000

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’);

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

techguru — Tue, 17 Jan 2012 08:50:21 +0000

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)