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

From: <kgrigg_at_acxiom.com>
Date: 1997/12/08
Message-ID: <881615841.1649213448_at_dejanews.com>#1/1

Well, I'm still stuck on one of my first PL/SQL scripts...

I posted before that it choked on my CREATE Table statement, so, I've removed it and just went ahead and created it on my own. Here is what it looks like:

[Quoted] 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),
[Quoted] 	cutoff_date		date,
	directory_number	varchar2 (6),
	publication_date	date,
	room_code		varchar2 (1),
	room_number		varchar2 (8))

tablespace temporary_data;

[Quoted] 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 [Quoted] 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? I'm using Oracle8 and am testing this with Enterprise Manager's SQL notepad. [Quoted] Listed below is the section in my main block that bombs and the error message that follows 2> begin ... ...

[Quoted]   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

If anyone can help me I'd greatly appreciate it. I'm feeling extremely newbie-ish and can't seem to find what I'm doing wrong in any of the literature I have on hand...

TIA!! Kelly
kgrigg_at_acxiom.com

ps. Please cc by mail too...our newsfeed is not reliable here....

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Mon Dec 08 1997 - 00:00:00 CET