Monday, April 14, 2014

Active Directory - Getting a user's Contact Info from AD

Saving a snippet of code...

        [EnvironmentPermissionAttribute(SecurityAction.LinkDemand, Unrestricted = true)]
        private void accessAdEmpDetails()
        {
            string[] TempUserId = new string[2];

            // Username/Password with rights to query AD
            string activeDirUsername = ConfigurationManager.AppSettings["adUserName"];
            string activeDirPassword = ConfigurationManager.AppSettings["adPassword"];

            string[] activeDirServerNames = new string[] { ConfigurationManager.AppSettings["adLink1"], 
                                                       ConfigurationManager.AppSettings["adLink2"], 
                                                       ConfigurationManager.AppSettings["adLink3"], 
                                                       ConfigurationManager.AppSettings["adLink4"], 
                                                       ConfigurationManager.AppSettings["adLink5"] };

            WindowsIdentity WindowsIdentity = WindowsIdentity.GetCurrent();
            WindowsPrincipal WindowsPrincipal = new WindowsPrincipal(WindowsIdentity);

            char[] SeparatorSlash = { '\\' };

            TempUserId = Page.User.Identity.Name.Split(SeparatorSlash, 2);

            // Keeping user Id into the Session variable to furthur use.
            Session["USER_ID"] = TempUserId[1];

            if (WindowsIdentity.AuthenticationType.ToUpper().Equals("KERBEROS"))
            {
                // Loop through AD servers
                foreach (string serverName in activeDirServerNames)
                {
                    // Get a DirectoryEntry from each AD server
                    using (DirectoryEntry entry = new DirectoryEntry(serverName))
                    {
                        entry.Username = activeDirUsername; //User to access AD
                        entry.Password = activeDirPassword; //Pwd to access AD

                        using (DirectorySearcher searcher = new DirectorySearcher(entry))
                        {
                            searcher.Filter = "(&(objectClass=user)(samaccountname=" + TempUserId[1] + "))";

                            SearchResult Result = searcher.FindOne();

                            if (Result != null)
                            {
                                if (Result.GetDirectoryEntry().Properties["departmentNumber"].Value != null)
                                {
                                    userDept = Result.GetDirectoryEntry().Properties["departmentNumber"].Value.ToString();
                                }

                                if (!string.IsNullOrEmpty(Result.GetDirectoryEntry().Properties["name"].Value.ToString()))
                                {
                                    userName = Result.GetDirectoryEntry().Properties["name"].Value.ToString();
                                }

                                if (Result.GetDirectoryEntry().Properties["physicalDeliveryOfficeName"].Value != null)
                                {
                                    userLocation = Result.GetDirectoryEntry().Properties["physicalDeliveryOfficeName"].Value.ToString();
                                }

                                if (Result.GetDirectoryEntry().Properties["mail"].Value != null)
                                {
                                    userMail = Result.GetDirectoryEntry().Properties["mail"].Value.ToString();
                                }

                                if (Result.GetDirectoryEntry().Properties["telephoneNumber"].Value != null)
                                {
                                    userPhone = Result.GetDirectoryEntry().Properties["telephoneNumber"].Value.ToString();
                                }
                            }
                        }
                    }
                }
            }
        }

Friday, September 14, 2012

Verifying what fonts are on your RHEL6 system

Tried to run xlsfonts but got error Install package 'xorg-x11-xfs-utils' to provid....

Run yum install xorg-x11-xfs-utils to fix this issue.

Successfully ran xlsfonts | more

Now let's check for the Microsoft Web Fonts... xlsfonts | grep ^-microsoft.

Sigh, no luck.

Thursday, September 13, 2012

Installing the MS Core TrueType Fonts into RHEL6

Followed http://www.geeksite.in/how-tos/linux/centos-how-tos/how-to-install-microsoft-truetype-fonts-in-centos-6-rhel-6.html on how to install the Microsoft TrueType fonts into RHEL6.

Found the fonts in... /usr/share/fonts/msttcore/.

Second Attempt
Followed http://oimon.wordpress.com/2011/09/05/msttcorefonts-on-rhel6-centos-6-sl6/.

Wednesday, September 12, 2012

Welcome to Red Hat 6 (RHEL6)?!

Well, I am apparently back to the world of Linux after 20 years. Wow, that makes me feel old. I haven't used it since college, so needless to say I'm out of practice.

Going forward, I will be posting a number of noob discoveries as I work on re-learning Linux.

First one... how do I determine what version I'm running?

cat /etc/issue

Returns:
Red Hat Enterprise Linux Workstation release 6.3 (Santiago)

