Blog

Oracle - Change Password

2012-01-09T13:33:00.000-06:00

alter user jvogel identified by "jason.123" replace "jason.456"

Oracle - SQL - Evenly dividing a range of Data using NTILE, MIN, and MAX

2012-01-05T11:26:00.001-06:00

Problem: Over a widely dispersed set of IDs, create 10 evenly divided ranges each in terms of count per group. Determine the ID range for each group.
Practical: We needed to scrub a bunch of data in parallel streams. The easist answer was to create 10 simultaneous UPDATEs, but we didn't want the updates stepping on each other.
Solution: Use NTILE to create 10 evenly "divided" temporary groups [from a 'rowcount' standpoint]. Use MIN and MAX against each temporary group to determine the starting and ending points. Query:

SELECT 
   Quartile,
   MIN("ID") "StartingID",
   MAX("ID") "EndingID",
   COUNT(*) "CountPerGroup"
FROM
   ( 
      SELECT 
         a.contract_payment_id "ID",
         NTILE(10) OVER(ORDER BY a.contract_payment_id) AS quartile
      FROM   
         contract_payment a 
      WHERE
         a.transaction_type_code = 'CC' and 
         a.credit_card_type_code = 'VISA'
         and rownum <= 10000  -- Reduce resultset
 ) 
GROUP BY 
   quartile
ORDER BY 
   1;

Results:

QUARTILE  StartingID  EndingID  CountPerGroup
    1        59261      93271       1000
    2        93281     110492       1000
    3       110512     122382       1000
    4       122391     134062       1000
    5       134072     144371       1000
    6       144372     154481       1000
    7       154482     162741       1000
    8       162752     169163       1000
    9       169173     176913       1000
   10       176921     183833       1000

SameTime - Recovering your SameTime BuddyList

2011-12-12T11:56:00.000-06:00

Under Windows XP...

C:\...\\My Documents\SametimeFileTransfers\buddylist.xml

Under Windows 7...

C:\Users\\AppData\Roaming\Lotus\Sametime\.metadata\.plugins\com.ibm.collaboration.realtime.imhub\buddylist.xml

Oracle - Performance - Timing a Statement

2011-09-07T14:29:00.002-05:00

Always, start with a fresh database session to remove any caching from previous runs.

connect user/password@database;

set timing on;

select 
	PKG_ACCT_DECRYPTION_FUNCTIONS.f_secure_decrypt(PKG_ACCT_ENCRYPTION_FUNCTIONS.f_secure_encrypt(to_char(sysdate,'mm/dd/yyyy hh24:mi:ss')))
from dual;

Also see Explain Plan and SysTimeStamp with Milliseconds.

Oracle - Sessions

2011-09-01T17:27:00.003-05:00

SELECT
  'Currently, ' 
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of ' 
  || VP.VALUE 
  || ' connections are used.' AS "Session Usage"
FROM 
  V$PARAMETER VP
WHERE VP.NAME = 'sessions'

Currently, 1552 out of 6000 connections are used.

Unix Notes - System / Kernel Parameters

2011-09-01T17:20:00.002-05:00

sysdef -i

pending

Unix Notes - iostat

2011-08-31T12:58:00.001-05:00

iostat -x 5 100

                 extended device statistics
device    r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
md10      1.2   15.2    9.6   19.7  0.1  0.5   35.9   8   8
md11      0.6   15.2    4.8   19.7  0.0  0.5   29.9   0   7
md12      0.6   15.2    4.8   19.7  0.0  0.4   28.1   0   7
md20      0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0
md21      0.0    0.0    0.0    0.0  0.0  0.0    0.0   0   0

Compiling PL/SQL and Recompiling Dependent Objects

2011-08-17T07:24:00.001-05:00

set echo on;
SELECT * FROM user_objects WHERE status = 'INVALID';
set echo off;

Prompt 
Prompt Compiling c:\dispatch_data_obj_body.sql
@c:\dispatch_data_obj_body.sql

Prompt 
Prompt Attempting to recompile any invalid objects 
exec sys.UTL_RECOMP.recomp_serial(schema => user);

set echo on;
Prompt 
SELECT * FROM user_objects WHERE status = 'INVALID';
set echo off;

Unix Notes - Server Performance Monitoring with TOP

2011-08-16T11:03:00.003-05:00

/usr/local/bin/top

load averages:  1.49,  1.43,  1.52;                    up 0+03:4211:08:29
344 processes: 341 sleeping, 3 on cpu
CPU: 93.9% idle,  5.6% user,  0.5% kernel,  0.0% iowait,  0.0% swap
Memory: 128G phys mem, 33G free mem, 63G swap, 63G free swap

   PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 26167 oracle     1   0    0   64G   64G cpu      0:02 52.42% oracle
 26175 oracle     1   0    0   64G   64G cpu      0:01 24.22% oracle
  4884 oracle     1  59    0 1157M 1153M sleep    4:56 17.69% oracle
 14457 oracle    11  59    0   64G   64G sleep    0:06  7.65% oracle
 14329 oracle    11  59    0   64G   64G sleep    0:12  6.31% oracle
 12036 oracle     1  59    0   64G   64G sleep    0:24  2.34% oracle
  4093 oracle     1  59    0   64G   64G sleep    2:20  1.47% oracle
 12034 oracle    11  59    0   64G   64G sleep    0:05  1.24% oracle
 24352 oracle     1  59    0   64G   64G sleep    0:00  1.08% oracle
 10318 oracle     1  59    0   64G   64G sleep    0:12  1.06% oracle
  4995 oracle     3  43    0   46M   34M sleep    0:33  0.71% tnslsnr
  4527 oracle     1  59    0   64G   64G sleep    0:21  0.55% oracle
  5939 oracle     1  59    0   64G   64G sleep    0:00  0.47% oracle
  8863 oracle     1  59    0   64G   64G sleep    0:07  0.42% oracle
 23943 jvogel     1  59    0 3816K 2832K cpu      0:02  0.38% top

Unit Notes - vmstat (virtual memory statistics)

2011-08-02T10:56:00.000-05:00

Reference: http://en.wikipedia.org/wiki/Vmstat

I've formatted the following example to make it a little easier to read.

$ vmstat 5
  kthr      memory            page             disk            faults        cpu
 r  b w   swap    free   re   mf   pi  po fr de sr m0 m2 m3 m4  in    sy     cs  us sy id
 3  6 0 9901864 4988152 1312 1293 2295 22 47  0  9  1  0  9  1  831    877    90 49 11 40
 0 10 0 6819504 2078880 1153 5777 4383  5  3  0  0  0  0  1  1 4642 188881 18478 32 12 57
 0  9 0 6813976 2075208 1350 7623 4380  6  6  0  0  0  0  2 18 5293 171861 18412 32 15 53
 0 10 0 6793096 2060464 1587 5025 4361 16 14  0  0  0  0  2  0 5773 192402 19898 31  9 59
 0 12 0 6781504 2046376 1865 8465 4436 16 16  0  0  0  0  7  0 6014 185604 17696 48 11 41
 0  6 0 6786576 2049600 1269 5981 4369  8  8  0  0  2  0  2  0 5592 203470 21790 35 10 55

In this specific example, we are having issues with blocking.

Unix Notes - Find a File

2011-06-23T11:40:00.002-05:00

Related Posting: To find a string within a file list, see this post.

Find all the SQL (*.sql) files in the current directory:

find . -name '*.sql'

To hide any [access] errors, include 2>/dev/null at the end of the line.

find . -name '*.sql' 2>/dev/null

To list only files and avoid all directories:

find . -type f -name '*.sql' 2>/dev/null

Search all directories ('/' is the top level directory reference):

find / -type f -name '*.sql' 2>/dev/null

Unix Notes - Find Text / String in Files

2011-06-23T11:31:00.003-05:00

From the current directory and down, search for "ldapDelete" explicitly. Hide any read/access errors (2>/dev/null).

find . -exec grep "ldapDelete" '{}' \; -print 2>/dev/null

From the current directory and down, search for "ldapdelete" WITHOUT case sensitivity.

find . -exec grep -i "ldapdelete" '{}' \; -print 2>/dev/null

Resetting / unexpiring an Oracle user

2011-06-09T15:23:00.002-05:00

If you need to reset a user's password, here is a simple sample.

Please note the double quotes around the password.

It also requires the user to change their password on first login.

select username,account_status from dba_users where username = 'JOEUSER';

alter user JOEUSER identified by "expired.567";
alter user JOEUSER password expire;
alter user JOEUSER account unlock;

select username,account_status from dba_users where username = 'JOEUSER';

Burning an Oracle Sequence value to raise it above Table Max

2011-06-09T11:47:00.006-05:00

We have a log table that we use to track miscellaneous behaviors via some triggers for "audit" purposes. The design is fairly simple... on the occurrence of a "monitoring" behavior, INSERT a row into the AUDIT_LOG table. The INSERT grabs a new sequence value and poof, we're good.

Well, not always. Periodically, the MAX table value exceeds the sequence value. I don't know why/how it happens, but it causes our applications to start throwing errors [as expected]. I wrote a simple query to get the sequence value back above the table ID maximum.

Note: you may need to establish the "currval" of the session by burning a "nextval".

select
	-- audit_seq.nextval "Sequence NextVal",
	audit_seq.currval "Sequence CurrVal",
	(select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) "Table Max ID",
	case
		when audit_seq.currval >= (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG)
			then 'Sequence good'

		when audit_seq.currval < (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG)
			then 'Sequence bad, Table Higher by '||( (select max(AUDIT_LOG.SEQ_NO) from AUDIT_LOG) - audit_seq.nextval)
		else
			'wtf'
	end "Sequence Status"
