Home » Developer & Programmer » Forms » Function doesn't insert
Function doesn't insert [message #601109] Fri, 15 November 2013 16:13 Go to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Hello, I create a table that has a primary key number,,,,
the details of this table are needed in another table, so, I created functions that copy the data I want from that table, it works well, and it shows me correct data,

but, when I go to oracle and write a select statement for that details, It doesn't insert,
-------------------------------
the functions as the following,
------------------------------------------------------------
FUNCTION Get_name(benef_id number) RETURN VARCHAR2 IS
re_val varchar2(250) ;
BEGIN
if benef_id is null
then return null ;
end if ;

select full_name into re_val
from ORACLE_WAQF where BENEFICIARY_NUMBER = benef_id ;
return re_val ;
exception when no_data_found then return null;

END;
-------------------------------------------------------------
I also have a displayed item that gets the details from a calculation equation,
it also doesn't insert
Re: Function doesn't insert [message #601111 is a reply to message #601109] Sat, 16 November 2013 01:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Please enclose your code within tags, as described here How to use [code] tags and make your code easier to read

I do not understand your question. You say "It doesn't insert". Of course it doesn't: there is no INSERT statement. There is a SELECT statement. Can you explain again?
Re: Function doesn't insert [message #601112 is a reply to message #601111] Sat, 16 November 2013 02:12 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
FUNCTION Get_name(benef_id number) RETURN VARCHAR2 IS
re_val varchar2(250) ;
BEGIN 
if benef_id is null 
then return null ; 
end if ;

select full_name into re_val
from ORACLE_WAQF where BENEFICIARY_NUMBER = benef_id ; 
return re_val ;
exception when no_data_found then return null; 

END;


this function is supposed to return a value which is full_name, I want the full name to be inserted to the other table,,,,,
Re: Function doesn't insert [message #601113 is a reply to message #601112] Sat, 16 November 2013 02:19 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
What's the problem? Your code works. I had to edit it to work with a standard table; next time, please include your CREATE TABLE statement.
orclz>
orclz> create or replace FUNCTION Get_name(benef_id number) RETURN VARCHAR2 IS
  2  re_val varchar2(250) ;
  3  BEGIN
  4  if benef_id is null
  5  then return null ;
  6  end if ;
  7
  8  select ename into re_val
  9  from emp where empno = benef_id ;
 10  return re_val ;
 11  exception when no_data_found then return null;
 12
 13  END;
 14  /

Function created.

orclz> select Get_name(7934) from dual;

GET_NAME(7934)
--------------------------------------------------------------------------------
MILLER

orclz>
Re: Function doesn't insert [message #601115 is a reply to message #601113] Sat, 16 November 2013 05:17 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
The functionality of it is correct. But, I want the return value to be inserted in my table.
---------------------------------------------------------------------------------------------------
ex:
I have a form, that has the following :

BENEFICIARY_NUMBER-----MATURITY_IN------NAME--------WHO_TAKES------NUMBER_FOLLOWERS----CH EQUE_VALUE--One_Cheque_val
------------------------------------------------------------------------------------------------------------------------------
I only have to enter the Beneficiary_number,Maturity_In, Cheque_recieved,Who_takes,Cheque_ser_number,and one_cheque_val Only,

Other Columns like : NAME is a return value from my function ,,,,, CHEQUE_VALUE is a formula ,,,,,
Those two has been changed to be calculation mode ++++ formula,
-----------------------------------------------------------------------------------------------------

BENEFICIARY_NUMBER-----MATURITY_IN------NAME------WHO_TAKES--------NUMBER_FOLLOWERS----CH EQUE_VALUE--One_Cheque_val
2-------------------------2013------"from function"----himself----"from function"------Formula--------100

well this is how it appears in the form,
I save, then
if I wrote a select statement in iSQL*pluse
select * from cheque_table; 

the field that gets its values from the function and the formula will appear Null,,,
How can I make the returned values to be inserted to my table

[Updated on: Sat, 16 November 2013 05:25]

Report message to a moderator

Re: Function doesn't insert [message #601118 is a reply to message #601115] Sat, 16 November 2013 11:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
NAME and CHEQUE_VALUE must be database item. Are they?

How do you populate their values? As of NAME, WHEN-VALIDATE-ITEM on BENEFICIARY_NUMBER seems to be a good choice.

As of CHEQUE_VALUE: it is a formula column, you say; if that's so, I'd suggest you NOT to store its value into a table because it can always be calculated. If you do store it, what will you do if one (or more) values that are involved into that formula change? Will you remember to recalculate it? Maybe you should consider use of a virtual column instead (if your database version supports them).

