The Oracle Mack

SQL: date datatypes & milliseconds

2011-08-04T01:17:00.000-07:00

This morning I was playing with dates while executing an anonymous plslq block from a java class. My goal was to trace the execution time of a class... including milliseconds.

The well known SYSDATE use the "DATE" datatype but from oracle 9i we can use SYSTIMESTAMP (using "TIMESTAMP" datatype):

SELECT
to_char(sysdate, 'YYYY:MM:DD:HH24:MI:SS')
,to_char(systimestamp, 'YYYY:MM:DD:HH24:MI:SS.FF6')
FROM dual

when using TIMESTAMP the comparison can be made through TO_TIMESTAMP (or TO_TIMESTAMP_TZ if having more than one zone field, TZ_OFFSET for getting the region time delay)

so... why we still have to use sysdate?
Because we can configure what is returned from the SYSDATE function modifing the "fixed_date" init parameter; I found it very usefull when testing application that are date-related.

ALTER SYSTEM SET FIXED_DATE = '2011-01-01-00:00:01';
System altered.

select sysdate from dual;

SYSDATE
-----------
01-JAN-2011

select systimestamp from dual;

SYSTIMESTAMP
------------------------------
08-JUL-2011 11.05.02.298000 AM

Adobe LiveCycle vs. Chrome

2011-06-15T04:07:00.000-07:00

While developing a new UCM service showing an in-line view of some loaded PDF documents, I discovered that Chrome uses the "Chrome PDF Viewer" proprietary plugin.

My goal was to force the official Adobe plugin for viewing my LiveCycle encrypted PDF.
Here's what I've done:
- open chrome;
- in the Chrome address bar, type "chrome://plugins";
- click "Disable" on the "Chrome PDF Viewer" plugin;
- close chrome;
- install Acrobat reader 10.1 (the only version i found fully functional with Chrome and LiveCycle);
- open chrome;
- enable the "Adobe Acrobat - Version: 10.1.X.X" plugin ("chrome://plugins");

done.

Quarantine JDeveloper?

2011-03-21T05:44:00.000-07:00

Funny... AVG finds Jdev contains a malware!

UCM: default datepicker with idoc

2011-02-25T15:43:00.000-08:00

If you need to include a datepicker in a page template or in an hcsp and you won't use jquery, you can use the default UCM calendar with datepicker.
Check the following code as an example:

FROM:<input type="text" id="calendarFrom" />
<img onClick="javascript: pucToggleCalendar('calendarFrom');" border="0" style="cursor: pointer; cursor:hand; vertical-align: middle;" src="images/PopUpCalendar/calendar.png"/>

TO:<input type="text" id="calendarTo">
<img onClick="javascript: pucToggleCalendar('calendarTo');" border="0" style="cursor: pointer; cursor:hand; vertical-align: middle;" src="images/PopUpCalendar/calendar.png"/>

<script type="text/javascript">
pucCreateCalendar({ id: "calendarFrom",caption: "From date"}, "calendarFrom");
pucCreateCalendar({ id: "calendarTo",caption: "To date"}, "calendarTo");
</script>

Pay attention to include "std_checkin_html_head_declarations" in your template ; the static javascript code (with the "pucToggleCalendar" function) used to create the popup is included in that resource:

<$include std_checkin_html_head_declarations$>

Linux: boot with terminal

2011-02-25T14:49:00.000-08:00

I've just finished installing "stuff" on a VM with Oracle Enterprise Linux and I need to configure the machine without the resource consuming X11 automatically loaded at startup.

The Linux boot process has six states of operation of which "0" is the shutdown state and "3" and above are fully operational with all essential processes running for user interaction. The init process is the last step of the boot procedure and it is identified by process with id=1; Init is responsible for starting system processes as defined in the "/etc/inittab" file.

Editing the inittab file, I changed the default line:

id:5:initdefault:

with the following:

id:3:initdefault:

Here there are the available states:

0 halt system (loaded the /etc/rc.d/rc0.d/ library)
1 single user (/etc/rc.d/rc1.d/)
2 multiuser with no network (...2.d)
3 terminal
4 reserved for local use
5 X11 GUI mode
6 reboot

After startup type "starx" or "init 5" to enter X11.

WLS: embedded LDAP client access

2011-02-06T08:26:00.000-08:00

To view the entries of the embedded LDAP server through an LDAP client browser, first of all you have to change the embedded LDAP credentials:

1. Access WLS admin console
2. Expand the Domain node.
3. Select the Security-->Embedded LDAP tab.
4. Change the Credential attribute from its generated value to a new password
5. Click SAVE.
6. At last, reboot WebLogic Server.