from
	dual

You may want to reference: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#sthref806

ORA-02287: sequence number not allowed here

Oracle Replication - Disabling it a for a session

2011-06-07T13:00:00.003-05:00

DBMS_REPUTIL.REPLICATION_OFF;

Oracle Explain Plan

2011-05-25T12:48:00.000-05:00

set serveroutput on;
clear;

explain plan for
   select
   d.id,
   --d.xml,
   extractValue(d.xml,'/Account/StarId')"StarID",
   extractValue(d.xml,'/Account/Name') "DistributorName",
   NVL(extractValue(d.xml,'/Account/FranchiseCode'),'< Null >') "FranchiseCode"
   from
   genesis_dist_to_ent d
   where
   -- rownum <= 5
   extractValue(d.xml,'/Account/StarId') = '16534' ;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Results:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | B
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               | 29002 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  2 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  3 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  4 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  5 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  6 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  7 |  FILTER                      |                               |       |
|   8 |   INDEX FAST FULL SCAN       | PK_GENESIS_DIST_TO_ENT        | 29002 |
|*  9 |   TABLE ACCESS BY INDEX ROWID| GENESIS_XMLINDEX_PATHTABLE    |   371 |
|* 10 |    INDEX RANGE SCAN          | SYS3119198_GENESIS__PATHID_IX |     4 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."PATHID"=HEXTORAW('4B1D')  AND "SYS_P0"."RID"=:B1)
   3 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
   4 - access("SYS_P2"."PATHID"=HEXTORAW('7F13')  AND "SYS_P2"."RID"=:B1)
   5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P4"."LOCATOR")=1)
   6 - access("SYS_P4"."PATHID"=HEXTORAW('35BE')  AND "SYS_P4"."RID"=:B1)
   7 - filter( (SELECT "SYS_P6"."VALUE" FROM "PROD"."GENESIS_XMLINDEX_PATHTABLE"
              WHERE "SYS_P6"."RID"=:B1 AND "SYS_P6"."PATHID"=HEXTORAW('4B1D')  A
              SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)='16534')
   9 - filter(SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)
  10 - access("SYS_P6"."PATHID"=HEXTORAW('4B1D')  AND "SYS_P6"."RID"=:B1)
Note
-----
   - 'PLAN_TABLE' is old version
 
35 rows selected

Oracle - Tracking Progress without DBMS_OUTPUT buffering issues

2011-05-13T15:40:00.005-05:00

Oracle's dbms_output.put_line(...) functionality works fine unless you actually want to see output while a script is running.

Disclaimer: This "solution" requires access to V$SESSION. Confirm before preceeding.

