Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL: Now it won't let me insert...

Re: PL/SQL: Now it won't let me insert...

From: <kgrigg_at_acxiom.com>
Date: 1997/12/09
Message-ID: <881680956.17543@dejanews.com>#1/1

Ooops, sorry to everyone who has already replied, I made a mistake and posted the wrong create table statement. I really only have one date field named publication_date. Cutoff_date has been changed to varchar2 for testing purposes here. So, I am inserting x's into all fields except for publication_date where I am inserting a date. I still get the error message listed below. Thanks to all for all the replies so far, I hope you will take a second look and let me know what you might think. Once again, sorry for not proof reading my post a little better...I was pretty frustrated yesterday and in a hurry to get answers...

TIA!!
Kelly
kgrigg_at_acxiom.com

> I have an insert statement in my PL/SQL script which was complicated, so
> I just replaced all the variables with dummy values to try to figure out
> which one was screwing up...However, I keep getting the same strange
> message about an number conversion error. I don't know where this could
> be happening. I have all but one columns defined as varchar2 with one
> column as date. Can any one figure out what is going on here?

<snip>
> create table temp_out
> (no_address varchar2 (1),
> children_present varchar2 (1),
> no_solicitation varchar2 (1),
> primary_firstname varchar2 (14),
> primary_middle_initial varchar2 (1),
> primary_lastname varchar2 (26),
> street_number varchar2 (11),
> pre_directional varchar2 (2),
> street_name varchar2 (36),
> mail_to_city varchar2 (20),
> mail_to_state varchar2 (2),
> zip varchar2 (5),
> zip_4 varchar2 (4),
> carrier_route_code varchar2 (4),
> mail_to_county_code varchar2 (4),
> primary_prefix varchar2 (3),
> primary_professional varchar2 (3),
> primary_gender varchar2 (1),
> primary_generation varchar2 (2),
> secondary_firstname varchar2 (14),
> secondary_middle_initial varchar2 (1),
> secondary_prefix varchar2 (3),
> secondary_professional varchar2 (3),
> secondary_gender varchar2 (1),
> secondary_generation varchar2 (2),
> area_code varchar2 (3),
> phone varchar2 (7),
> record_use_indicator varchar2 (1),
> cutoff_date varchars (1),<--This is varchar2, not
> directory_number varchar2 (6), a date as in orig. post.
> publication_date date,
> room_code varchar2 (1),
> room_number varchar2 (8))
> tablespace temporary_data;
>

 <snip>
> Listed below is the section in my main block that bombs and the error
> message that follows

2> begin
....
....
>
> 164> if (c_individ_rec.individual_type = 'P') and (SQL%NOTFOUND) then
> 165> /* Do Insert of last record if primary */
> 166> insert into temp_out values ('x','x','x',
> 167> 'x','x','x','x',
> 168> 'x','x','x',
> 169> 'x','x',
> 170> 'x','x','x','x',
> 171> 'x','x','x',
> 172> 'x','x','x',
> 173> 'x','x','x',
> 174> 'x','x','x','x',
> 175> 'x','01-NOV-89',
> 176> 'x','x');
> 177> end if;
> 178>
> 179>
> 180> end loop;
> 181>
> 182> commit;
> 183>
> 184>
> 185>
> 186> end;
> 187>
> 188>
> ORA-01722: invalid number
> ORA-06512: at line 166
>
><snip>

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet
Received on Tue Dec 09 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US