Smaller Footprint Oracle Client Software

Do you need to bundle Oracle Client Software with your application and want something leaner than the Oracle Instant Client? Do you need a quick and simple way to make a native Oracle 10 connection from your Windows PC?

Here’s an option that will provide Oracle 10 native connectivity (not ODBC) under Windows 32-bit using as few Oracle files as possible. Compressed size is 10 Megs compared to 17 Megs for the Oracle Client Basic Lite package.

Oracle10Client.zip

To install, you simply unzip the contents. The oracle key file contains a registry path that must correspond to the folder in which you have installed the client software. In the zip file, bin / oracle.key has

SOFTWARE\ORACLE\myOracle10Client

and the oracle10.reg file has

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\myOracle10Client]
“Oracle_Home”=”C:\\Program Files\\Oracle\\myOracle10Client”

You can use the oracle10.reg file to create the required registry entry.

You should update these files to point to the location in which you install the client software.

You can assemble this distribution yourself by paring down an Oracle 10 Client install to just:

BIN/
NETWORK/
RDBMS/
nls/
oracle10.reg
oracore/

./BIN:
ORASLAX10.DLL
TNSPING.EXE
oci.dll
oracle.key
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
oraocr10.dll
oraocrb10.dll
oraplp10.dll
orapls10.dll
orasnls10.dll
orasql10.dll
oraunls10.dll
orauts.dll
oravsn10.dll
oraxml10.dll

./NETWORK:
ADMIN/
mesg/

./NETWORK/ADMIN:
SAMPLE/
sqlnet.ora
tnsnames.ora

./NETWORK/ADMIN/SAMPLE:
SQLNET.ORA
TNSNAMES.ORA

./NETWORK/mesg:
tnsus.msb

./RDBMS:
mesg/

./RDBMS/mesg:
ocius.msb
oraus.msb

./nls:
data/

./nls/data:
lx00001.nlb
lx10001.nlb
lx1boot.nlb
lx20001.nlb
lx200b2.nlb
lx20367.nlb
lx207d0.nlb

./oracore:
zoneinfo/

./oracore/zoneinfo:
readme.txt
timezlrg.dat
timezone.dat

I should mention how this list was assembled. Initially, I used a very expensive product called AppSight (now BMC AppSight). Once I discovered by Mark Russinovich’s sysinternals, that worked just as well.

Published in: on April 23, 2008 at 6:27 pm  Comments (8)  
Tags: , ,

Configure TortoiseSVN for Auto-Logon Using Putty

Getting TortoiseSVN to stop prompting for a password can be a bit challenging. Here’s a step by step solution using Putty culled together from various sources on the internet.

TortoiseSVN Auto Logon

Published in: on April 15, 2008 at 2:31 pm  Comments (5)  
Tags: , , ,

Using a Custom Bitmap for a PowerBuilder Window Icon

Sybase’s PowerBuilder allows you to choose an icon for a window. However, the list includes only the .ico resources. Menus and toolbars, which can use the bitmap resources, have a much larger set of images in their drop-downs.

I don’t really care about the nuances that distinguish icon and bitmap resources. I just want the image choices to be consistent throughout the product.

The Problem

Here’s what can happen:

You find a custom image you really like for your dialog, and you assign it to the menu item, the toolbar item and perhaps a picture button or two. So you have your image appearing consistently throughout your application so the user is really dialed-in to what you want that image to convey. Then, you think, hey, let’s also put the image as the icon for my dialog (by icon, I mean the small image that appears in the window header, usually on the far left).

But, you discover that the choices for your windows are limited only to .ico resources. A very small listing in PowerBuilder.

The Solution

Here’s how you get the bitmap out of PowerBuilder so you can use it as an icon:

Using Resource Hacker, open the pbvm DLL. For PB 9.0, this is Program Files / Sybase / Shared / PowerBuilder / pbvm90.dll.

Select View / Expand Tree.

Now, scroll through the list looking for the image you’re after. Once you find it, right-click and choose Save [Bitmap : 25 :1033 ] (your numbers will be different).

Now, you simply convert that bitmap to an icon. Using IrfanView, open the image, and save it as an .ICO file with transparency. Select the color you want to be transparent (usually the gray color), and you’re all set!

Published in: on March 20, 2008 at 6:05 pm  Comments (4)  
Tags:

A List Function for Oracle

SQL Anywhere has a very convenient LIST function for combining rows of data into a single column.

Ex.

SELECT LIST( emp_id, ';' ORDER BY emp_lname ) AS "Sorted IDs"
FROM EMPLOYEE
GROUP BY dept_id

Sorted IDs
160;105;1250;247;266;249;445;…
1039;129;1142;195;667;1162;902;…
1336;879;586;390;757;148;1483;…
1751;591;1062;1191;992;888;318;…
1013;191;750;921;868;1658;703;…

