Home » Developer & Programmer » Forms » Function doesn't insert
| Function doesn't insert [message #601109] |
Fri, 15 November 2013 16:13  |
 |
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 #601113 is a reply to message #601112] |
Sat, 16 November 2013 02:19  |
John Watson
Messages: 8963
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 |
|
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 |
 |
Littlefoot
Messages: 21823
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 #601122 is a reply to message #601121] |
Sat, 16 November 2013 12:04 |
|
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 #601142 is a reply to message #601132] |
Sun, 17 November 2013 03:53 |
 |
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 #601151 is a reply to message #601143] |
Sun, 17 November 2013 08:37 |
|
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 #601155 is a reply to message #601152] |
Sun, 17 November 2013 11:08 |
|
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 |
|
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 |
|
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 #601190 is a reply to message #601187] |
Mon, 18 November 2013 05:40 |
|
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,,,,,,,?????
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jan 22 10:47:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
