Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 28783 invoked from network); 12 Dec 2007 13:12:57 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-69-64-49-119.inaddr.intergenia.de with SMTP; 12 Dec 2007 13:12:53 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BF3F77D8538; Wed, 12 Dec 2007 14:12:47 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 14366-01; Wed, 12 Dec 2007 14:12:47 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 37F597D854D; Wed, 12 Dec 2007 14:12:47 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 12 Dec 2007 13:25:22 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BA1267D7CBE for ; Wed, 12 Dec 2007 13:25:22 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06909-01 for ; Wed, 12 Dec 2007 13:25:22 -0500 (EST) Received: from mail34-dub-R.bigfish.com (mail-dub.bigfish.com [213.199.154.10]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4373A7D7BDA for ; Wed, 12 Dec 2007 13:25:21 -0500 (EST) Received: from mail34-dub (localhost.localdomain [127.0.0.1]) by mail34-dub-R.bigfish.com (Postfix) with ESMTP id 25735F88BE6 for ; Wed, 12 Dec 2007 18:25:10 +0000 (UTC) X-BigFish: VP X-MS-Exchange-Organization-Antispam-Report: OrigIP: 198.22.236.83;Service: EHS Received: by mail34-dub (MessageSwitch) id 1197483903426944_15261; Wed, 12 Dec 2007 18:25:03 +0000 (UCT) Received: from mailc.nysemail.state.ny.us (mailc.nysemail.state.ny.us [198.22.236.83]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by mail34-dub.bigfish.com (Postfix) with ESMTP id 70A2A11180B0; Wed, 12 Dec 2007 18:25:00 +0000 (UTC) Received: from EXCNYSM0A1AJ.nysemail.nyenet ([10.66.81.139]) by mailc.nysemail.state.ny.us with Microsoft SMTPSVC(6.0.3790.2499); Wed, 12 Dec 2007 13:23:46 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Pl/sql bulk collect problem Date: Wed, 12 Dec 2007 13:23:45 -0500 Message-ID: In-Reply-To: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Pl/sql bulk collect problem From: "Mercadante, Thomas F (LABOR)" To: , X-OriginalArrivalTime: 12 Dec 2007 18:23:46.0247 (UTC) FILETIME=[221F1570:01C83CEC] X-archive-position: 3885 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Thomas.Mercadante@labor.state.ny.us Precedence: normal Reply-to: Thomas.Mercadante@labor.state.ny.us List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Joe, Try declaring your PL/SQL tables as just plain numbers? TYPE lat_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; Tom -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Sweetser, Joe Sent: Wednesday, December 12, 2007 12:28 PM To: oracle-l@freelists.org Subject: Pl/sql bulk collect problem Disclaimer: I am not anything even close to a programmer. But I am playing around with FORALL and BULK COLLECT. I am trying to convert character values to numeric ones and then update a table with the numeric values. The character data can have up to 10 digits to the right of the decimal point so I am trying to convert that to a number and round it to 6 digits of accuracy. There are also some NULL values in those character fields. I have a table that has (among others) these columns defined: desc risksr ID_BUILDING NUMBER(10) LOCLATITUDE VARCHAR2(25) LOCLONGITUDE VARCHAR2(25) I have the following declarations in my pl/sql code: TYPE id_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE lat_type IS TABLE OF NUMBER(10,6) INDEX BY PLS_INTEGER; TYPE long_type IS TABLE OF NUMBER(10,6) INDEX BY PLS_INTEGER; b_id_data id_type; b_lat_data lat_type; b_long_data long_type; And my select statement is: SELECT id_building, NVL(ROUND(TO_NUMBER(loclatitude), 6), 99999), NVL(ROUND(TO_NUMBER(loclongitude), 6), 99999) BULK COLLECT INTO b_id_data, b_lat_data, b_long_data FROM risksr; When I try to run this, I get: DECLARE * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define ORA-06512: at line 102 102 is the line where my select statement is. I've been OTN'ing, Metalink'ing and google'ing with no luck on this error. Is it wrong to try and convert the data during a BULK COLLECT operation? Any other ideas/pointers/suggestions welcome as well. Thanks, -joe Confidentiality Note: This message contains information that may be confidential and/or privileged. If you are not the intended recipient, you should not use, copy, disclose, distribute or take any action based on this message. If you have received this message in error, please advise the sender immediately by reply email and delete this message. Although ICAT Managers, LLC scans e-mail and attachments for viruses, it does not guarantee that either are virus-free and accepts no liability for any damage sustained as a result of viruses. Thank you. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l