Since I have to move between SQL Anywhere and Oracle quite often, I needed a way to duplicate, as much as possible, this function in Oracle.

Tom’s stringAgg routine is a great start. It is a good example of building a custom aggregate function in Oracle. However, it doesn’t lend itself to specifying the delimiter. Certainly you can use REPLACE, but if your strings contain commas, you’re out of luck.

Here is a solution, built on Tom’s StringAggType, that uses an object to pass in a delimiter. It’s a little clumsy to call, and doesn’t support DISTINCT, but it does work. Suggestions for improvement are welcome.

SQL> select deptid, list(listparm(lname, '|')) 
from emp 
group by deptid;

DEPTID       LIST(LISTPARM(LNAME,'|'))
----------   --------------------------------------------------------------------------------
10           Jones|Johnson|Hendricks
20           Sewell|Rogers|Yancy
30           Yost|Smith|Carter|Smith
40           Michaels

The Code:

drop type ListType
/

create or replace type ListParm as object
(
   theString varchar2(4000),
   is_Delimiter varchar2(100)
);
/

create or replace type ListType as object
(
   is_Delimiter varchar2(100),
   theString varchar2(4000),

   static function
        ODCIAggregateInitialize(sctx IN OUT ListType )
        return number,

   member function
        ODCIAggregateIterate(self IN OUT ListType ,
                             value IN ListParm )
        return number,

   member function
        ODCIAggregateTerminate(self IN ListType,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,

   member function
        ODCIAggregateMerge(self IN OUT ListType,
                           ctx2 IN ListType)
        return number
);
/

show errors

create or replace type body ListType
is

    static function ODCIAggregateInitialize(sctx IN OUT ListType)
    return number
    is
    begin
        sctx := ListType( ',', null );
        return ODCIConst.Success;
    end;

    member function ODCIAggregateIterate(self IN OUT ListType,
                                         value IN ListParm )
    return number
    is
    begin
        self.is_Delimiter := value.is_Delimiter;

        self.theString := self.theString || self.is_Delimiter || value.theString;
        return ODCIConst.Success;
    end;

    member function ODCIAggregateTerminate(self IN ListType,
                                           returnValue OUT varchar2,
                                           flags IN number)
    return number
    is
    begin
        returnValue := rtrim( ltrim( self.theString, self.is_Delimiter ), self.is_Delimiter );
        return ODCIConst.Success;
    end;

    member function ODCIAggregateMerge(self IN OUT ListType,
                                       ctx2 IN ListType)
    return number
    is
    begin
        self.theString := self.theString || self.is_Delimiter || ctx2.theString;
        return ODCIConst.Success;
    end;

end;
/

CREATE OR REPLACE FUNCTION List(input ListParm)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING ListType;
/

Published in: on January 17, 2008 at 5:22 pm  Comments (11)  

Displaying Currency with Adaptive Server/SQL Anywhere

SQL Anywhere, also known as Adaptive Server Anywhere, does not provide support to display values as currency.

So, I had to write my own function. Since I’m copying my SQL from Oracle, I want to easily replace the TO_CHAR( amount, ‘$9,999.99’) expressions. So, this function is named TO_MONEY() and has the same signature as TO_CHAR()’s. I just replace CHAR with MONEY in SQL Anywhere.

TO_MONEY( IN ad_value double, IN as_format varchar(50) DEFAULT ‘$9,999.99’ )

ALTER FUNCTION "dba"."to_money"( 
    IN ad_value double,
    IN as_format varchar(50) DEFAULT '$9,999.99' )
RETURNS varchar(50)
DETERMINISTIC
BEGIN
    DECLARE ls_value varchar(50);
    DECLARE ls_v varchar(50);
    DECLARE len integer;
    DECLARE ls_separator char(1);
	
    SET ls_separator = '.';
    
    SET ls_value = CAST( ROUND(ad_value*100,0) AS CHAR );
    SET len = LENGTH( ls_value );

    SET ls_v = RIGHT( ls_value, 2 );
    SET ls_value = SUBSTR( ls_value, 1, len - 2 );
    SET len = len - 2;
    
    WHILE len > 3 LOOP
        SET ls_v = RIGHT( ls_value, 3 ) + ls_separator + ls_v;
        SET ls_value = SUBSTR( ls_value, 1, len - 3 );
        SET len = len - 3;

        SET ls_separator = ',';
    END LOOP;

    IF len > 0 THEN
        SET ls_v = ls_value + ls_separator + ls_v;
    ELSEIF len = 0 THEN
        SET ls_v = '0' + ls_separator + ls_v;
    END IF;

	RETURN '$' + ls_v;
END