Friday, September 18, 2009
Back in Action
Thursday, February 19, 2009
Using CGI Environment Variable in APEX
begin
OWA_UTIL.PRINT_CGI_ENV;
end;
This will return:
PLSQL_GATEWAY = WebDb
GATEWAY_IVERSION = 2
SERVER_SOFTWARE = Oracle Embedded PL/SQL Gateway/11.1.0.6.0
GATEWAY_INTERFACE = CGI/1.1
SERVER_PORT = 8080
SERVER_NAME = XDB HTTP Server
REQUEST_METHOD = POST
PATH_INFO = /wwv_flow.show
SCRIPT_NAME = /apex
REMOTE_HOST =
REMOTE_ADDR = 10.26.0.97
SERVER_PROTOCOL = HTTP/1.1
REQUEST_PROTOCOL = HTTP
REMOTE_USER = ANONYMOUS
ORACLE_SSO_USER =
HTTP_CONTENT_LENGTH = 285
HTTP_CONTENT_TYPE = application/x-www-form-urlencoded; charset=UTF-8
HTTP_USER_AGENT = Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.6) Gecko/2009011913 Firefox/3.0.6 (.NET CLR 3.5.30729)
HTTP_HOST = sparrow.hidinc.com:8080
HTTP_ACCEPT = text/html
HTTP_ACCEPT_ENCODING = gzip
HTTP_ACCEPT_LANGUAGE = en-us
HTTP_ACCEPT_CHARSET = ISO-8859-1
HTTP_REFERER = http://sparrow.hidinc.com:8080/apex/f?p=4500:1003:3536261107957055::NO:::
WEB_AUTHENT_PREFIX =
DAD_NAME = apex
DOC_ACCESS_PATH = docs
DOCUMENT_TABLE = wwv_flow_file_objects$
PATH_ALIAS =
REQUEST_CHARSET = AL32UTF8
REQUEST_IANA_CHARSET = UTF-8
SCRIPT_PREFIX =
HTTP_COOKIE = WWV_CUSTOM-F_4752219103344869_117=336B3BAFD41DE0F0; WWV_CUSTOM-F_4752219103344869_113=C7DAB25C26BF73CF; WWV_CUSTOM-F_988309881576256_1000=6F199DA1AEF14FB0; WWV_CUSTOM-F_4752219103344869_114=C544CAFF903CDE0F; oracle.uix=0^^GMT-6:00; activity_ask_expert=1|9500346262672; ORA_WWV_USER=336B3BAFD41DE0F0; ORA_WWV_REMEMBER_UN=BRIAN.BURDITT:BRIAN_TEST; ORA_WWV_ATTRIBUTE_PAGE=4301%2C%23HEAD; LOGIN_USERNAME_COOKIE=brian.burditt; ORA_WWV_R1=%23ALL; ORA_WWV_R2=%23ALL; ORA_WWV_R3=%23ALL
This can now be used with owa_util.get_cgi_env() for validations on these items.
Wednesday, February 18, 2009
Issues with APEX/AJAX/Extjs
TOPICS
APEX
AJAX
Application Process / On Demand
Ext.tree.AsyncTreeNode
Ext.tree.TreeLoader
Ext.tree.TreePanel
Application Process / On Demand
---------------------------------------DECLARE v_Return CLOB; l_nStartPostion NUMBER(22,0) := 1; l_nEndPostion NUMBER(22,0); l_vcBuffer VARCHAR2(32767); l_TotalLength NUMBER(22,0);
BEGIN --set up owa_util.mime_header('text/html', FALSE ); htp.p('Cache-Control: no-cache'); htp.p('Pragma: no-cache'); owa_util.http_header_close;
--Get JSON string --[{"text":"My folder","id":"10","cls":".folder","leaf":false},{"text":"another folder","id":"11","cls":".folder","leaf":false}]
v_Return:= PKG_REPORT_REPOSITORY.PFUNC_GET_NODE_CHILDREN(wwv_flow.g_x01);
--What is the total length of the JSON string l_TotalLength := dbms_lob.getlength(v_Return); --Set the end postion l_nEndPostion:= 30000;
--cycle until we are at the end WHILE l_nEndPostion <= l_TotalLength LOOP l_vcBuffer := dbms_lob.substr (v_Return ,l_nEndPostion-l_nStartPostion + 1 ,l_nStartPostion ); --send the output htp.p(l_vcBuffer ); --reset the start and end l_nStartPostion := l_nEndPostion +1 ; l_nEndPostion := l_nEndPostion +30000; END LOOP; l_vcBuffer := dbms_lob.substr(v_Return,l_TotalLength-l_nStartPostion +1,l_nStartPostion ); htp.p(l_vcBuffer);
END;
Ext.tree.TreeLoader------------------------------
// Create user extensions namespace (Ext.hid)//Ext.hid.TreeLoader.jsExt.namespace('Ext.hid'); /** * Ext.hid.TreeLoader Extension Class * * @author Daniel Moody * @version 1.0 * * @class Ext.hid.TreeLoader * @extends Ext.tree.TreeLoader * @constructor * @param {Object} config Configuration options */Ext.hid.TreeLoader = function(config) { // call parent constructor Ext.hid.TreeLoader.superclass.constructor.call(this, config);}; // end of Ext.hid.TreeLoader constructor
// extendExt.extend(Ext.hid.TreeLoader, Ext.tree.TreeLoader,{ nodeParamName:"node",
getParams:function(D){var A=[],C=this.baseParams;for(var B in C){if(typeof C[B]!="function"){A.push(encodeURIComponent(B),"=",encodeURIComponent(C[B]),"&");}}A.push(this.nodeParamName + "=",encodeURIComponent(D.id));return A.join("");}
}); // end of extend // end of file
Ext application
----------------------------.
.
.
var u = (window.location.href.indexOf("?") > 0) ? window.location.href.substring(0,window.location.href.indexOf("?")) : window.location.href;var baseURL = u.substring(0,u.lastIndexOf("/"));baseURL = baseURL + '/wwv_flow.show?p_flow_id=' + Ext.getDom('pFlowId').value + '&p_flow_step_id=0&p_instance=' + Ext.getDom('pInstance').value + '&p_request=APPLICATION_PROCESS=GET_NODE_CHILDREN'; myTreeLoader = new Ext.hid.TreeLoader( {dataUrl:baseURL,nodeParamName: 'x01'} );
myRoot = new Ext.tree.AsyncTreeNode({ id:'0', text:'Report Repository', split:true}); myTree = new Ext.tree.TreePanel({ id:'im-tree', width:200, split: true, region: 'west', title: 'Folders', loader: myTreeLoader, rootVisible:true, lines:true, autoScroll:true, root: myRoot});
Thursday, October 16, 2008
Under the OWB engine
My, it has been a long time since I have posted shame on me. Today we are going to show you a little under the engine of OWB. More specifically, some queries to extract details of a map from the repository. First, I make no guarantees that the following is 100% correct. At least these queries may help you get a foothold on how OWB is storing maps, projects, and properties.
---================================================================
--- To get the project ID
-------------------------------------------------------------------
select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where S2_1 = 'CMPWBProject'
and S2_3 = 'DSS_' 'FIN'
---RESULT---------------------------------------------------------
8184190
---================================================================
---================================================================
--- To get the install oracle folder in the project
--------------------------------------------------------------------------------
select * --I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
WHERE R_19 = 8184190
and S2_1 = 'CMPWBDataWarehouse'
and S1_8 = 'Production'
---RESULT---------------------------------------------------------
8344258
---NOTE-----------------------------------------------------------
--- Other options
--- CMPWBDataWarehouse
--- CMPProcessInstalledModule process flow modules
--- CMPWBFileInstalledModule flat file folder
---================================================================
---================================================================
--- To get the expected files list
-------------------------------------------------------------------
select S3_4
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258
and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
---RESULT---------------------------------------------------------
f_recip_alt_id.dat
f_clm_hdr.dat
f_hdr.dat
f_note.dat
f_pybl.dat
f_pybl_recv.dat
f_recv.dat
---================================================================
---================================================================
--- To get the filename and stored location name
-------------------------------------------------------------------
SELECT a.S3_4,b.S3_4 FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILELOC'
) B
ON A.R_11 = b.R_11
---RESULT---------------------------------------------------------
f_recip_alt_id.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_clm_hdr.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_hdr.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_note.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_pybl.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_pybl_recv.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
f_recv.dat 60383E500D6341A6AF8DFD4A03B78F06:DSS_FIN_DATA_SOURCE
---================================================================
---================================================================
--- To get the filename and directory
-------------------------------------------------------------------
SELECT a.S3_4, E.S3_4
FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILENAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPINGS.SQLLOADERSTEP.DATAFILE.IDENTIFICATION.DATAFILELOC'
) B
ON A.R_11 = b.R_11
INNER JOIN
"OWB_REPOSOWNER".CMPFCOCLASSES C
ON C.S2_5 ':' C.S2_3 = B.S3_4
INNER JOIN "OWB_REPOSOWNER"."CMPSCOPRPCLASSES" E
ON E.R_15 = C.I_1
---RESULT---------------------------------------------------------
f_recip_alt_id.dat J:\FIN\
f_clm_hdr.dat J:\FIN\
f_hdr.dat J:\FIN\
f_note.dat J:\FIN\
f_pybl.dat J:\FIN\
f_pybl_recv.dat J:\FIN\
f_recv.dat J:\FIN\
---================================================================
---================================================================
--- To get the database connection details. This is good to verify that the map
--- will run into the correct user schema and the correct oracle instance.
-------------------------------------------------------------------
select A "table_name"
, MAX(DECODE(B,'CMPLocation_Host',C)) as "HOST"
, MAX(DECODE(B,'CMPLocation_ConnectAsUser', C)) as "USER_SCHEMA"
, MAX(DECODE(B,'CMPLocation_ServiceName',C)) as "SERVICE_NAME"
from
(
SELECT a.S3_4 a,E.S4_1 b, E.S3_4 c
FROM
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = 'DATAENTITY.PARENT_OBJECT_NAME'
) A
INNER JOIN
(select *
FROM "OWB_REPOSOWNER"."CMPSCOPRPCLASSES"
where R_11 IN
(select I_1
FROM "OWB_REPOSOWNER"."CMPFCOCLASSES"
where r_17 = 8344258 and s2_1 = 'CMPMap'
)
and S4_1 = '8i.MAPPING.ENTITY.LOCATION'
) B
ON A.R_11 = b.R_11
INNER JOIN
"OWB_REPOSOWNER".CMPFCOCLASSES C
ON C.S2_5 ':' C.S2_3 = B.S3_4
INNER JOIN "OWB_REPOSOWNER"."CMPSCOPRPCLASSES" E
ON E.R_15 = C.I_1
) g
group by A
order by A
---RESULT---------------------------------------------------------
table_name HOST USER_SCHEMA SERVICE_NAME
-------------- ------- -------------- ----------------
FIN_BEN_ALT_ID raca ODS0_FIN dss.jp.db.com
FIN_CLM_HDR raca ODS1_FIN dss.jp.db.com
FIN_HDR raca ODS1_NET dss.jp.db.com
FIN_NOTE raca ODS1_REC dss.JP.db.COM
FIN_PYBL racd ODS2 dss.jp.db.com
FIN_PYBL_RECV racd ODS2 dss.jp.db.com
FIN_RECV racd ODS2 dss.jp.db.com
---================================================================
Friday, July 11, 2008
Job Monitor
Query for the audit Log file
After you run a map, get the AUDIT_EXECUTION_ID.(you can get that in the job monitor or the control center manager). Now open SQL Developer and switch to the user schema where your repository is located.
SELECT * FROM WB_RT_AUDIT_FILES WHERE AUDIT_EXECUTION_ID = 2895504
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Jul 10 13:08:27 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: J:\XXXXXXXXXX.ctlCharacter Set WE8MSWIN1252 specified for all input.
Data File: J:\XXXXXXXXXX.txt Bad File: c:\XXXXXXXXXX.bad Discard File: c:\XXXXXXXXXX.dsc (Allow 0 discards)
Number to load: ALLNumber to skip: 0Errors allowed: 50Bind array: 200 rows, maximum of 50000 bytesContinuation: Concatenate every 1 physical recordsPath used: ConventionalSilent options: FEEDBACKTable "TMP_XXXXXXXXXX", loaded from every logical record.Insert option in effect for this table: APPENDTRAILING NULLCOLS option in effect
.
.
.
Cheers
Danny
Monday, July 7, 2008
Oracle IIF
One of the native functions I miss a lot from SQL Server is the IIF. (http://msdn.microsoft.com/en-us/library/27ydhh0d(VS.71).aspx). So I made one for myself for Oracle. Simple but very useful.
Enjoy,
Danny
DECLARE
v_Return VARCHAR2(200);
BEGIN
v_Return := IIF(
1=2,
'YES',
'NO'
);
DBMS_OUTPUT.PUT_LINE('v_Return = ' v_Return);
END;
create or replace
FUNCTION ODS_ETL.IIF
( param1 IN BOOLEAN
, param2 IN VARCHAR2
, param3 IN VARCHAR2
) RETURN VARCHAR2 AS
BEGIN
IF param1
THEN
RETURN param2;
ELSE
RETURN param3;
END IF;
END IIF;