Now configure a new connection in the LDAP browser (as a suggestion, I use Apache Directory Studio based on Eclipse).

Use the http server port 7001 (7002 if SSL is being used), "dc=[UCM_DOMAIN_NAME]" as the base dn, cn=Admin as the ldap administration user with the newly created password.

UCM: Rules and Profiles metadata

2010-04-08T09:08:00.000-07:00

UCM do not store metadata of Rules and Profiles. To obtain those information you need to use IdcServices (or parse the rules/profiles HDA files).

Following a list of sample URLs:

All profiles:

http://HOSTNAME:PORT/idc/idcplg?IdcService=GET_DOCPROFILES&IsSoap=1

Rules vs single Profile:

http://HOSTNAME:PORT/idc/idcplg?IdcService=GET_DOCPROFILE&dpName=PROFILE_NAME&IsSoap=1

All rules:

http://HOSTNAME:PORT/idc/idcplg?IdcService=GET_DOCRULES&IsSoap=1

Attributes of a single rule:

http://HOSTNAME:PORT/idc/idcplg?IdcService=GET_DOCRULE&dpRuleName=RULE_NAME&IsSoap=1

UCM: Modify the check-in menu

2010-04-08T09:03:00.000-07:00

To modify the "New check in" menu structure in UCM you have to customize the dynamic include "custom_finish_layout_init" using component wizard.

After creating the new resource and the new include use the javascript "navBuilder" object to modify the structure of the menu; use "navBuilder.getNodeById("NEW_CHECK_IN").childNodes" method to search the leafs of the node.

The leaf IDs of the new check in menu are usually named "MY_PERSONAL_CHECKINS_[n]" (with n starting from 0).

Then you can add new nodes or collections using:

navBuilder.addChildNodeTo("NEW_CHECK_IN","collection","id==ACCOUNT_PAYABLES","label==Account payables","url==");

navBuilder.addChildNodeTo("NEW_CHECK_IN","item","id==ACCOUNT_PAYABLES","label==Account payables","url==http://oramack.blogspot.com");

If trying to add a new collection you can choose the position of the leaf specifying the sibling node:

navBuilder.addPrevSiblingNodeTo("MY_PERSONAL_CHECKINS_0","collection","id==ACCOUNT_RECEIVABLES","label==<$lc("wwACCOUNT_RECEIVABLES")$>","url==");

navBuilder.addPrevSiblingNodeTo("MY_PERSONAL_CHECKINS_0","collection","id==ACCOUNT_PAYABLES","label==<$lc("wwACCOUNT_PAYABLES")$>","url==");

then move check in profile into the newly created collections:

i=0;
while(i navBuilder.moveItemInto("ACCOUNT_RECEIVABLES",ARChilds[i]); i++;
}

i=0;
while(i navBuilder.moveItemInto("ACCOUNT_PAYABLES",APChilds[i]); i++;
}

Note: use the IDOC function "lc" for getting translation; example: <$lc("wwACCOUNT_RECEIVABLES")$>

Here is the include full code:

<$include super.custom_finish_layout_init$>

APChilds = new Array();
ARChilds = new Array();

var children = navBuilder.getNodeById("NEW_CHECK_IN").childNodes;
for (var i = 0; i < children.length; i++)
{
TEXT = children[i].getAttribute("label") ;
if (TEXT.match(/\bAR\b/i) != null) {
arrLength =ARChilds.push(children[i].getAttribute("id"));
}
if (TEXT.match(/\bAP\b/i) != null) {
arrLength =APChilds.push(children[i].getAttribute("id"));
}
}

navBuilder.addPrevSiblingNodeTo("MY_PERSONAL_CHECKINS_0","collection","id==ACCOUNT_RECEIVABLES","label==<$lc("wwACCOUNT_RECEIVABLES")$>","url==");
navBuilder.addPrevSiblingNodeTo("MY_PERSONAL_CHECKINS_0","collection","id==ACCOUNT_PAYABLES","label==<$lc("wwACCOUNT_PAYABLES")$>","url==");

i=0;
while(i navBuilder.moveItemInto("ACCOUNT_RECEIVABLES",ARChilds[i]); i++;
}

i=0;
while(i navBuilder.moveItemInto("ACCOUNT_PAYABLES",APChilds[i]); i++;
}

Search inside files

2009-10-14T03:24:00.000-07:00

In order to search for a specific text pattern inside every file in every subdirectory of a given path in linux you can do:

grep -r "text_2_search" *

"-r" stands for "recursive."
Note that with "zgrep" util you can search recursively inside a .zip or .tar.gz file.

Linux resize screen resolution with xrandr