For some reason known only to the Oracle gods [pun intended], this output is buffered until the block / script completes. My preferred workaround is to use DBMS_APPLICATION_INFO.SET_CLIENT_INFO(text) and then to query v$session for column CLIENT_INFO by the appropriate criteria (e.g. SID, Serial#, USERNAME,...).

select
   s.client_info,
   s.program, 
   s.status,
   s.SID||' - '||s.SERIAL#
from 
   v$session s
where
   s.username = 'JVOGEL';

Disclaimer: I'm sure there is a performance penalty of some sort. The real-time feedback is critical so I'm willing to accept the trade-off. In a loop you could choose to only invoke the routine every "x" number of occurrences.

I've encapsulated this into a "common" routine that I use called SetRunStatus.

/**
SetRunStatus('Evaluating Record Status',n_index => i,n_count => ln_count,n_id => lnt_remit_queue_id_list(i));
*/
PROCEDURE SetRunStatus(
 s_msg          IN   VARCHAR2,
 n_count        IN   NUMBER := Null,
 n_index        IN   NUMBER := Null,
 n_id           IN   NUMBER := Null )
IS
 ls_msg     VARCHAR2(64) := Null;

BEGIN
 
 IF (n_index > 0) THEN 
  ls_msg := ls_msg||' '||n_index;
 END IF;
 IF (n_count > 0) THEN
  ls_msg := ls_msg||' of '||n_count;
 END IF;
 IF (n_id > 0) THEN
  ls_msg := ls_msg||' [ID:'||n_id||']';
 END IF;
 ls_msg := SUBSTR('[Remit] '||TRIM(s_msg||ls_msg),1,64);

 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(ls_msg);

 EXCEPTION
  WHEN OTHERS THEN 
   Null; -- Swallow any exceptions caused by SetRunStatus 

END SetRunStatus;

Bad Characters to Use in Web-based Filenames

2011-04-28T14:36:00.000-05:00

RFC 2396 is the governing document for "Uniform Resource Identifiers (URI): Generic Syntax." This RFC defines what can and can't be used in a URI, as well as what shouldn't be used.

First, section "2.2. Reserved Characters" contains the following list of reserved characters:

reserved = ";" | "/" | "?" | ":" | "@" |
           "&" | "=" | "+" | "$" | ","

Second, section "2.4.3. Excluded US-ASCII Characters" contains the following lists of delimiter and unwise characters:

delims = "<" | ">" | "#" | "%" | <">

unwise = "{" | "}" | "|" | "\" | "^" | "[" | "]" | "`"

ToDo: Add a Regular Expression to explicitly exclude these characters in SanitizeText

PL/SQL Date Calculations

2010-09-02T17:26:00.001-05:00

Recently, I needed to do some Oracle PL/SQL / SQL Date range manipulation. I thought that I would share what I used after a quick web search returns some overly verbose results based on conversions using TO_CHAR.
Queries:

SELECT 
   SYSDATE "Today",
   TRUNC(SYSDATE) "Start of Day",
   TRUNC(SYSDATE+1) - 1/(24*60*60) "End of Day (EOD)",
   TRUNC(SYSDATE,'MM') "First Day of the Month",
   TRUNC(SYSDATE,'YEAR') "First Day of the Year"
FROM 
   DUAL

Results: (Formated for readability)

Today                   9/2/2010 5:17:51 PM
Start of Day            9/2/2010
End of Day              9/2/2010 11:59:59 PM
First Day of the Month  9/1/2010
First Day of the Year   1/1/2010

EnGenius ERB9250 Wireless Range Extender (Repeater)

2010-08-20T14:52:00.003-05:00

I recently bought a EnGenius ERB9250 Wireless Range Extender and coupled it with my Apple AirPort Extreme (which support A, B, G, and N wireless networks) after configuring it from my Windows XP client.

It works amazingly well.

I bought it from NewEgg. There were your standard complaints about the manual. There was indeed a picture missing from the Windows XP installation steps [which is what my laptop runs].

I followed the "manual" instructions.

There are a couple of additional details that I would like to add.

After you "configure" the repeater as per the instructions and you are preparing to connect... stop. Until the repeater displays the same name as the original router, it's not ready for connections. The repeater is ready when it shows up as a second access point with the same name as the original.

Again, an amazing product hampered by a poor manual.

Execute Shell Commands from Oracle

2010-06-25T11:35:00.002-05:00

Excellent article on executing command line functionality from within Oracle.

Be aware!

Requires special permissions to execute
Security risk

http://www.oracle-base.com/articles/8i/ShellCommandsFromPLSQL.php

Unix Notes - CRON

2010-06-21T13:23:00.003-05:00

-- Location of cron jobs
/var/spool/cron/crontabs

Unix Notes - Root and sudo access

2010-06-07T15:53:00.002-05:00

-- sudo access
/usr/local/bin/sudo su -

Oracle SQL Query Formatting - Using the FORMAT command

2010-06-02T10:41:00.007-05:00

Query

column "User Counts as of" FORMAT a19;
column "Site 1" FORMAT 99999999;
column "Site 2" FORMAT a10;

select 
 TO_CHAR(SYSDATe,'MM/DD/YYYY HH24:MI:SS') "User Counts as of",
 (select count(*) from v$session) "Site 1",
 (select count(*) from v$session@orcl_site2) "Site 2"
from dual;

Results

User Counts as of       Site 1     Site 2
------------------- ---------- ----------
06/02/2010 10:43:20        600        581

Check out thinklinkr.com! Create and share outlines in your browser.

2010-05-21T08:48:00.004-05:00

Check out thinklinkr.com! Create and share outlines in your browser.

Unix Notes - Change Password

2010-05-05T17:19:00.005-05:00

-- Change Password
passwd <username>

Unix Notes - Disk Utilization

2010-05-05T17:14:00.006-05:00

 
-- Show Disk Space Utilization by Directory 
du -sk * | sort -rn | more

-- Show Disk Space Free for the current directory
df -h

-- Show Disk Space Free for /export/home
df -h /export/home

Unix Notes - Wide display for ps

2010-04-29T15:56:00.001-05:00

/usr/ucb/ps auxw

Oracle: Determining if a Table is Replicated

2010-03-16T13:33:00.015-05:00

Query:

Select Oname, Sname 
from dba_repobject 
where Oname IN ('DISPATCH_QUEUE','CONTRACT');

Results:

ONAME	SNAME
CONTRACT	PROD

The CONTRACT table is replicated, but the DISPATCH_QUEUE table is not.

PL/SQL Cursor For Loop with Bulk Collect and Limit

2009-04-14T10:21:00.004-05:00

Here is an cursor example of how to use a BULK COLLECT with a LIMIT clause. DevNote: %NOTFOUND check must be at the end of the loop

declare

   PROCEDURE bulk_with_limit (
      n_start_in     IN    CONTRACT.CONTRACT_ID%TYPE,
      n_end_in       IN    CONTRACT.CONTRACT_ID%TYPE,
      limit_in       IN    PLS_INTEGER := 100
   )
   IS
      CURSOR contracts_cur
      IS
         SELECT *
         FROM contract
         WHERE contract_id between n_start_in and n_end_in;

      TYPE contract_trec IS TABLE OF contracts_cur%ROWTYPE INDEX BY PLS_INTEGER;

      l_contracts   contract_trec;

   BEGIN
      OPEN contracts_cur;

      LOOP
         FETCH contracts_cur
            BULK COLLECT 
            INTO l_contracts 
            LIMIT limit_in;

         -- Perform business logic ... 
         FOR i IN 1 .. l_contracts.COUNT LOOP
            dbms_output.put_line(lpad(' ',5 - length(i))||i||' : '||l_contracts(i).contract_id);
         END LOOP;

         -- DevNote: %NOTFOUND check must be at the end of the loop 
         EXIT WHEN contracts_cur%NOTFOUND;

      END LOOP;

      CLOSE contracts_cur;
   END bulk_with_limit;

begin
   bulk_with_limit(n_start_in => 10, n_end_in => 100000, limit_in => 100);
end;

Count occurences of a character in a string (Oracle)

2009-03-30T10:57:00.010-05:00

Recently, I needed to see how many times the "@" [at sign] was in a particular column more than once.

So Abhijit noticed an interesting detail, if you are counting characters where the input string is nothing but the same repeating search character, then the resulting count is incorrect. I concatenated in a "random" character to prevent this scenario. This only affects the "pure Oracle solution".

Pure Oracle Solution (Updated)

select
   :str as "string",
   :chr as "character",
   length(:str||chr(1)) - length(translate(:str||chr(1),chr(0)||:chr,chr(0))) as "count"
from dual;

select *
from 
(
   select
      ia.internet_address_id as "InternetAddressID",
      ia.internet_address as "string",
      '@' as "character",
      length(ia.internet_address||chr(1)) - length(translate(ia.internet_address||chr(1),chr(0)||'@',chr(0))) as "occurences"
   from internet_address ia
)
where "occurences" > 1;

Regular Expression Based Solution (Thanks Jan Leers)

SELECT *
FROM
(
 SELECT 
  ia.internet_address_id as "InternetAddressID",
      ia.internet_address as "InternetAddress",
  LENGTH(REGEXP_REPLACE(ia.internet_address,'[^@]')) as "Occurences"
 FROM 
  internet_address ia
)
WHERE "Occurences" > 1;

Syntax Highlighting Posted Code Snippets

2009-01-21T14:22:00.021-06:00

For years, I have struggled with how to share my code snippets on the web. I wanted the "fancy" look, but I didn't want to waste a bunch of time converting my code to html/css.

I'd see some sites that had a nice look, but when I looked under the covers, they were invoking proprietary server side engines to generate the the "pretty print code".

Today, I found the answer on GoogleCode: Syntaxhighlighter, a free DYNAMIC Javascript-based syntax highlighter.

The custom CSS for sourcecodetable is mine. The only change that I made was to go...

From:

<pre class="sourcecodetable">
... some C# code here ...
</pre>

To:

<pre name="code" class="C#">
... some C# code here ...
</pre>

It supports:

C#
Java
XML
PHP
Python
SQL
VB
C++
CSS
Html
Delphi

I'm really impressed with how well it looks.

Link: http://alexgorbatchev.com/wiki/SyntaxHighlighter

Link: http://geektalkin.blogspot.com/2009/11/embed-code-syntax-highlighting-in-blog.html

Redirects (Html, ASP.Net, JSP, ...)

2009-01-20T12:23:00.020-06:00

Html (not generally recommended)

<html>
<head>
<META HTTP-EQUIV="Refresh"CONTENT="0; URL=http://www.new-url.com">
</head>
<body>
This page is redirecting your browser to www.new-url.com<br>
Please, click <a href="http://www.new-url.com">here</a> if redirection doesn't work.
</body>
</html>

ASP.Net

<script runat="server">
private void Page_Load(object sender, System.EventArgs e)
{   
   Response.Status = "301 Moved Permanently";
   Response.AddHeader("Location","http://www.new-url.com/");
}
</script>

JSP

<%
response.setStatus(301);
response.setHeader("Location", "http://www.new-url.com/" );
response.setHeader("Connection", "close" );
%>

Outlook - Red "X" on Image Paste

2008-12-02T11:08:00.009-06:00

Delete the contents of the folder that is at "string value" RegEdit
Path : My Computer\HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Service
String Name : OutlookSecureTempFolder
String Value : D:\Documents and Settings\JVogel\Local Settings\Temporary Internet Files\OLK41\

Html Fieldset Rounded Corners - Updated (1/18/2010)

2008-11-25T14:56:00.023-06:00

I don't usually prefer anything about Internet Explorer. However, IE's default presentation for the Html fieldset tag just looks nicer. Firefox, on the otherhand, defaults to an ugly square box presentation. So, to try to get Firefox to look more like IE (something I never thought that I would say) ...

fieldset { 
   -moz-border-radius-bottomleft: 7px;
   -moz-border-radius-bottomright: 7px;
   -moz-border-radius-topleft: 5px;
   -moz-border-radius-topright: 7px;
   -webkit-border-radius: 7px;
   border-radius: 3px;
}

This works for IE, Firefox, and now Chrome. It still doesn't look "nice" in Safari.

If you absolutely must have rounded corners: http://www.spiffycorners.com. However, I caution you against the author's approach because it requires too much Html for an effect that will be cleanly implemented in CSS3. Again, this is only a nice to have.

Example:

Title contents

ASP.Net - Dynamically Adding a CSS/StyleSheet Reference at run-time

2008-11-05T11:15:00.006-06:00

Per Michael, I have removed a technique that was only available in Beta 1.

protected void Page_Init(object sender, EventArgs e)
{
   HtmlLink link = new HtmlLink();
   link.Href = "~/StyleSheet.css";
   link.Attributes.Add("rel", "stylesheet");
   link.Attributes.Add("type", "text/css");
   Page.Header.Controls.Add(link);
}

Print.css

2008-10-07T13:57:00.006-05:00

Recently, I ran into a case where a user's printout of an order confirmation was cropping a portion along the rights side of the page.

By default, almost all browers print in "Scale-to-Fit" mode. This particular user happened to be using Print at "100%".

Changed our html/aspx page to the following...

Html

<head id="head" runat="server">
   <link href="/shared/css/style.css" type="text/css" rel="stylesheet">
   <link href="/shared/css/print.css" type="text/css" rel="stylesheet" media="print">
<head>

CSS

body
{
background: white;
margin: 0;
font-size: 0.75em;
width: 660px;
}

#PageContainer
{
width:640px;
}

a:link, a:visited
{
color: #520;
background: transparent;
font-weight: bold;
text-decoration: underline;
}

Getting the current Oracle version (SQL Select)

2008-08-12T13:57:00.007-05:00

Queries :

select *
from v$version;

select *
from product_component_version;

Results :

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production CORE 11.1.0.6.0 Production TNS for Solaris: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production

PRODUCT	VERSION	STATUS
NLSRTL	11.1.0.6.0	Production
Oracle Database 11g Enterprise Edition	11.1.0.6.0	64bit Production
PL/SQL	11.1.0.6.0	Production
TNS for Solaris:	11.1.0.6.0	Production

gzip Http Compression in IIS

2008-08-07T16:54:00.012-05:00

References URLS:

In order to enable compression (herein gzip), there are several steps that have to be completed.

Enable Compression in IIS

From the IIS snap-in, right-click on the Web Sites node and click on Properties
Select the Service tab - Enable Compress application files
Enable Compress static files
Change Temporary Directory to the folder that you created above, or leave it at it's default
Set the max size of the temp folder to something that the hard drive can handle. e.g. 1000.
Save and close the Web Site Properties dialog

Note: The temporary compress directory is only used for static pages. Dynamic pages aren't saved to disk and are recreated every time so there is some CPU overhead used on every page request for dynamic content.

<IIsCompressionScheme Location ="/LM/W3SVC/Filters/Compression/deflate"
  HcCompressionDll="%windir%\system32\inetsrv\gzip.dll"
  HcCreateFlags="0"
  HcDoDynamicCompression="TRUE"
  HcDoOnDemandCompression="TRUE"
  HcDoStaticCompression="FALSE"
  HcDynamicCompressionLevel="0"
  HcFileExtensions="htm
    html
    txt
    css"
  HcOnDemandCompLevel="10"
  HcPriority="1"
  HcScriptFileExtensions="asp
    dll
    exe
    aspx
    asmx
    js"
>
</IIsCompressionScheme>
<IIsCompressionScheme Location ="/LM/W3SVC/Filters/Compression/gzip"
  HcCompressionDll="%windir%\system32\inetsrv\gzip.dll"
  HcCreateFlags="1"
  HcDoDynamicCompression="TRUE"
  HcDoOnDemandCompression="TRUE"
  HcDoStaticCompression="TRUE"
  HcDynamicCompressionLevel="0"
  HcFileExtensions="htm
    html
    txt
    css"
  HcOnDemandCompLevel="10"
  HcPriority="1"
  HcScriptFileExtensions="asp
    dll
    exe
    aspx
    asmx
    js"
>
</IIsCompressionScheme>

From a CMD prompt, iisreset stop

Now save metabase.xml

From a CMD prompt, iisreset

Prototyping in ASP.Net - asp:SqlDataSource Example

2008-04-16T11:32:00.012-05:00

Recently, I've been doing a bunch of ASP.Net. One of ASP.Net's best features is its ability to be used for realistic rapid prototyping.

Here is a simple example that executes a SQL query and displays the results.

In the aspx... (be sure and notice the "$" in the connection string)

<asp:sqldatasource id="SqlDSCoreCoverages" runat="server" connectionstring="<%$ ConnectionStrings:ConnectionString_DEV %>" >"
       ProviderName="Oracle.DataAccess.Client"
       SelectCommand="
             select coverage_description, display_order
             from prod_cov
        whereid = :argProductVersionId
             order by display_order
       "
       EnableCaching="True">
    </asp:SqlDataSource>

<table cellpadding="3" style="border: 1px #333333 solid;">
                <tr><td>
                   <asp:gridview id="GridView1" runat="server" datasourceid="SqlDSCoreCoverages" autogeneratecolumns="False" gridlines="None">
                      <headerstyle cssclass="SectionHeader">
                      <columns>
                         <asp:BoundField DataField="coverage_description"  HeaderText="Included Core Coverages"
                            ReadOnly="True" SortExpression="coverage_description" />
                      </columns>
                   </asp:GridView>
                </td></tr>
             </table>

In the aspx.cs [codebehind] ...

this.SqlDSCoreCoverages.SelectParameters.Add("argProductVersionId", productVersionId.ToString());

this.SqlDSCoreCoverages.DataBind();

Feuerstein's article on BULK COLLECT and %NOTFOUND

2008-04-10T15:40:00.012-05:00

Steven Feuerstien wrote an informative article (http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html) on using BULK COLLECT with LIMIT in a "reasonable" manner. Much of this content is drawn from that article. Basically, the use the LIMIT construct requires a secondary loop to process the smaller "batch". Our DBAs require us to do this kind of "batching" to put less strain on Replication. Smaller COMMITs going across the pipe rather than a 100,000 COMMIT. In otherwords, "commit every x number of rows." [Search engine fodder]

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
   CURSOR employees_cur
   IS
       SELECT * FROM employees;

   TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
       INDEX BY PLS_INTEGER;

   l_employees employees_aat;
BEGIN  
   OPEN employees_cur;
   LOOP
       FETCH employees_cur
           BULK COLLECT INTO l_employees LIMIT limit_in;

       FOR indx IN 1 .. l_employees.COUNT LOOP
           update_compensation (l_employees(indx));
       END LOOP;

       COMMIT; -- Commit batch of size LIMIT 

       EXIT WHEN l_employees.COUNT < limit_in;

   END LOOP;

   CLOSE employees_cur;
END process_all_rows;

To summarize his second article, don't use %NOTFOUND with BULK COLLECT and LIMIT. Use .COUNT. Secondary Example

set serveroutput on;
clear;

declare
  CURSOR contracts_cur
   IS
       SELECT * FROM contract where rownum <= 50;

   TYPE t_contract IS TABLE OF contracts_cur%ROWTYPE
       INDEX BY PLS_INTEGER;

   l_contract t_contract;
   
BEGIN  
   OPEN contracts_cur;
   LOOP
       FETCH contracts_cur
           BULK COLLECT INTO l_contract LIMIT 10;

       FOR indx IN 1 .. l_contract.COUNT LOOP
        common_func.display_output(indx||' : '||l_contract(indx).contract_id);
       END LOOP;

       EXIT WHEN l_contract.COUNT = 0;

   END LOOP;

   CLOSE contracts_cur;
   
   EXCEPTION 
    WHEN OTHERS THEN 
       common_func.display_output(SQLERRM);
   
END;

Handling ORA-04068: Existing state of packages has been discarded

2008-03-20T13:31:00.016-05:00

How to deal with the frustrating ORA-04068: existing state of packages has been discarded error that crops up while in the circular cycle of "code..execute..debug".

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "PKG.PROC" has been invalidated
ORA-04065: not executed, altered or dropped package "PKG.PROC"
ORA-06508: PL/SQL: could not find program unit being called

Previously, the only thing I knew to do was logout and then log back in. This works, but it is kind of a waste of time. I knew there had to be a better way because my PL/SQL Editor (PL/SQL Developer) doesn't require this step. I probably could have asked the guys at AllAround, but I think that I found the answer:

begin
   DBMS_SESSION.RESET_PACKAGE;
   -- execute routine... 
end;

Addendum:
To respond to Abida's comment, you can run into this issue even if all the packages under your ID are valid. Your debug/test session however may not "see" the valid state of these packages. The snippet above fixes the issue with our debug/test session. Thanks, Jason

Upsert / Merge within the Same Table (Oracle)

2008-01-15T09:50:00.007-06:00

Okay, I'm a huge fan of using Oracle's %ROWTYPE to define records structures. I love writing code like the following:

DECLARE

   rec_contract  CONTRACT%ROWTYPE := Null;

BEGIN

   -- ... retrieval logic and business logic ...

   -- "Set" logic
   UPDATE CONTRACT
   SET ROW = rec_contract
   WHERE CONTRACT_ID = rec_contract.CONTRACT_ID;

   IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO CONTRACT
      VALUES rec_contract;
   END IF;
END;

Code like the above is really convenient for writing "setters". It can easily be extended to handle DELETE logic too. Now, recently I needed to copy a bunch of data within the same table but with a different primary key. I started to write a bunch of code like the above, but I just needed some of code. To cut to the chase, the code below is an example of how to do an "UPSERT" (like a MERGE) but within the same table [which is impossible with the MERGE command]. Source:

drop table test;
clear;

create table test(
   id number(10),
 name varchar2(50)
);

insert into test(id,name) values (1,'First row');

insert into test
   using select 2,'Row two' from dual;

insert into test
   using select 3,'Row three - unmodified' from dual;

commit;

select * from test;

update test set (id,name) = (select 3,'Row three - modified' from test where id = 3) where id = 3;

commit;

select * from test;

Results:

Table created
 
1 row inserted
 
1 row inserted
 
1 row inserted
 
Commit complete
 
         ID NAME
----------- --------------------------------------------------
          1 First row
          2 Row two
          3 Row three - unmodified
 
1 row updated
 
Commit complete
 
         ID NAME
----------- --------------------------------------------------
          1 First row
          2 Row two
          3 Row three - modified

Suzuki Katana - Tire Pressure

2007-10-17T12:52:00.000-05:00

I kept misplacing my notes about the tire pressure for my 2001 Katana 750... so I'm putting them here. 220 35/38 250 36.5/39.5 Per http://www.drivegreen.com/Motorcycle_Tire_Pressure/SUZUKI.shtml, GSX750 Katana (1989-2002) 34 36

Oracle: InstantClient, TNSPING, and PL/SQL Developer

2007-10-12T00:03:00.000-05:00

Well, anyone that has tried to use tnsping with Oracle's InstantClient (http://www.oracle.com/technology/tech/oci/instantclient/index.html) was in for a big nasty surprise... it's not included. Not only is not included, you can't download a version that is compatible with InstantClient. I've been a fan of tnsping for some time. It has always been valuable as a quick way to test the "reachability" factor of a particular database. I now have to admit that it's not required since SQLPlus technically can perform this functionality. Well, habits die hard.

Goal: Get InstantClient working with tnsping and AllAroundAutomation's PL/SQL Developer (http://www.allroundautomations.nl/plsqldev.html).

Solution:

Disclaimer:

I don't want to tell you how long it took me to figure this all out, but it was way too long.
It's an unacceptable solution from a long-term maintenance, support standpoint.

Steps:

Download the appropriate InstantClient "base" (in other words, English-only or International).
Download the InstantClient SQL-Plus "add-on".
Extract both Zip files into the same directory [for this example, "c:\oracle\instantclient"].
At this point, we haven't done anything special.
Define an environment variable oracle_home that points to the location of your InstantClient directory (e.g.: "c:\oracle\instantclient").
Create a subdirectory called "network" (c:\oracle\instantclient\network).
In "network", create a subdirectory called "admin".
If desired, put your tnsnames.ora and sqlnet.ora in c:\oracle\instantclient\network\admin [technically, this isn't required but it follows Oracle's "standards"].
Define an environment variable tns_admin that points to the location of your tnsnames.ora (if you are using one).
In "network", create a subdirectory called "mesg".
From an Oracle 10.x Fat client installation, copy tnsus.msb into c:\oracle\instantclient\network\mesg. This is the file that tnsping uses to "lookup" the message text while running.
At this point, you should be able to fire up PL/SQL Dev and still have things work.

Okay, now the weird part starts

Create a subdirectory called "bin" (c:\oracle\instantclient\bin), and yes, it must be called bin.
From an Oracle 10.x Fat client installation the following files into c:\oracle\instantclient\bin:

heteroxa10.dll
oci.dll
ocijdbc10.dll
ociw32.dll
oraclient10.dll
oracommon10.dll
oracore10.dll
orageneric10.dll
orahasgen10.dll
oraldapclnt10.dll
oran10.dll
orancds10.dll
orancrypt10.dll
oranhost10.dll
oranl10.dll
oranldap10.dll
oranls10.dll
orannzsbb10.dll
oranro10.dll
orantcp10.dll
orantns10.dll
oraocci10.dll
oraociicus10.dll
oraocr10.dll
oraocrb10.dll
oraplp10.dll
orapls10.dll
ORASLAX10.DLL
orasnls10.dll
orasql10.dll
Orasqlplusic10.dll
oraunls10.dll
orauts.dll
orauts_imp.dll
oravsn10.dll
oraxml10.dll
sqora32.dll
sqoras32.dll
sqresja.dll
sqresus.dll
tnsping.exe

Now, copy all the files from c:\oracle\instantclient into c:\oracle\instantclient\bin except those that already exist.

At this point, InstantClient is "valid", PL/SQL Developer works [but it's actually using c:\oracle\instantclient\bin, and tnsping should work from c:\oracle\instantclient\bin. Talk about smells bad. So if you want tnsping for InstantClient, create this nightmare-ish drink from this recipe or complain to Oracle and ask for a InstantClient-compatible version of tnsping. Jason

Google Search

2007-08-09T12:40:00.000-05:00

I recently discovered that my Blog wasn't being picked up correctly by Google. A little odd since Blogger/Blogspot is owned by Google. I hoping that it is just an issue with bad keywords/meta-tags.

Rails : Maintaining an Oracle connection (preventing a timeout)

2007-02-19T10:14:00.001-06:00

Kubo posted a good note about how to maintain an "active" connection to Oracle from Rails. Add the following code at the end of 'config/environment.rb' as a workaround.

Thread.new(ActiveRecord::Base.connection.raw_connection) do |conn|
  # run a SQL every one hour.
  while true
    sleep(3600)
    conn.exec('select 1 from dual') {}
  end
end

Ruby : WebService - GetQuote

2007-02-02T01:01:00.001-06:00

require 'soap/wsdlDriver'

wsdl = "http://www.webservicex.net/stockquote.asmx?wsdl"

params = {:symbol => "IBM"}

drv = SOAP::WSDLDriverFactory.new(wsdl).create_rpc_driver
drv.wiredump_dev = STDOUT if $DEBUG
result = drv.GetQuote(params)

puts "\n\nGetQuote : "+result.inspect+"\n\n"

if (result != nil)

 puts "\n\Result.getQuoteResult.methods : "+result.getQuoteResult.methods.sort.join("\n").to_s+"\n\n"
#   puts "Symbol : "+result.getQuoteResult.stockQuotes.Stock.Symbol

end

Ruby - Code Snippet - Methods List

2007-01-31T10:35:00.001-06:00

Just a snippet how to print out a sorted list of an object's methods.

puts "\n\object.methods : "+ object.methods.sort.join("\n").to_s+"\n\n"

Ruby - Default Values for Method Arguments

2006-12-13T09:42:00.001-06:00

>> def test(args)
>  >>   puts args.values_at(:a, :b, :c)
>  >> end
> => nil
>  >> test :a => 1, :c => 2

def test(args)
>>   args = { "a" => "default a",
?>            "b" => "default b",
?>            "c" => "default c" }.merge(args)
>>   puts args.values_at("a", "b", "c")
>> end
=> nil
>> test "a" => "some a", "c" => "some c"
some a
default b
some c
=> nil

Source :

def test(args)
  args[:a] ||= 'defaultA'
  args[:b] ||= 'defaultB'
  args[:c] ||= 'defaultC'
  puts args.indexes(:a, :b, :c)
end

Result: Breaks when...

test :a => false
test :a => nil
test 'a' => true

Ruby - How do I catch a missing method on a passed block

2006-12-13T09:38:00.001-06:00

J.Vogel - Copied... from Erik V. - http://www.erikveen.dds.nl/

module Kernel
  def block_with_method_missing(block1, &block2)
    class << block1
      self
    end.module_eval do
      define_method(:method_missing, &block2)
      define_method(:call){block1.instance_eval(&block1)}
    end

    block1
  end
end

class Foo
  def bar(&block)
    block = method_missing_inspect(block)

    block.call
  end

  def method_missing_inspect(block)
    block_with_method_missing(block) do |method_name, *args|
      p [:mm, method_name, args]
    end
  end
end

Foo.new.bar{baz(1, 2, 3)}

Rails : View - Partials

2006-12-06T23:52:00.001-06:00

Partials are awesome...

<%= render :partial => "coverages"  unless @base_coverages.blank? %>

Rails - Automatic Reloading and Models

2006-12-06T22:31:00.001-06:00

I'm fairly new to RoR. I have to admit that I'm pretty excited about exiting the Code-Compile-Deploy-Run cycle because of config.cache_classes = false in \environments\development.rb. However, I've discovered that doesn't apply to Models. Models are loaded at server startup. Therefore, we're back to the stop..start to pickup Models changes. I haven't tried either of these yet!

Two options:

Reload the models through console

ruby script\console
Dispatcher.reset_application!

http://www.wejn.org/stuff/rails-reloadable-custom-models.html

Ruby: View - Inserting Text

2006-12-06T21:56:00.001-06:00

<%= "hello" %>

<%= concat("hello", binding) %>

Rails: Snippet - View - Render a Object into a Table

2006-12-06T13:31:00.001-06:00

Please don't get all bent out of shape. My Rails code isn't perfect, but I wanted to post some of my snippets that do work!

<% if (@base_coverages != nil) && (@base_coverages.length > 0) %>
<>
<% @base_coverages.each do |i| %>
<>">
  <><%= i.coverage_id %>< /td>
  <><%= i.external_description %>< /td>
< /tr>
<% end %>
< /tbody>< /table>
<% end %>

Ruby : Defaulted optional arguments

2006-12-06T00:24:00.001-06:00

I'd seen this technique, but I'd lost it.

def placeholder(requiredarg, options = {})
  # set default options
  o = { :class => 'placeholder', :tag => 'p' }.merge(options)

  ...
end

Rails - Value Checking

2006-12-05T09:21:00.000-06:00

These are all options posted to me:

return [] unless myvalue
return unless arguments.all?
'arguments.all?' will return true if and only if none of the collection members is false or nil.
If an argument can be false, you could do this:
return unless arguments.all? { |arg| !arg.nil? }

raise ArgumentError, "Parameter X is required" if x.blank?

Enterprise Ruby / Rails : Links to Review

2006-11-29T16:14:00.000-06:00

This is just a blog note to myself to review :

Jason

PL/SQL : Example using SysTimeStamp (milliseconds for SysDate)

2006-11-27T13:05:00.005-06:00

Oracle's SYSDATE only goes to the second. If you want more precision, then you need to use the new SYSTIMESTAMP (available starting in Oracle 9). Source :

declare

ltimestamp_start       timestamp;
ltimestamp_stop        timestamp;
linterval_diff         interval day to second;

ldt_temp               date;

begin

ltimestamp_start := systimestamp;
dbms_output.put_line(ltimestamp_start);

  -- Routine to performance test... 
  for i in 1 .. 10000 loop
    select sysdate into ldt_temp from dual;
  end loop;

ltimestamp_stop := systimestamp;
dbms_output.put_line(ltimestamp_stop);

linterval_diff := ltimestamp_stop - ltimestamp_start;

dbms_output.put_line(CHR(10)||LPAD('=',22,'=')||CHR(10));
dbms_output.put_line('  Runtime Difference'||CHR(10)||LPAD('=',22,'='));
dbms_output.put_line(
     '     Days : '||EXTRACT(DAY FROM linterval_diff)||CHR(10)||
     '    Hours : '||EXTRACT(HOUR FROM linterval_diff)||CHR(10)||
     '  Minutes : '||EXTRACT(MINUTE FROM linterval_diff)||CHR(10)||
     '  Seconds : '||EXTRACT(SECOND FROM linterval_diff) );
end;

Results :

27-NOV-06 01.26.50.118424 PM
27-NOV-06 01.26.51.125141 PM

======================
Runtime Difference
======================
Days : 0
Hours : 0
Minutes : 0
Seconds : 1.006717

PL/SQL: Joinrows - Convert multiple [returned cursor] rows into one row

2006-11-27T10:24:00.006-06:00

This routine converts multiple rows into a single row. The trick is to pass the data via a SYS_REFCURSOR (see the CURSOR call in the example). Example Usage :

select joinrows(cursor(select contract_id from contract where rownum < 10 )) from dual;

CREATE OR REPLACE FUNCTION joinrows(
 sysref_cursor_in      sys_refcursor,
 s_delimiter_in        VARCHAR2 := ',' )
RETURN VARCHAR2
IS
   s_value            VARCHAR2(32767) := Null;
   s_result           VARCHAR2(32767) := Null;
   s_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN

   LOOP
      FETCH sysref_cursor_in
         INTO s_value;

      EXIT WHEN sysref_cursor_in%NOTFOUND;

      IF (s_result IS NOT NULL) THEN
       s_result := s_result||s_delimiter;
      END IF;

      s_result := s_result||s_value;

   END LOOP;

   RETURN s_result;

END joinrows;

Results:

0,2,3,9,37,404,818,991,1178

Ruby: Invoking a PL/SQL Package with Array args

2006-11-21T10:57:00.008-06:00

I'm fairly new to Ruby (and subsequently to Rails). I would be normally be labeled an "Enterprise Developer"... yada, yada. Anyway, I'm trying to invoke a Oracle PL/SQL Package from Ruby that has IN and OUT arguments that are arrays (TABLE OF VARCHAR2 INDEX BY BINARY-INTEGER, in Oracle PL/SQL terms). We have a fairly extensive library of PL/SQL that I want to reuse. This technique also works with Oracle Types (CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000);).

I've spent several weeks trying to come up with a solution. First, the best solution would be for Oracle to write the OCI driver for Ruby. OCI8 [http://rubyforge.org/projects/ruby-oci8/] (by Kubo Takehiro) is wonderful, but he is still a person. For companies to adopt and support Ruby/Rails, the support needs to be more robust. Upper Management and Operations resist open source. Single person supported software is easier for them to dismiss. I would prefer that Oracle had an OCI expert write and maintain the library (along the lines of their support for PHP). If this were the case, then the driver could support returning arrays natively.

My PL/SQL Packages :

CREATE OR REPLACE PACKAGE common_func IS
TYPE string_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
END common_func;
/
CREATE OR REPLACE PACKAGE BODY common_func
IS
BEGIN
   NULL;
END common_func;
/
create or replace package ruby_test is

function f_ruby(s in number,t out varchar2,st out common_func.string_table)

end ruby_test;
/
create or replace package body ruby_test is

function f_ruby(s in number,t out varchar2,st out common_func.string_table)
return varchar2
is
begin
   t := 'outta here';
   st(1) := 'array 1';
   st(2) := 'array 2';
   return 'Ruby rocks '||TO_CHAR(NVL(s,5))||' times!';
end;

begin
   null;
end ruby_test;

Desired Ruby :

cursor = conn.parse("BEGIN :result := ruby_test.f_ruby(s => :in,t => :out,st => st); END;")

cursor.bind_param(':result', nil, String, 100)
cursor.bind_param(':in', 10)
cursor.bind_param(':out', nil, String, 100)
# cursor.bind_param(':out_array', Array, 100) <= I tried this too!
cursor.bind_param(':out_array', String[], 100)

cursor.exec()

p cursor[':result'] # => 'Ruby rocks 10 times!'
p cursor[':out'] # => 'outta here'
p cursor[':out_array'] # => 'st(1) = array 1, st(2) = array 2' !!! Fails

First Solution : Convert the array to a string, bind to that string, and then split the delimited string apart on the Ruby side. I don't care for this solution for a couple of reasons :

Size issue at 32K
What delimiter should I use? How do I know that it will be the "right" delimiter?

First Solution (Revised) : I could switch from a string bind argument to a CLOB. OCI8 supports CLOBs, but I couldn't get it to work. The documentation is incomplete, and I'm not fluent enough in Ruby. Hints back to my issue with the library being solely maintained.

Current Solution : Convert the string array to a reference cursor (SYS_REFCURSOR) and use OCI8's bind to OCI::CURSOR support. Type :

CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000)

Cursor Package :

CREATE OR REPLACE PACKAGE cursor_func IS
/**
Converts an array into a SYS_REFCURSOR (System Reference Cursor)

@Return
{*} SysRefCursor Success
{*} Exception Error
*/
FUNCTION f_array_to_SYSREFCURSOR(
   st_array_in       IN   common_func.STRING_TABLE )
RETURN SYS_REFCURSOR;
PRAGMA RESTRICT_REFERENCES(f_array_to_sysrefcursor,WNDS,TRUST);

END cursor_func;
/
CREATE OR REPLACE PACKAGE BODY cursor_func IS

/**
Converts an array into a SYS_REFCURSOR (System Reference Cursor)

@Return
{*} SysRefCursor Success
{*} Exception Error
*/
FUNCTION f_array_to_SYSREFCURSOR(
   st_array_in       IN   common_func.STRING_TABLE )
RETURN SYS_REFCURSOR
IS
   lsysrefcursor_array  SYS_REFCURSOR;
   le_error      EXCEPTION;

   lst_prodtypes    prod_types.type_string_array;

BEGIN

   lst_prodtypes := common_func.convert_table(st_array_in);

  OPEN lsysrefcursor_array FOR
     SELECT column_value FROM TABLE(cast(lst_prodtypes AS prod_types.type_string_array));

   RETURN lsysrefcursor_array;

END f_array_to_SYSREFCURSOR;

BEGIN
   Null;
END cursor_func;

Ruby Source :

plsql = conn.parse(
"DECLARE "+
"   st common_func.string_table; " +
"BEGIN "+
"   :result := ruby_test.f_ruby(s => :in,t => :out, st => st); " +
"   :cst := cursor_func.f_array_to_SYSREFCURSOR(st_array_in => st); " +
"END;")

plsql.bind_param(':result', nil, String, 100)
plsql.bind_param(':in', 10)
plsql.bind_param(':out', nil, String, 100)
plsql.bind_param(':cst', OCI8::Cursor)

plsql.exec()

puts "\nResults from returning a SysRefCursor\n"

p plsql[':result'] # => 'Ruby rocks 10 times!'
p plsql[':out'] # => 'outta here'

cursor = plsql[':cst']

plsql.close

x = ''
while r = cursor.fetch()
x = x + r.join(', ') + "\n"
end
cursor.close() # <= Don't forgot this

puts x

And success, finally!

Results from returning a SysRefCursor
"Ruby rocks 10 times"
"outta here"
array 1
array 2

Warning : There are two potential gotchas to this solution :

If the developer forgets the cursor.close statement, the transaction could be jeopardized if there are too many cursors opened. I don't remember which ORA-##### this is.
A single database session could run into issues with just having too many reference cursors open at any one instant.

Native support for arrays would be a blessing.

Posts of Interest :

http://sqlrelay.sourceforge.net/sqlrelay/programming/rubydbi.html

Rails: Hello World / HelloWorld

2006-11-13T13:53:00.002-06:00

Assuming that Ruby and Rails are already installed...

Go to the appropriate top level directory [where you want to create your new application]. For me, c:\develop\rubysrc.

rails helloworld
cd helloworld
ruby script\generate controller helloworld

You can either:

Use the controller DIRECTLY...

class HelloController < ApplicationController
def index
render_text << EOF
<html>
<head>
<title>Rails HelloWorld from Controller</title>
</head>
<body>
HelloWorld from Controller
</body>
</html>
EOF
end
end

Use a view... Create a file called index.rhtml in app/views/helloworld, containing "Hello World"

<html>
<head>
<title>Rails HelloWorld from View</title>
</head>
<body>
HelloWorld from View
</body>
</html>

ruby script\server
Navigate to http://localhost:3000/helloworld in your browser and be greeted with your friendly application: "Hello World"

PHP: Autofilled Forms

2006-11-07T16:33:00.000-06:00

Source URL : http://www.onlamp.com/pub/a/php/2006/03/16/autofill-forms.html

This looks like a clever way to design PHP forms (be sure and check out the forums for issues).

Oracle: SQL - Converting one row into two rows [multiple rows]

2006-11-03T16:34:00.013-06:00

Problem : A single row needs to be converted into two rows.

Solution 1 :

Prerequite :

CREATE OR REPLACE TYPE PROD_TYPES.TYPE_STRING_ARRAY AS TABLE OF VARCHAR2(2000)

Function : This routine handles an unlimited number of desired rows as opposed to Solution 2.

CREATE OR REPLACE FUNCTION split (
 s_delimited_list_in     VARCHAR2,
 s_delimiter_in          VARCHAR2 := ',')
RETURN prod_types.type_string_array PIPELINED
IS
/*
@Usage Example:
select * from table(split('one,two,three'));
*/
 l_idx               PLS_INTEGER;
 l_list              VARCHAR2(32767) := s_delimited_list_in;
 l_value             VARCHAR2(32767);
 ls_delimiter        VARCHAR2(100) := NVL(s_delimiter_in,',');
BEGIN
LOOP
 l_idx := INSTR(l_list,ls_delimiter);

 IF (l_idx > 0) THEN

    PIPE ROW(SUBSTR(l_list,1,l_idx-1));
    l_list := SUBSTR(l_list,l_idx+LENGTH(ls_delimiter));
  
 ELSE

    PIPE ROW(l_list);
    EXIT;
  
 END IF;
END LOOP;

RETURN;

END SPLIT;

Sorry, there was a typo in the above script. It was fixed on 2/9/2009. Jason

The reverse of "split" is "joinrows".

Solution 2 :

Here is a simple example of converting one row into multiple rows. "atomic_contract" is returning a single row, but we need two columns from it to be returned as two rows.

select *
from (
select ac.*,
decode(t.row_count,1,ac.sk_seller,
                  2,ac.sk_buyer, null) sk_customer
from atomic_contract ac,
( select 1 row_count from dual
union all
select 2 row_count from dual) t
where contract_id = 3562777)

JavaScript and AJAX

2006-10-27T10:05:00.000-05:00

Tweaking Firefox via userChrome.css

2006-10-26T09:20:00.001-05:00

My Changes :

/*
* Edit this file and copy it as userChrome.css into your
* profile-directory/chrome/
*/

/*
* Do not remove the @namespace line -- it's required for correct functioning
*/
@namespace url("http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul"); /* set default namespace to XUL */

/* Make the Search box flex wider */
#search-container, #searchbar {
width: 280px !important;
}

/* Unread tabs */
#content tab:not([selected]) {
font-style: italic !important;
color: #008001 !important;
}

/* Normalise the title of read, but unselected tabs */
#content tab[selected="false"] {
font-style: normal !important;
color: Black !important;
}

/* Active Tab... */
tab[selected="true"] {
background-color: rgb(222,218,210) !important;
color: black !important;
font-weight: bold !important;
background-image: url(gradient_sunorange_top.gif) !important;
}

/* Remove the 'Go' button */
#go-button {
display: none !important; }

/* Show tab favicon only on selected tab */
tab:not([selected="true"]) .tab-icon {
display: none !important; }

/*
 * Adjust font and size of Bookmarks Toolbar items : http://userstyles.org/style/show/901
 */
#bookmarks-ptf .toolbarbutton-text {
 /* font-size: 10px !important;*/
 font-size: 8pt !important;
}


/* Make inactive tabs hardly visible 
#content tab:not([selected="true"]) {
  -moz-opacity: 0.5 !important; }
*/


/* Change color of tabs NOT currently selected... regardless of read or not
tab:not([selected="true"]) {
  font-style: italic !important;
  color: Fuchsia !important;
}
*/

/* Slightly rounded address and search bar corners 
#urlbar {
-moz-appearance: none !important;
-moz-border-radius: 3px !important;
  padding-right: 1px !important; }

*/

VMWare...

2006-10-24T14:17:00.000-05:00

CSS and Html Resources

2006-10-24T14:12:00.001-05:00

http://www.alvit.de/handbook/

JavaScript and AJAX

2006-10-24T14:12:00.000-05:00

http://www.codecoffee.com/articles/9tips.html

Rails and Ruby Links to Review

2006-10-23T23:31:00.000-05:00

WebServices

ActionWebService: WebServices on Rails (http://manuals.rubyonrails.com/read/book/10)

User Interface

Intuitive Date Input Selection (http://datebocks.inimit.com/)

SingleTableInheritance (STI)

Debugging

Full Information for Ruby Errors (http://www.bigbold.com/snippets/posts/show/2427)

Deployment

Capistrano: Automating Application Deployment (http://manuals.rubyonrails.com/read/book/17)

Translation

Using GetText to Translate your Rails Application (http://manuals.rubyonrails.com/read/book/16)

CSS Example: Table Design Control

2006-10-17T09:11:00.000-05:00

CSS Example


<style type="text/css">
table.sample {
    border-width: 1px 1px 1px 1px;
    border-spacing: 2px;
    border-style: outset outset outset outset;
    border-color: gray gray gray gray;
    border-collapse: separate;
    background-color: white;
}
table.sample th {
    border-width: 1px 1px 1px 1px;
    padding: 1px 1px 1px 1px;
    border-style: inset inset inset inset;
    border-color: gray gray gray gray;
    background-color: white;
    -moz-border-radius: 0px 0px 0px 0px;
}
table.sample td {
    border-width: 1px 1px 1px 1px;
    padding: 1px 1px 1px 1px;
    border-style: inset inset inset inset;
    border-color: gray gray gray gray;
    background-color: white;
    -moz-border-radius: 0px 0px 0px 0px;
}
</style>

<table class="sample">
<tr>
    <th>Header</th>
    <td>Content</td>
</tr>
</table>

Blogger Items To Review

2006-10-10T15:59:00.000-05:00

http://help.blogger.com/bin/answer.py?answer=42215&query=how%20do%20I%20add%20tags&topic=&type=f
http://help.blogger.com/bin/answer.py?answer=41447&query=how%20do%20I%20add%20tags&topic=&type=f
http://help.blogger.com/bin/answer.py?answer=42528&query=how%20do%20I%20add%20tags&topic=&type=f
http://help.blogger.com/bin/answer.py?answer=46995&query=how%20do%20I%20add%20tags&topic=&type=f
http://help.blogger.com/bin/answer.py?answer=41641
http://userscripts.org/scripts/source/1989.user.js
http://help.blogger.com/bin/answer.py?answer=42095
http://userscripts.org/scripts/show/2182
http://userscripts.org/scripts/show/4712
http://blogfresh.blogspot.com/2005/08/blogger-hacks-series.html
http://www.jackslocum.com/yui/2006/10/09/my-wordpress-comments-system-built-with-yahoo-ui-and-yahooext/

Oracle VARRAY Example

2006-10-10T15:44:00.008-05:00

(Revised 09/20/2010, I've revised this example to be more pertinent.)
VARRAYs provide the interesting ability to store multiple values in a single column. Note, "interesting" doesn't mean I'm necessarily encouraging its use. I expect there are some potentially significant performance penalties using VARRAYs.

set serveroutput on;

column id FORMAT A5;
column url FORMAT A20;
column Tag(s) FORMAT A20;

clear;

-- Create a VARRAY that can hold 10 "objects" [cells] of type VARCHAR2 (i.e. 10 Tags of up to 50 characters)
create or replace type vcarray_tags as VARRAY(10) OF VARCHAR2(50);
/

-- Each URL is stored in a single row regardless of the number of tags
create table site_tags (id number, url varchar2(100), tag_list vcarray_tags);

insert into site_tags values (1, 'www.bing.com', vcarray_tags('search', 'microsoft'));
insert into site_tags values (2, 'www.google.com', vcarray_tags('search', 'google'));
commit;

set echo on;

-- Note, The VARRAY column is of type "object"
select 
   *
from
   site_tags;

-- Display each tag on a separate row 
select 
   site_tags.id, 
   site_tags.url,
   tags.column_value "Tag(s)"
from
   site_tags,
   table(site_tags.tag_list) tags;

-- Show all the URLs "tagged" with 'search'
select 
   site_tags.id, 
   site_tags.url,
   tags.column_value "Tag(s)"
from
   site_tags,
   table(site_tags.tag_list) tags
where
   tags.column_value = 'search';
/
-- PL/SQL Example 
begin
   for c in (select * from site_tags) loop
      dbms_output.put_line(c.id||' : '||c.url);
      for i in c.tag_list.first .. c.tag_list.last loop
         dbms_output.put_line('      Tag: '||c.tag_list(i));
      end loop;
   end loop;
end;

Results:

create or replace type vcarray_tags as VARRAY(10) OF VARCHAR2(50);
/
Type created
create table site_tags (id number, url varchar2(100), tag_list vcarray_tags);
 
Table created
insert into site_tags values (1, 'www.bing.com', vcarray_tags('search', 'microsoft'));
 
1 row inserted
insert into site_tags values (2, 'www.google.com', vcarray_tags('search', 'google'));
 
1 row inserted
commit;
 
Commit complete
set echo on;

select
 *
from
 site_tags;
 
   ID URL                  TAG_LIST
----- -------------------- --------
    1 www.bing.com         <Object>
    2 www.google.com       <Object>

select
 site_tags.id,
 site_tags.url,
 tags.column_value "Tag(s)"
from
 site_tags,
 table(site_tags.tag_list) tags;
 
   ID URL                  Tag(s)
----- -------------------- --------------------
    1 www.bing.com         search
    1 www.bing.com         microsoft
    2 www.google.com       search
    2 www.google.com       google

select
 site_tags.id,
 site_tags.url,
 tags.column_value "Tag(s)"
from
 site_tags,
 table(site_tags.tag_list) tags
where
 tags.column_value = 'search';
 
   ID URL                  Tag(s)
----- -------------------- --------------------
    1 www.bing.com         search
    2 www.google.com       search

begin
 for c in (select * from site_tags) loop
  dbms_output.put_line(c.id||' : '||c.url);
  for i in c.tag_list.first .. c.tag_list.last loop
   dbms_output.put_line('      Tag: '||c.tag_list(i));
  end loop;
 end loop;
end;
/
 
1 : www.bing.com
      Tag: search
      Tag: microsoft
2 : www.google.com
      Tag: search
      Tag: google

How to compile PL/SQL with a reference to DBA_ROLE_PRIVS when you don't have rights to it...

2006-10-03T12:03:00.002-05:00

I have some PL/SQL [Function userHasRole(...) Return Boolean] which references DBA_ROLE_PRIVS. This package function normally is compiled under a schema which does have rights.

Issue: What do you do if you, the lowly Developer, DOES NOT have rights to the [generally DBA-only] DBA_ROLE_PRIVS view?

Fix: Under your user, create a view named DBA_ROLE_PRIVS that references USER_ROLE_PRIVS instead...

CREATE OR REPLACE VIEW dba_role_privs AS
SELECT
  GRANTED_ROLE,
  USER "GRANTEE"
FROM
  USER_ROLE_PRIVS

Simple Performance Tuning for Oracle PL/SQL using SYSTIMESTAMP

2006-10-02T11:26:00.000-05:00

Starting in Oracle 9.x, there is a more precise version of SYSDATE that goes to the millisecond. Previously, the only way to get to milliseconds was to use dbms_utility.get_time (which didn't really return a real time but a counter position within a time loop that recycled itself).

Code :

declare

   ltimestamp_start  timestamp;
   ltimestamp_stop   timestamp;
   linterval_diff    interval day to second;

   ldt_temp     date;

begin

   ltimestamp_start := systimestamp;
   dbms_output.put_line(ltimestamp_start);

   -- Routine to performance test...
   for i in 1 .. 10000 loop
      select sysdate into ldt_temp from dual;
   end loop;

   ltimestamp_stop := systimestamp;
   dbms_output.put_line(ltimestamp_stop);

   linterval_diff := ltimestamp_stop - ltimestamp_start;

   dbms_output.put_line(CHR(10)||LPAD('=',22,'=')||CHR(10)||'  Runtime Difference'||CHR(10)||LPAD('=',22,'='));
   dbms_output.put_line(
   '     Days : '||EXTRACT(DAY FROM linterval_diff)||CHR(10)||
   '    Hours : '||EXTRACT(HOUR FROM linterval_diff)||CHR(10)||
   '  Minutes : '||EXTRACT(MINUTE FROM linterval_diff)||CHR(10)||
   '  Seconds : '||EXTRACT(SECOND FROM linterval_diff) );
end;

Results:

02-OCT-06 11.17.00.863610 AM
02-OCT-06 11.17.01.878845 AM

======================
Runtime Difference
======================
   Days : 0
  Hours : 0
Minutes : 0
Seconds : 1.015235

Create a logo image with a transparent background

2006-09-26T10:57:00.001-05:00

Image :

Use GIMP or Paint.NET to create a GIF image (I can't get it to correctly work with any other image format).
Create a new GIF image of the desired width and height
Make the background layer... NOT visible
Insert a new layer

CSS (Style Sheet) :

I had to manually set the width and height
For some reason, the cursor wasn't changing to URL "pointer", so I forced it

#company_logo {
 background-image: url(images/company_logo1.gif);
 background-repeat: no-repeat;
 width: 189pt;
 height: 76pt;
 cursor: pointer;
}

Html :

<a href="http://www.mycompany.com"><div id="#company_logo"></div></a>

Oracle SELECT with an array using an IN

2006-09-21T10:57:00.005-05:00

declare

   -- CREATE OR REPLACE TYPE PROD_TYPES.TYPE_NUMBER_ARRAY AS TABLE OF NUMBER
  nt_src      prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY();    -- Constructor     
  nt_dest     prod_types.type_number_array := PROD_TYPES.TYPE_NUMBER_ARRAY();    -- Constructor     

begin

  -- Test data (no order)
  select
    product_version_id
  bulk collect
    into
      nt_src
  from product_version v
  where v.product_version_id between 138120 and 140000;

  -- select * from table(sys.dbms_debug_vc2coll(1,2,'a')) order by 1 desc
 
  -- Data ordered
  select
    product_version_id
  bulk collect
    into 
      nt_dest
  from
    product_version
  where
    product_version.product_version_id in (select column_value from table(cast(nt_src AS prod_types.type_number_array)))
  order by
    product_version.expiration_date2 desc,
    product_version.base_price desc,
    product_version.expiration_date2 desc;

  for i in nt_dest.first .. nt_dest.last loop
    dbms_output.put_line(to_char(nt_dest(i)));
  end loop;

  exception
    when others then
      dbms_output.put_line('sqlerrm : '||sqlerrm);
end;

Determine IP address of a VM Ware Player sessions running a Linux distribution

2006-09-20T10:55:00.000-05:00

Recently, I started playing with VMWare Player (http://www.vmware.com/products/player/). I do some work in Unix/Linux, but not a lot. I've always been able to do what I needed, however I couldn't remember what it took to determine what the IP address of machine was. The documentation said : "http://your.vm.ip.address/". Damn, how?

Hence this post,

Command line: ifconfig eth0

ActiveSync Issues

2006-09-12T10:57:00.000-05:00

Well, I ran into some ActiveSync issues last night.

Basically, I was working with MicroTorrent, and wanted to see what my Windows Firewall settings were. I had the firewall off, but when Windows realized that I "looked" at the settings it decided to turn on the Firewall. Basically, after turning the Firewall off and several reboots later I finally started syncing again. What's weird is that the Firewall has the entries to allow ActiveSync.ErrorCode : 80070008

http://www.mtekk.com.au/Forums/tabid/56/forumid/17/postid/48412/view/topic/tpage/3/Default.aspx

If you are using a software firewall on your PC, you need to allow ActiveSync to use TCP/IP. ActiveSync uses the programs WCESCOMM.EXE, WCESMGR.EXE, RAPIMGR.EXE, and CEAPPMGR.EXE.

TCP/IP ports:

990 (RAPI)
999 (Status)
5678 (Legacy Replication)
5679 (Legacy Replication)
5721 (Desktop Passthrough)
26675 (AirSync)

The WM5 ‘Device’ is the DHCP server, thus the device has the IP address of 169.254.2.1 (Default) and the PC obtains the IP address of 169.254.2.2 (Default) [thanks to Brian for the update].

WindowsMobile Links to Review

2006-09-11T10:56:00.000-05:00

http://www.pocketthinker.com/ptppc.html Outliner (WindowsMobile and Desktop)
http://www.softtrends.com/products/livepvr/livepvrwmsp.htm Personal Voice Recorder...
http://www.jetwaremobile.com/index.htm The JETware Hands-free Extension provides additional functionality to the built-in Windows Mobile support for the Bluetooth Hands-Free Profile.
http://www.softwareandson.com/SuperTasks/screenshots.php

WindowsMobile Links of Relevance

2006-09-10T10:56:00.000-05:00

Jason Langridge's WebLog - MR Mobile! (http://blogs.msdn.com/jasonlan/default.aspx)

T-Mobile Accessories

2006-09-09T10:55:00.000-05:00

Well, I can say that I am very disappointed that I can't find anyone that carries a silicon case for US version of the MDA. If anybody finds one, leave me a post / comment.

T-Mobile MDA Program Installation and CAB Files

2006-09-08T10:55:00.000-05:00

It took me a minute to understand how to install CAB files on the MDA. I found the answer here : http://www.microsoft.com/windowsmobile/articles/findapps.mspx#3.

Powered by Qumana

T-Mobile MDA Saga... Part 3 of aahhhh...

2006-09-07T10:55:00.000-05:00

Okay, as a software developer it pains me to have to give in and call tech support. ...but I did. And, well, I can happily say my MDA is working like a charm.

First, if you have just bought an MDA or are thinking about it... install the upgrade ROM [it wipes out all data... you've been warned!]. I haven't used it, but SpriteBackup claims to be able to move data between ROM upgrades http://www.spritesoftware.com/backup.php.

Summary: I couldn't sync, and the ROM upgrade fixed all my problems. URL : http://www.t-mobile.com/wmupgrade/

Detail : ActiveSync 4.1 would just sit and say "Connecting..." and then finally come back and say device not connected, but there were no errors. Somewhere in the guts of the ActiveSync network code, it was not working. Why they chose those model as the default rather than simple USB, I don't know. Anyway, I quickly got transfer up the support chain to someone that knew the magic buttons to press to force simple USB connectivity to install the ROM. Exactly, I need to patch, but I couldn't connect to patch, but I needed to patch to connect.

Hardcode Reset : If your MDA wont budge, try this: Enter Bootloader, - hold the "camera" (bottom right) and briefly press the reset button, don't release "camera" until you can see colour screen, connect USB cable, see "USB" on the bottom of the screen, and start to re-flash the ROM.

Figuring out what a machine is on your network...

2006-09-06T10:54:00.000-05:00

I use ZoneAlarm (free verson), and its log shows some entries of a machine talking to mine. So what's the best way to figure out what that other machine is?

nbtstat can at least get me the DHCP "name" nbtstat -A <ip_address>

Other suggestions?

Oracle ORA-03117: two-task save area overflow

2006-09-05T10:53:00.026-05:00

Feel free to skip to the bottom and read the "Update"...

Recently, I made some changes in Development to an Oracle Package. The package compiled cleanly and all the unit tests passed successfully.

However upon testing in one of our client applications, we started getting an "ORA-03117: two-task save area overflow" error. It is similar to an ORA-00600 error, useless to us, and usually results in Oracle saying "upgrade to x" [Oracle 10g, in this case].

What is particularly distressing about this particular case is that the Powerbuilder code errors at the package level. The changes were not even being referenced directly by PowerBuilder application.

Update I've discovered that I could recreate this error solely within SQL-Plus by "desc <complex packagename>". If the "complex" package has a reasonable number of nested records/types, you can get this error.

DESC DBMS_METADATA;

We were using either Oracle 8 or Oracle 9 SQL-Net clients for the communication layer. If you are using the 10.2.x SQL-Net client, you don't encounter an error. Upgrading to a newer version of SQL-Net fixes this problem. It is NOT an application code issue.

Oracle and getting Milliseconds from Sysdate... [Systimestamp]

2006-09-04T10:53:00.004-05:00

First, you can't.

Sysdate doesn't support going to milliseconds. However, starting with Oracle 9, you can getting milliseconds from another system source : SYSTIMESTAMP.

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

T-Mobile MDA Saga... Part 2 of #*$%!

2006-09-03T10:54:00.000-05:00

Well, I'm officially annoyed. I can't get ActiveSync 4.2 to work. And, now I've discovered that there is a ROM upgrade.

With hindsight,

Set a system restore point in Windows XP.
Use the ActiveSync that comes with your phone... 4.1 in my case. There are way too many news postings about not upgrading because of issues with the latest and greatest..
Install the latest ROM for the phone. This wipes out the phone, so you don't want to do this later.

T-Mobile MDA

2006-09-02T10:54:00.000-05:00

Well, I just switched to T-Mobile from Cingular. I've been a Cingular customer for years, but their silly (nicest word I can use right now) policy of treating existing customers with expired contracts as worthless is too much. The phone I wanted was a Cingular 8125, but T-Mobile has the same phone as the MDA. If I had upgraded with Cingular it would have cost me about $200 bucks [after rebates, and being force to buy a $20 more expensive plan] or ~$0 [after rebates] for switching (no hassle and mild plan change for $5 with more minutes).

See my "t-mobile mda" labels (categories)

Test

2006-09-01T13:59:00.000-05:00

I've moved my Blog from WordPress because I wanted to have my own style sheet (CSS) without having to pay for it. Wordpress has been very nice otherwise.