Re: help with simple stored procedure creation

From: Roman Mirzaitov <rmirzaitov_at_kt.kg>
Date: Tue, 9 Jul 2002 09:00:10 +0600
Message-ID: <agdjk9$krv44$1@ID-127142.news.dfncis.de>

Hi,

replace
temp := "";
with
temp:=''; (single quotes).

Besides, I recommend replace sustr() with substr() unless it's your own defined function.

Regards,

--
Roman Mirzaitov
Brainbench MVP for Oracle Administration
www.brainbench.com

"Kin Ng" <kin_ng5_at_yahoo.com> wrote in message
news:d5b3f600.0207081626.f69500e_at_posting.google.com...


> Can any body tell me why I can't create this sp?

>

> CREATE OR REPLACE PROCEDURE bulk_insert_attr_val_combo (

>   p varchar2

>   )

>

> AS

>

> combo varchar2(10) := '1,2,3,';

> temp varchar2(20) := '';

>

>

> BEGIN

>

> for i in 1..LENGTH(combo) loop

>   if substr(combo,i,1) = ',' then

>      insert into attr_val_combo values(temp);

> temp := "";

>   else

>      temp := temp || sustr(combo,i,1);

>   end if;

> end loop;

>

> COMMIT;

> END;

> /

>

> I got this error message from SQL plus

>

> CREATE OR REPLACE PROCEDURE bulk_insert_attr_val_combo (

> *

> ERROR at line 1:

> ORA-00604: error occurred at recursive SQL level 1

> ORA-01400: cannot insert NULL into ("SYS"."OBJ$"."NAME")

Received on Mon Jul 08 2002 - 22:00:10 CDT