Oracle - Print Column values in Rows

Many times, we wonder how to print all the column values of a single record in row form which will be most widely used for analysis or studies

Here is an utility PL/SQL block which can print all column valus in a rows in tab limitied form (can be easily converted to Spreadsheets)


/**
* Author : Nambi Adhimoolam
* Date : 9/17/2008
* Revision : 1.0
*/

DECLARE
CURSOR c1 (v_table_name VARCHAR2)
IS
SELECT column_name
FROM all_tab_cols
WHERE table_name = v_table_name;

l_sql VARCHAR2 (100);
l_val VARCHAR2 (32000);
BEGIN
FOR k IN c1 ('MY_TABLE')
LOOP
l_sql :=
'SELECT ''''||'|| k.column_name
||' FROM MY_TABLE WHERE rownum < 2';

EXECUTE IMMEDIATE l_sql INTO l_val;

DBMS_OUTPUT.put_line (k.column_name||chr(9)||l_val);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM || SQLCODE);
END;



Disable Substitution variables in SQL* Plus

For example, if we need to execute a following sql, in SQL*Plus


INSERT INTO USER(ID,NAME) VALUES(12,'ABC & CO');


While executing this, SQL*Plus will promt the user to input value 'CO' as its prefixed with subsititution character &. However in this scenarios, its should not act as substitution variable but rather &hersand.

To overcome this issues, we can disable the subsititution variabels with


SET DEFINE OFF


For selective disabling and other options, refer
http://www.orafaq.com/wiki/SQL*Plus_FAQ

Frequent Use of VBScript Confirm Box

In an user friendly internet applications, one of the most used feature is prompts. Since Java script doesnot offer much flexibility, most of us turn to VB Script. Here the crux of wat we look normally for prompts.


retVal = makeMsgBox("Hi","how are you?",32,1,256,4096);


You can change the numbers to create alerts, confirms or any type of popup using the numbers / arguments below.

The alert and confirm boxes take 4 arguments

Argument one : The Icon 0 = none
16 = X
32 = ?
48 = !
64 = i

Argument two : The buttons 0 = OK # standard alert
1 = OK CANCEL
2 = ABORT RETRY IGNORE
3 = YES NO CANCEL
4 = YES NO
5 = RETRY CANCEL

Argument three : 0 = First
In your popup, which 256 = Second
button should be selected 512 = Thrid
by default?

Argument four : 0 = Browser stalls
The system command will 4096 = All apps stall
stall either the browser
or the whole system until
the user responds
(either submits or cancels)


A value is returned
depending on which button
was pressed. So you could say

if(retVal == 3){do this}
else {do this}

Here are the return values OK = 1
Cancel = 2
Abort = 3
Retry = 4
Ignore = 5
Yes = 6
No = 7

Effective RDBMS Interface for Pega!

Pega Rules Process commandar is a workflow engine that helps to develop Business Process Management application.

In recent project, we had an challenge in data intensive application design as Pega does not support distributed transaction with external databases.

After trying out several designs, we found an excellent solution which not only requires very less coding, but scalable, robust and more over fits perfectly given the nature of Pega Architecture and most of the RDBMS.

The one-line solution is XML!

Yes, since pega internally stores anything and everything as XML, we have an easy job when we can pass the complete work object (pega-terminalogy equivalent to object in java) in XML format to Database, where DB can parse the input, process and send back the reponse in same way.


PROCEDURE FRED_SERVICE (
p_header IN CLOB,
p_request IN CLOB,
p_response OUT VARCHAR2,
p_err_code OUT NUMBER
)
IS
/* XML Parsing local variables */

l_doc DBMS_XMLDOM.DOMDocument;
l_element DBMS_XMLDOM.DOMElement;
l_attr DBMS_XMLDOM.DOMAttr;
l_node DBMS_XMLDOM.DOMNode;
l_node_list DBMS_XMLDOM.DOMNodeList;

l_pxResults_list DBMS_XMLDOM.DOMNodeList;
l_rowdata_list DBMS_XMLDOM.DOMNodeList;
l_rowdata_child_list DBMS_XMLDOM.DOMNodeList;


l_node_value VARCHAR2(1000);
l_tag_name VARCHAR2(1000);
l_pxResults_size NUMBER;
l_rowdata_size NUMBER;
l_rowdata_child_size NUMBER;
l_rowdata_column FRED_UTIL.FRED_ELEMENT := FRED_UTIL.FRED_ELEMENT('ELEMENT1',
'ELEMENT2',
'ELEMENT3',
'ELEMENT4',
'ELEMENT5');
l_rowdata_map FRED_UTIL.FRED_ELEMENT_MAP;

/* Transaction Unit local variables*/
ELEMENT1 NUMBER;
ELEMENT2 NUMBER;
ELEMENT3 NUMBER;
ELEMENT4 DATE;
ELEMENT5 VARCHAR2(3);

/* Trace */
l_file UTL_FILE.file_type;
l_log_file_name VARCHAR2 (100) := 'fredService.log';
l_log_dir VARCHAR2 (100) := '/tmp';
l_log_msg VARCHAR2 (1000) := 'fredService';


BEGIN
p_err_code :=0;
p_response :=NULL;
l_log_msg := TO_CHAR (SYSDATE, 'MM/DD/YY HH24:MI:SS')||'**** fred_service STARTED****';
l_log_msg := l_log_msg||chr(10);
DBMS_OUTPUT.put_line (l_log_msg);
l_file := UTL_FILE.fopen (l_log_dir,l_log_file_name,'W');
UTL_FILE.put_line (l_file,l_log_msg);

IF p_request IS NULL
THEN
p_err_code := 999;
p_response := 'INPUTNULL';
UTL_FILE.put_line (l_file,p_response);
ELSE

l_doc := DBMS_XMLDOM.newdomdocument (p_request);
l_pxResults_list := DBMS_XMLDOM.getelementsbytagname (l_doc, 'pxResults');
l_rowdata_list := DBMS_XMLDOM.getChildrenByTagName(DBMS_XMLDOM.makeElement(DBMS_XMLDOM.item(l_pxResults_list,0)),'rowdata');
l_rowdata_size := DBMS_XMLDOM.getlength (l_rowdata_list);

FOR rowCounter in 0..l_rowdata_size-1 LOOP

FOR colCounter in 1..l_rowdata_column.count LOOP

l_rowdata_child_list := DBMS_XMLDOM.getChildrenByTagName(DBMS_XMLDOM.makeElement(DBMS_XMLDOM.item(l_rowdata_list,rowCounter)),l_rowdata_column(colCounter));
l_node := DBMS_XMLDOM.item(l_rowdata_child_list,0);
l_rowdata_map(l_rowdata_column(colCounter)) := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(l_node));

END LOOP;

ELEMENT1 :=TO_NUMBER(l_rowdata_map('ELEMENT1'));
ELEMENT2 :=TO_NUMBER(l_rowdata_map('ELEMENT2'));
ELEMENT3 :=TO_NUMBER(l_rowdata_map('ELEMENT3'));
ELEMENT4 :=TO_DATE(SUBSTR(l_rowdata_map('ELEMENT4'),1,8),'YYYYMMDD');
ELEMENT5 :=l_rowdata_map('ELEMENT5');


l_log_msg := l_log_msg||'
'
||'Calling Business Transaction with following parameters'||'
'
||'ELEMENT1: '||ELEMENT1||'
'
||'ELEMENT2: '||ELEMENT2||'
'
||'ELEMENT3: '||ELEMENT3||'
'
||'ELEMENT4: '||ELEMENT4||'
'
||'ELEMENT5: '||ELEMENT5||'
';

END LOOP;

END IF;
l_log_msg := l_log_msg ||'
'||TO_CHAR (SYSDATE, 'MM/DD/YY HH24:MI:SS')||'**** fred_service completed****';
p_response := l_log_msg;
UTL_FILE.put_line (l_file,l_log_msg);
UTL_FILE.fclose (l_file);

EXCEPTION WHEN OTHERS THEN
p_err_code := SQLCODE;
p_response := SQLERRM;
END FRED_SERVICE;





Autonomous Transaction

Feature from oracle 8i.

The commit/rollback does not affect the surrounding transaction.

Can be used if we want to log some message irrespective of state of the transaction.


PROCEDURE TEST_PRC IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
NULL;
END;


Vector / ArrayList - Size increment

ArrayList and Vector are internally implemented with Object[] with dynamically expands as we add more objects. But its interesting to know why people stress on using arrays if at all possible in the place these utilities.

These dynamic arrays increases the memory in folds.

ArrayList - default capacity 10

int newCapacity = (oldCapacity * 3)/2 + 1;


Vecor - default capacity

int newCapacity = (capacityIncrement > 0) ?
(oldCapacity + capacityIncrement) : (oldCapacity * 2);



So its better to initialize these objects with proper initial capacity to avoid performance issues.

Keywords not used in Java

• goto
• const

The keywords const and goto are reserved, even though they are not currently used. true, false, and null might seem like keywords, but they are actually literals; you cannot use them as identifiers in your programs.

Newly added keyword in Java 5 - enum

Refer

http://java.sun.com/docs/books/tutorial/java/nutsandbolts/_keywords.html