Form Builder: INSERT INTO statement doesn't work

[SashaBuilder3]

Hi everybody,


Newbie questions. I created a stored procedure which reads data from a text file and populates a table with new records using INSERT INTO statement. The procedure works OK when called from within SQL*Plus environment.

Now I want to make a form called for the same job - loading the table. I created a data block, placed an item - push button, added to it a trigger WHEN-BUTTON-PRESSED and copied the procedure here. It compiles without problems and even starts working - it reads a first line from the file but stops on INSERT INTO statement giving an error message:

FRM-40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01830.

Can anyone tell me what is going on? By the way, where can I find a list of all those ORA-XXXXX exceptions?

One more thing. At some point in the procedure I use the Trim() function. But in Forms it gives me a compilation error (Identifier TRIM must be declared). Why doesn't the Forms recognize it?



Thanks,

Alexandre

 

[sem]

Trim function is relatively new to Oracle and not yet implemented in Forms (it uses older pl/sql engine). Try to use ltrim(rtrim()) instead.

 

[SashaBuilder3]

Sem,

Thanks for this point!

Alexandre

 

[ESSAMNAZIR]

Hi ..
I sorted out a similar problem, I was getting ORA-01722 when recompiling a form (it is running successfuly in my lap-top with oracle 8) in a new server with Oracle 8i .. I spend some hours in try-and-error ended up to: the select into statement has no error but the 'where clause' was having a sigmentation and concatination in the first variable like - where substr(idno,1,3)||firstname = xyz -
( just flip the statement ..xyz before the =)
hope it means something to u.
Thanx.

 

[zeelovesu]

i had the same problem.
i think u should use the select into clause if u want to insert data from the file into form.
then use the commit_form or simply commit to save the data from the form into table.
you can write
select field into :block_name.item_name from
table;
I hope this works.

 

[lewisp]

ORA-01830 is 'date format picture ends before converting entire string' or similar. You possibly have a problem with a TO_DATE or TO_CHAR function on a date, and you have different default date settings on your local PC than in the database.

Compare the NLS_DATE_FORMAT in your registry against the default date format in the database.