[Updated on: Sat, 16 November 2013 11:04]

Report message to a moderator

Re: Function doesn't insert [message #601119 is a reply to message #601118] Sat, 16 November 2013 11:43 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
I populate their values using function. Yes, Name and cheque_value are database items.
I'm using functions and column in order not to let the user modify the values because, We have thousands of Cheques. The function and the formula will restrict the user from changing what the owner wants.
So, again,
It doesn't store the values returned from the functions and the formula into my table
Re: Function doesn't insert [message #601120 is a reply to message #601119] Sat, 16 November 2013 11:44 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
if you want, I will upload the form, so you can have a look, thought, it's in Arabic.
Re: Function doesn't insert [message #601121 is a reply to message #601120] Sat, 16 November 2013 11:56 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I populate their values using function

OK, but you still didn't explain HOW you do that. Where do you involve that function? Do you see values returned by these functions on the screen when you are entering data into the form?
Re: Function doesn't insert [message #601122 is a reply to message #601121] Sat, 16 November 2013 12:04 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
First, I create the block in my form, involved every field I wanted.
I went to Field properties==> calculation ,,, I chose formula ,, the formula I wrote " get_name(:CHEQUE_TABLE.BENEFICIARY_NUMBER) "
the function get_name :
FUNCTION Get_name(benef_id number) RETURN VARCHAR2 IS
re_val varchar2(250) ;
BEGIN 
  if benef_id is null 
  	then return null ; 
  end if ;
  
  select full_name  into re_val
  from ORACLE_WAQF where BENEFICIARY_NUMBER = benef_id ; 
  return re_val ;
  exception when no_data_found then return null; 

END;


the Cheque Value ,,, calculation mode >> formula >> :cheque_table.one_cheque_val*:cheque_table.shars_no



in the running time, it shows me correct details. So yes, when I enter the beneficiary number, it automatically gives the name and the cheque value, though it doesn't insert into my table,
I checked that using the iSQL*Pluse query
select * from cheque_table 

it shows null values for those who are in calculations mode
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Re: Function doesn't insert [message #601129 is a reply to message #601122] Sat, 16 November 2013 13:28 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
please upload your tables structure and .FMB file as well it seems a small issue but problem is I think you r not explaining properly.

Regard
Mughal

[Updated on: Sat, 16 November 2013 13:31]

Report message to a moderator

Re: Function doesn't insert [message #601131 is a reply to message #601129] Sat, 16 November 2013 15:43 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Here you go, I uploaded all the tables required.
We are working on those tables,
oracle_waqf and cheque Table
here is the link :

http://www.mediafire.com/?bf7fa4g0vg2maj1

[Updated on: Sat, 16 November 2013 15:50]

Report message to a moderator

Re: Function doesn't insert [message #601132 is a reply to message #601131] Sat, 16 November 2013 15:53 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Again,
The data came from the functions and the formula are not saved in the fields.
Yes, it shows me correct data, but, I don't know why when I enquiry the data, I don't find them .......
Re: Function doesn't insert [message #601142 is a reply to message #601132] Sun, 17 November 2013 03:53 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
Dear tables.fmb is corrupted not able to open and CHEQUE.fmb is ok please upload tables.fmb here on this forum. due to arabic its difficult to understand but inshallah will do something
and here some report is running
run_product(REPORTS,'CHEQUE.REP',ASYNCHRONOUS,RUNTIME,FILESYSTEM,pl_id,NULL);


FRM-41211: Integration error: SSL failure running another product.

Cause: There is a problem detected when launching another product.

Action: Check the RUN_PRODUCT built-in.

anyway not neccessary

can you explain you put your formula on "CHEQUE_TABLE.NAME" variable which is return val. when i run this form in the name field arabic name is showing which is of course will be name of beneficiary and in your function you are taking return values is of course full_name i want to ask data is currently is showing in your name field it is wrong.

select full_name  into re_val
  from ORACLE_WAQF where BENEFICIARY_NUMBER = benef_id ; 
  return re_val ;


of course facing problem with arabic data but we are trying to solve you problem.PLZ CHECK .png

Regard
Mughal

[Updated on: Sun, 17 November 2013 04:52]

Report message to a moderator

