Back in Action

Finally starting this blog back up. Cody and I have greatly expanded our knowledge base on Oracle products and are excited about continuing to blog what we find out. Hope you enjoy... and maybe we will see you at OOW

Using CGI Environment Variable in APEX

If you execute the following in SQL Workshop of APEX you will get a list of the variables that are available for use.

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.

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

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

---================================================================

Job Monitor

This is more of a tip, but instead of using the control center manager to watch the state of an executing map or process flow, you can use the job monitor.

Query for the audit Log file

You might be asking yourself “Man I would love to query out the log file instead of opening the file in a directory.” Well, today you get your wish.

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

Oracle IIF

Hello All,

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;