Home » Developer & Programmer » Reports & Discoverer » new report for oracle application : insert data from table A to Table B
new report for oracle application : insert data from table A to Table B [message #202251] Wed, 08 November 2006 20:23 Go to next message
voralux
Messages: 72
Registered: January 2006
Member

I would like to add new report for oracle application;
the report copy data from Table A to Table B

1. SQL Statement;

select (Field_A1, Field_A2, Field_A3)
from TABLE A
where Field_A3 = 1

2. Add : Function Formula CF_1
function CF_1Formula return char is
begin
insert into TABLE B
(Field_B1, Field_B2, Field_B3)
Values
:Field_A1
,:Field_A2
,:Field_A3);
end

If run report,the report show error
REP-1401 'cf_1formula'Fatal PL/SQL error occurred.
ORA-06503 pl/sql function returned without value.

Please help me
Re: new report for oracle application : insert data from table A to Table B [message #202294 is a reply to message #202251] Thu, 09 November 2006 00:37 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not talking about copying data, but the function: function MUST return a value. Yours doesn't have a RETURN statement (and has other mistakes). So, what happens if you change the function to
FUNCTION cf_1formula
   RETURN BOOLEAN
IS
BEGIN
   INSERT INTO b
               (field_b1, field_b2, field_b3
               )
        VALUES (:field_a1, :field_a2, :field_a3
               );

   RETURN (TRUE);
END;
Re: new report for oracle application : insert data from table A to Table B [message #202342 is a reply to message #202251] Thu, 09 November 2006 04:01 Go to previous messageGo to next message
voralux
Messages: 72
Registered: January 2006
Member

I changed function "CF_1"

function CF_1Formula return Number is
inv_id number;
begin
select count (*)
into inv_id
from TABLE A
where STATUS = '3';

If inv_id >0
then
return inv_id;

insert into TABLE B
(Field_B1, Field_B2, Field_B3)
Values
:Field_A1
,:Field_A2
,:Field_A3);
commit;

else
return '0';
end if;
end;

report return data but not insert data =====> Why?

Re: new report for oracle application : insert data from table A to Table B [message #202347 is a reply to message #202342] Thu, 09 November 2006 04:28 Go to previous messageGo to next message
Littlefoot
Messages: 20899
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no code which will do something just because you'd like it to. Did you anywhere call that function? If not, you shouldn't expect it to work.
Re: new report for oracle application : insert data from table A to Table B [message #206470 is a reply to message #202251] Thu, 30 November 2006 03:52 Go to previous messageGo to next message
zafarkarachi
Messages: 29
Registered: November 2006
Location: Karachi Pakistan
Junior Member
Hi Dear

I have attached a sample report regarding
data insert in a table might it help u

Zafar Iqbal

Re: new report for oracle application : insert data from table A to Table B [message #206484 is a reply to message #206470] Thu, 30 November 2006 04:55 Go to previous message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

Yeah,Zafer's RDF is working..!
Previous Topic: Copy selected values from a viewer report
Next Topic: Discoverer viewer & exporting to Excel
Goto Forum:
  


Current Time: Wed Dec 07 12:49:57 CST 2016

Total time taken to generate the page: 0.18719 seconds