SQL Cursor limitations? (Whittall, Conrad)

[fernando.olmos at hpa.com.au]

Thanks Conrad. The substring() was the trick. I don't have any Oracle
SQL documentation, other than what I found on the web, and I thought the
substr() function would have sufficed, but now that you mention PH uses
ANSI SQL, then that makes it easier.

The table name space size limitation is something I will refer to our
Oracle "tweakers". Unfortunately we do not have a DBA (!!!). We are
relying on Oracles' own external services to monitor db performance and
provide stats, advice, etc. Something which Oracle is very "thin" in
delivering, I might add.

------------------------------

Message: 5
Date: Wed, 17 Aug 2005 10:11:55 -0400
From: "Whittall, Conrad" <Conrad.Whittall at Cognos.COM>
Subject: RE: SQL Cursor limitations?
To: <powerh-l at lists.sowder.com>
Message-ID:
	
<20B567723B796747A3E19189A055BEB703FE6D9E at sottemail2.ent.ad.cognos.com>
	
Content-Type: text/plain;	charset="us-ascii"

The error message:

*E* ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

...is the big hint. Basically your cursor is selecting too much data for
your database schema's temporary tablespace to hold (which it needs to
do because your ORDERBY clause will force Oracle to sort the results).
You need to talk with your Oracle DBA and get the tablespace for the
TEMP schema extended...or if it is set to automatically extend, ensure
that there is sufficient disc space on the device(s) allocated to that
tablespace.

PowerHouse uses only ANSI-SQL, not any particular database vendor's
flavour. The ANSI SQL substring function is SUBSTRING, not SUBSTR. For
example,

SUBSTRING(x FROM 1 FOR 12)


Best regards,
Conrad
**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended solely for the use of the individual or entity to whom they are addressed.  If you received this e-mail in error, please notify the HPA Postmaster, postmaster at hpa.com.au, then delete  the e-mail.
This footnote also confirms that this e-mail message has been swept for the presence of computer viruses by MimeSweeper.  Before opening or using any attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more information about use, disclosure and access see our Privacy Policy at www.hpa.com.au
**********************************************************************