2009-10-14T03:01:00.000-07:00

You can quickly resize the screen resolution using "xrandr" utility (/usr/bin/xrandr).

with xrandr -q you can see all the available resolutions

Input xrandr -s (size) to dynamically resize screen; for example:

xrandr -s 1440x900

Oracle DB: Cold backup

2008-04-09T03:18:00.000-07:00

A cold backup is a phisical backup and is done with the database in a shutdown state (not available to users).
The datafiles do not change during the copy so the database is in sync upon restore.

First of all, you need to shutdown your instance:

export TNS_ADMIN=/export/opt/oracle/8.1.7/network/admin
export ORACLE_SID=some_sid
export ORACLE_HOME=/export/opt/oracle/8.1.7

DT=`date +%Y.%m.%d`
PFILE=/export/opt/oracle/8.1.7/dbs/init$ORACLE_SID.ora

echo Backup for $DT
echo Selecting the files that must be backed up....

/export/opt/oracle/8.1.7/bin/sqlplus -S '/ as sysdba' [minor][minor]eofsql

set termout off
set pages 0
set lines 120
set feedback off
set trimspool on
spool files.2.backup
select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;
select '$PFILE' from dual;
spool off
shutdown immediate
exit;
EOFSQL

then you should "tar" your datafiles, controlfiles and logfiles (so called "image copy"):

tar cfv - -I files.2.backup | gzip | rsh -l some_user some_host "cat - > /path/to/destdir/backup-$DT.tar.gz"

or you can direclty tar a folder; the following is an example on linux:

tar -zcvf ASRAC_20080409_00.tar.gz ASRAC /hera01/app/oracle/oradata/ASRAC

finally startup you instance:

/export/opt/oracle/8.1.7/bin/sqlplus -S '/ as sysdba' [minor][minor]eofsql
startup pfile=$PFILE
exit;
EOFSQL

LSOF: Who's doing what

2008-03-27T07:57:00.000-07:00

Sometimes i got problems on identifing what kind of thing is doing a specified process.
First of all, you need to identify your process in "ps -efl" list.

To learn what program is listening on wich port:

netstat -tlnp

You can also identify processes using files or sockets:

fuser [FILENAME]

now check if you have LSOF utility on you machine.

locate lsof

probably you will find it under "/usr/sbin/lsof".
LSOF (LiSt Open Files) is a useful and powerful tool that will show you opened files.

When lsof is called without parameters, it will show all the files opened by any processes.

lsof | nl

Other examples on who is using the apache executable file, /etc/passwd, what files are opened on device /dev/hda6 or who's accessing /dev/cdrom:

lsof `which apache2`
lsof /etc/passwd
lsof /dev/hda6
lsof /dev/cdrom

What process IDs are using the apache binary, and only the PID?

lsof -t `which apache2`

what files are opened by processes whose names starts by "k" (klogd, kswapd...) and bash?

lsof -c k
lsof -c bash

what files are opened by init?

lsof -c init

what files are opened by processes whose names starts by "courier", but exclude those whose owner is the user "mack"?

lsof -c courier -u ^mack

processes opened by user apache and user mack:

lsof -u apache,mack

Show what files are using the process whose PID is 30297:

lsof +p 30297

Search for all opened instances of directory /tmp and all the files and directories it contains:

lsof +D /tmp

List all opened internet sockets and sockets related to port 80:

lsof -i
lsof -i :80

List all opened Internet and UNIX domain files:

lsof -i -U

Show us what process(es) has an UDP connection opened to or from the host www.akadia.com at port 123 (ntp):

lsof -iUDP@www.akadia.com:123

