RE: Pl/sql bulk collect problem
- From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
- To: "Sweetser, Joe" <JSweetser@xxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Dec 2007 10:31:30 -0700
Omitted data:
Oracle 9i on RH
The id_building data is NOT sequential and there ARE gaps in it.
-----Original Message-----
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)
<snip>
LOCLATITUDE VARCHAR2(25)
LOCLONGITUDE VARCHAR2(25)
<snip>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