Another way...
cat /etc/*-release

Returns:
Linux Client for e-business (RHEL) 6.3 (Gold Master)
Open Client RHEL 64 3.30 (Gold Master)
Red Hat Enterprise Linux Workstation release 6.3 (Santiago)

Monday, January 09, 2012

Oracle - Change Password

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

Thursday, January 05, 2012

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

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

Monday, December 12, 2011

SameTime - Recovering your SameTime BuddyList

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

Wednesday, September 07, 2011

Oracle - Performance - Timing a Statement

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.

Thursday, September 01, 2011

Oracle - Sessions

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

sysdef -i
pending

Wednesday, August 31, 2011

Unix Notes - iostat

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

Wednesday, August 17, 2011

Compiling PL/SQL and Recompiling Dependent Objects

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;

Tuesday, August 16, 2011

Unix Notes - Server Performance Monitoring with TOP

/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

Tuesday, August 02, 2011

Unit Notes - vmstat (virtual memory statistics)

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.

Thursday, June 23, 2011

Unix Notes - Find a File

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

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

Thursday, June 09, 2011

Resetting / unexpiring an Oracle user

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

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

Tuesday, June 07, 2011

Oracle Replication - Disabling it a for a session

DBMS_REPUTIL.REPLICATION_OFF;

Wednesday, May 25, 2011

Oracle Explain Plan

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

Friday, May 13, 2011

Oracle - Tracking Progress without DBMS_OUTPUT buffering issues

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;

Thursday, April 28, 2011

Bad Characters to Use in Web-based Filenames

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

Thursday, September 02, 2010

PL/SQL Date Calculations

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

Friday, August 20, 2010

EnGenius ERB9250 Wireless Range Extender (Repeater)

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.

Friday, June 25, 2010

Execute Shell Commands from Oracle

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

Monday, June 21, 2010

Unix Notes - CRON

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

Monday, June 07, 2010

Unix Notes - Root and sudo access

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

Wednesday, June 02, 2010

Oracle SQL Query Formatting - Using the FORMAT command

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

Friday, May 21, 2010

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

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

Wednesday, May 05, 2010

Unix Notes - Change Password

-- Change Password
passwd <username>

Unix Notes - Disk Utilization

 
-- 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

Thursday, April 29, 2010

Unix Notes - Wide display for ps

/usr/ucb/ps auxw

Tuesday, March 16, 2010

Oracle: Determining if a Table is Replicated

Query:

Select Oname, Sname 
from dba_repobject 
where Oname IN ('DISPATCH_QUEUE','CONTRACT');
Results:
ONAMESNAME
CONTRACTPROD
The CONTRACT table is replicated, but the DISPATCH_QUEUE table is not.

Tuesday, April 14, 2009

PL/SQL Cursor For Loop with Bulk Collect and Limit

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;

Monday, March 30, 2009

Count occurences of a character in a string (Oracle)

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;

Wednesday, January 21, 2009

Syntax Highlighting Posted Code Snippets

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

Tuesday, January 20, 2009

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

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" );
%>

Tuesday, December 02, 2008

Outlook - Red "X" on Image Paste

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\

Tuesday, November 25, 2008

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

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

Wednesday, November 05, 2008

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

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

Tuesday, October 07, 2008

Print.css

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

Tuesday, August 12, 2008

Getting the current Oracle version (via a SQL Select query)

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
PRODUCTVERSIONSTATUS
NLSRTL11.1.0.6.0Production
Oracle Database 11g Enterprise Edition11.1.0.6.064bit Production
PL/SQL11.1.0.6.0Production
TNS for Solaris:11.1.0.6.0Production

Thursday, August 07, 2008

gzip Http Compression in IIS

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

Wednesday, April 16, 2008

Prototyping in ASP.Net - asp:SqlDataSource Example

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

Thursday, April 10, 2008

Feuerstein's article on BULK COLLECT and %NOTFOUND

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;

Thursday, March 20, 2008

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

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

Tuesday, January 15, 2008

Upsert / Merge within the Same Table (Oracle)

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

Wednesday, October 17, 2007

Suzuki Katana - Tire Pressure

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

Friday, October 12, 2007

Oracle: InstantClient, TNSPING, and PL/SQL Developer

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:

  1. I don't want to tell you how long it took me to figure this all out, but it was way too long.
  2. It's an unacceptable solution from a long-term maintenance, support standpoint.
Steps:
  1. Download the appropriate InstantClient "base" (in other words, English-only or International).
  2. Download the InstantClient SQL-Plus "add-on".
  3. Extract both Zip files into the same directory [for this example, "c:\oracle\instantclient"].
  4. At this point, we haven't done anything special.
  5. Define an environment variable oracle_home that points to the location of your InstantClient directory (e.g.: "c:\oracle\instantclient").
  6. Create a subdirectory called "network" (c:\oracle\instantclient\network).
  7. In "network", create a subdirectory called "admin".
  8. 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"].
  9. Define an environment variable tns_admin that points to the location of your tnsnames.ora (if you are using one).
  10. In "network", create a subdirectory called "mesg".
  11. 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.
  12. At this point, you should be able to fire up PL/SQL Dev and still have things work.
  13. Okay, now the weird part starts.
  14. Create a subdirectory called "bin" (c:\oracle\instantclient\bin), and yes, it must be called bin.
  15. 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
  16. 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

Thursday, August 09, 2007

Google Search

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.

Monday, February 19, 2007

Rails : Maintaining an Oracle connection (preventing a timeout)

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

Friday, February 02, 2007

Ruby : WebService - GetQuote

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

Wednesday, January 31, 2007

Ruby - Code Snippet - Methods List

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"

Wednesday, December 13, 2006

Ruby - Default Values for Method Arguments

>> 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

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

Wednesday, December 06, 2006

Rails : View - Partials

Partials are awesome...

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

Rails - Automatic Reloading and Models

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:

Ruby: View - Inserting Text

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

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

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

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

Tuesday, December 05, 2006

Rails - Value Checking

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?

Monday, November 27, 2006

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

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

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

Tuesday, November 21, 2006

Ruby: Invoking a PL/SQL Package with Array args

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

Related Posts :

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 :

Monday, November 13, 2006

Rails: Hello World / HelloWorld

Note, I was developing on Windows at the time of this post.

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.
  1. rails helloworld
  2. cd helloworld
  3. ruby script\generate controller helloworld
  4. 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>
    
  5. ruby script\server
  6. Navigate to http://localhost:3000/helloworld in your browser and be greeted with your friendly application: "Hello World"

Tuesday, November 07, 2006

PHP: Autofilled Forms

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).

Friday, November 03, 2006

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

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)

Thursday, October 26, 2006

Tweaking Firefox via userChrome.css

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

*/