(about LSOF: http://www.akadia.com/services/lsof_intro.html)

Ldapsearch and LDIF files in OID

2008-03-26T07:17:00.000-07:00

The ldapsearch utility tool comes with every common LDAP library and allows a command-line user to run queries against LDAP directories.

Using Oracle OID ldap you can find the utility under "$OID_HOME/bin/ldapsearch" and the following is the syntax:

ldapsearch -h oid_hostname
-D "binddn"
-w password
[-Y "proxy_dn"]
[-p ldap_port]
[-V ldap_version]
-b "basedn"
{-s base|one|sub}
{"filter_string" [attributes]|-f input_file}
[-A]
[-a never|always|search|find]
[-F separator]
[-S] [-R] [-i 1|0] [-t] [-u] [-L|-X] [-B] [-M] [-v] [-n]
[-l time_limit]
[-z size_limit]
[-O ref_hop_limit]
[-U SSL_auth_mode {-W wallet_location -P wallet_password}]
[-d debug_level]
[-E character_set]

(details here: http://download.oracle.com/docs/cd/B14099_19/idmanage.1012/b15883/syntax_datamngmnt013.htm)

Here some example on using this utility.

[Performing a simple subtree search]

ldapsearch -p 389 -h myhost -b "c=US" -s sub -v "cn=John*"

[The following example retrieves only the distinguished name along with the surname (sn) and description (description) attribute values]

ldapsearch -p 389 -h myhost -b "c=US" -s sub -v "cn=Person*" dn sn description

[search for all_groups starting on a given DN]

ldapsearch \
-h hostname \
-p 3060 \
-D cn=adminuser \
-w password \
-b 'cn=Groups,dc=organizazion,dc=com' \
-s sub objectclass=orclgroup 'cn=*'

[search for users in a given DN]

ldapsearch \
-h hostname \
-p 3060 \
-D cn=adminuser \
-w password \
-b 'cn=GRP_ITA_TPM_VIW,cn=portal.id_install,cn=groups,dc=organization,dc=com' \
-s sub objectclass=orclgroup \
uniquemember

You can generate an LDIF file from an ldapsearch.
The LDAP Data Interchange Format (LDIF) is a standard plain text data interchange format for representing LDAP directory content and update requests.
For example, i can search for all groups with name starting with FBK,DOC,GRP,ORG a generate a file:

ldapsearch \
-h hostname \
-p 3060 \
-D cn=adminuser \
-w password \
-b 'cn=portal.id_install,cn=Groups,dc=organization,dc=com' \
-s sub \
"(&(objectclass=orclgroup)(|(cn=FBK*)(cn=DOC*)(cn=GRP*)(cn=ORG*)))" \
dn > all_grp.ldif

Optionally i can clear the file and consider only the rows i need:

cat all_grp.ldif |grep cn > t1.txt

and then import the LDIF file on a target LDAP:

ldapadd \
-h targethost \
-p 13060 \
-D cn=adminuser \
-w password \
-f ./t1.ldif

Case statement in SQL and PL/SQL

2008-03-26T06:53:00.000-07:00

The following as examples using SIMPLE and SEARCHED CASE statement in pl/sql.
Simple CASE:

text := case n
when 1 then one
when 2 then two

when 3 then three
else other
end case;

Searched CASE:

text := case
when n = 1 then one

when n = 2 then two
when n = 3 then three
when ( n > 3 and n <>
else other
end;

Exception handling:

...
case
when p = 1 then Action1;
when r = 2 then Action2;
when q > 1 then Action3;
end case;
exception
when case_not_found
...

In SQL, you can also have SIMPLE and SEARCHED case.

SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;

SELECT last_name, job_id, salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary <>
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary <>
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM employees;

Disable Ctrl-N

2008-03-07T09:24:00.000-08:00

Here is a simple way to disable CTRL-N in Javascript.

document.onkeydown = function(){
if ((event.keyCode == 78) && (event.ctrlKey)){
//alert ("No new window")
event.cancelBubble = true;
event.returnValue = false;
event.keyCode = false;
return false;
}
}

No right click

2008-03-07T09:23:00.000-08:00

Here is a simple way to disable right-click in Javascript.

JSON

2008-03-05T12:06:00.000-08:00

JSON (JavaScript Object Notation) is a format used for transmitting structured data over a network connection in a process called serialization. Its main application is in Ajax web application programming, where it serves as an alternative to the traditional use of XML.

JSON's basic types are

Number (integer, real, or floating point)
String (double-quoted Unicode with backslash escapement)
Boolean (true and false)
Array (an ordered sequence of values, comma-separated and enclosed in square brackets)
Object (collection of key/value pairs, comma-separated and enclosed in curly brackets)
null

The following example shows the JSON representation of an object that describes a person. The object has string fields for first name and last name, contains an object representing the person's address, and contains a list of phone numbers (an array).

{
"firstName": "John",
"lastName": "Smith",
"address": {
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": 10021
},
"phoneNumbers": [
"212 732-1234",
"646 123-4567"
]
}

The following Javascript code shows how the client can use an XMLHttpRequest to request an object in JSON format from the server. (The server-side programming is omitted; it has to be set up to respond to requests at url with a JSON-formatted string.)

var the_object;
var http_request = new XMLHttpRequest();
http_request.open( "GET", url, true );
http_request.onreadystatechange = function () {
if ( http_request.readyState == 4 ) {
if ( http_request.status == 200 ) {
the_object = eval( "(" + http_request.responseText + ")" );
} else {
alert( "There was a problem with the URL." );
}
http_request = null;
}
};

More info at http://www.json.org