OraFAQ Forum: Reports & Discoverer » new report for oracle application : insert data from table A to Table B

Home » Developer & Programmer » Reports & Discoverer » new report for oracle application : insert data from table A to Table B

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

new report for oracle application : insert data from table A to Table B [message #202251]

Wed, 08 November 2006 20:23

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

Report message to a moderator

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

Littlefoot
Messages: 21813
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;

Report message to a moderator

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

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?

Report message to a moderator

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

Littlefoot
Messages: 21813
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.

Report message to a moderator

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

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

Attachment: insert_in_table.RDF
(Size: 68.00KB, Downloaded 1358 times)

Report message to a moderator

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

javed.khan
Messages: 340
Registered: November 2006
Location: Banglore

Senior Member

Yeah,Zafer's RDF is working..!

Report message to a moderator

Previous Topic:	Copy selected values from a viewer report
Next Topic:	Discoverer viewer & exporting to Excel

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 08:47:04 CDT 2024