Re: Function doesn't insert [message #601143 is a reply to message #601142] Sun, 17 November 2013 05:01 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
here check
Re: Function doesn't insert [message #601151 is a reply to message #601143] Sun, 17 November 2013 08:37 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
Dear problem is i think solved removed formula from NAME field property and
put on block-level-->trigger post-query
:cheque_table.name := get_name(:cheque_Table.beneficiary_number); 


and as we knew we haven't saved name field data yet B'COZ it is returning by function therefore press button for saving data or change according to your requirement.


Regard
Mughal



[Updated on: Sun, 17 November 2013 08:38]

Report message to a moderator

Re: Function doesn't insert [message #601152 is a reply to message #601151] Sun, 17 November 2013 08:50 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Thank you
Re: Function doesn't insert [message #601155 is a reply to message #601152] Sun, 17 November 2013 11:08 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Well, are you sure this is the correct way to do that, I mean that you repair something and another problem showed up.
Yes, it does insert to my query, but, if I created a new record, I will only enter the beneficiary number, other columns should be filled automatically.
What you have done insert, but, it doesn't shows the data when I create a new record. Why ?
Re: Function doesn't insert [message #601156 is a reply to message #601155] Sun, 17 November 2013 11:48 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
upload picture and mark that point when you will create record definately field will empty for enter new record. and in your .fmb i saw you did not define any trigger for that purpose you are telling that if you will enter beneficiary_number data should come automatically where you have defined these things i don't see anything like this from which table data should come you need some more work to do in this form the problem you highlighted i solved in a proper way, new prolem also i will solve just highlight/explain. you can do this very easily like you are saying as Enter Beneficiary_number others colum data will come automatically. i don't exactly know your whole scenario only problem i found which you have highlighted which i have solved.


[Updated on: Sun, 17 November 2013 12:22]

Report message to a moderator

Re: Function doesn't insert [message #601178 is a reply to message #601156] Mon, 18 November 2013 02:40 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
I am sorry, but why else do I need the functions for ??
If I couldn't use my functions to get the names and the other data automatically, then, the functions are useless.
I will explain again what I want, you already have the form and the database,,,,,
User will enter the following :
Beneficiary Number ,,,
then, the name and the number of followers will be shown automatically and saved,,,,

[Updated on: Mon, 18 November 2013 02:42]

Report message to a moderator

Re: Function doesn't insert [message #601184 is a reply to message #601178] Mon, 18 November 2013 03:07 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
please use your orignal .FMB Put beneficiary_number 3 or etc. and Show me the data mark and upload picture here on this forum. i will solve it.


Regard
Mughal
Re: Function doesn't insert [message #601187 is a reply to message #601184] Mon, 18 November 2013 04:57 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
Kindly Explain this.


Regard
Mughal
Re: Function doesn't insert [message #601190 is a reply to message #601187] Mon, 18 November 2013 05:40 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Ok,, Yes, you entered the beneficiary number,,,,
The field after it is the name, the name should be coming from other table, and here the function get_name is working, it gives you the name, and the data is correct, the next a yes or no list" not important, the next is who takes, not important , the next is a serial numebr also not important.
Then, comes, I should gives him the number of shares by each person , let's say it's 2
then, I have to give him the value per one share, let's say it's 100
Now, you have three displayed items, they are in the like in the following , respectively ,
The first one is a number of followers, which is a return value from a function called get_follower_number ,,,,, it should return a function ,,,
then,
The second is the total of shares which will be a function, it will multiply the shere per a person with the follower_number+1 ,,,,
The third is the total value of the cheque, which as well will be a multiply value with the cheuque_one value with the total number of shares<<<
the last field will give me the money in words,,,
...
is that what you are asking,,,,,,,?????
Re: Function doesn't insert [message #601207 is a reply to message #601190] Mon, 18 November 2013 11:00 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
then you no need for any function just run this form and tell me you can also control duplication. let me know any further query.

feel free for further query.

Regard
Mughal


Re: Function doesn't insert [message #601208 is a reply to message #601207] Mon, 18 November 2013 11:35 Go to previous messageGo to next message
mughals_king
Messages: 392
Registered: January 2012
Location: pakistan
Senior Member
Just Enter Beneficiary_number and press Enter name will comm automatically from oracle_waqf as i said in my above post you can also control duplicate Beneficiary_number to enter in cheque_table.

Re: Function doesn't insert [message #664482 is a reply to message #601208] Thu, 20 July 2017 01:49 Go to previous message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Thank You. Problem solved.
Previous Topic: oracle form (merged 2)
Next Topic: Messages
Goto Forum:
  


Current Time: Thu Mar 28 16:56:48 CDT 2024