SQL Cursor limitations?

[Whittall, Conrad]

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

-----Original Message-----
From: powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com
[mailto:powerh-l-bounces+conrad.whittall=cognos.com at lists.sowder.com] On
Behalf Of fernando.olmos at hpa.com.au
Sent: Tuesday 16 August 2005 23:34
To: powerh-l at lists.sowder.com
Subject: SQL Cursor limitations?

Hi guys,

I am still working on this performance task using SQL cursors.
Can someone tell me why I am getting this error?

*E* Data access error. (CRS_INVOICED_MSTDET)
*E* DMS-E-GENERAL, A general exception has occurred during operation
'asynchronous open'. (CRS_INVOICED_MSTDET)
*E* ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Here is the cursor ...

sql in hisdb declare crs_invoiced_mstdet        &
cursor for                              &
        select  im.order_no,            &
                im.account_number,      &
                im.order_name,          &
                im.comment_1,           &
                im.comment_2,           &
                im.comment_3,           &
                im.credit_amount_1,     &
                im.credit_area_1,       &
                im.credit_note_flag,    &
                id.base_cost,           &
                id.care_number,         &
                id.charge_indicator,    &
                id.credit_note_no,      &
                id.gl_account_no,       &
                id.invoice_number,      &
                id.invoice_qty,         &
                id.invoiced_date,       &
                id.minimum_cost,        &
                id.per_unit,            &
                id.sales_tax_amt,       &
                id.sell_price           &
        from    invoiced_master     im, &
                invoiced_detail     id  &
        where   im.credit_note_flag          =  'Y'             and &
                id.gl_account_no             <> 'D2480'             &
;                (substr(im.comment_1, 1, 10) = 'HPA Errors'     or  &
;                 substr(im.comment_1, 1, 12) = 'Process/Prod'   or  &
;                 substr(im.comment_1, 1, 13) = 'Service Level') &
        order by im.order_no


Also, if you know of a way to use the substr() function, that would be
great.

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

--
= = = = = = = = = = = = = = = = = = = = = = = = = = = = Mailing list:
powerh-l at lists.sowder.com
Subscribe: "subscribe" in message body to
powerh-l-request at lists.sowder.com
Unsubscribe: "unsubscribe &lt;password&gt;" in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber. 
  
       This message may contain privileged and/or confidential information.  If you have received this e-mail in error or are not the intended recipient, you may not use, copy, disseminate or distribute it; do not open any attachments, delete it immediately from your system and notify the sender promptly by e-mail that you have done so.  Thank you.