I need to insert into DB a value, witch end user enter into combobox.

Hello,

My tables:
1. T_DAL
columns:
DAL_ID
DAL_PAV
AERO_ID

2. T_AERO
columns:
AERO_ID
AERO_PAV

In my forms block T_DAL I have a ListItem 'CMB_AERO' witch is a data base field (AERO_ID) and here I Populate data from other table T_AERO. I populate this list on when-new-form-instance trigger and populate it from record group
Record Group selekt = 'Select AERO_PAV, to_char(AERO_ID) from T_AERO';

When I inserting a new value into T_DAL, I also assign a value to CMB_AERO (select it from combobox)
But if user enter into this combobox a new data, I want to insert this to my T_AERO table, or open a new form (for enter data for new Aero).

In when-validate-item trigger I put such code

declare

LVal NUMBER;
LLab VARCHAR2(200);
listAero ITEM := find_item('CMB_AERO');

begin

Llab := :T_DAL.cmb_aero;      
select S_AERO.nextval into LVal from dual;
insert into Aviacija.T_AERO(aero_id, aero_pav) values (lval, llab);
add_list_element(listAero, 1, llab, LVal);

end;

I see a new value in a list, but not in DB.

How to insert these new values list, DB and that value user could assign at once to CMB_AERO?
Could you give some advices, comentars?

I would be very pleased

Solveiga


LVL 1
SolveAsked:
Who is Participating?
 
Helena MarkováConnect With a Mentor programmer-analystCommented:
It cannot be done such way because it is the same transaction.

You can display message to the user, cancel changes and use button for opening form for input a new value.
0
 
musduCommented:
Hi,

too see record inserted into database, you must commit your form; (issue a commit_form statement);

regards
0
 
SolveAuthor Commented:
But If I didn't entered other required fields yet? When I can't do a commit.

Also I got a messages: if I updating - cannot update record
if inserting - cannot insert record;

Solveiga

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Helena Markováprogrammer-analystCommented:
"Also I got a messages: if I updating - cannot update record if inserting - cannot insert record" - you have to insert/update all NOT NULL columns.
0
 
SolveAuthor Commented:
I cheked..

And with treigger you recomend to use.. and how to issue commit_form?

I get a message that I can't commit_form in when-validate-item tregger.
What would be your comentars about my code placed above?

Solve
0
 
SolveAuthor Commented:
When I do commit it raise a when-validate-item trigger again and when
its trying to insert into DB only AERO_ID(nextval of my sequence)

I looked an have in my table such data:

160
161 AeroName1

....

Solveiga
0
 
SolveAuthor Commented:
It seems it is working....

But how to call commit_form programaticaly?
And mabe someone could write me a code, witch will check is there in data base a record, witch name is that new entered value to combobox?

Solveiga
 
0
 
Helena Markováprogrammer-analystCommented:
Commit_Form is a  restricted procedure so you can use it in a triggers where restricted procedures can be used. If you read the description of some trigger you can find there section Legal Commands.

For checking if there is a value in a table you can use this fast select-
it returns 0 if there is no such value(record) or 1 if there is:
SELECT count(*) INTO n_pom FROM dual WHERE EXISTS
   (select null from your_table where column=new_value);
0
 
SolveAuthor Commented:
Thank You, Helena.

And on witch event I should do that insert into table (when-validate-item, when-new-item-instanse or some other)
Because first of all i need to be sure that I want to insert, I need to chek a values I need to insert and so after that I need that value from combobox to be inserted?
Have you more patience to help me with this problem?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
I think that it is not a good practice to insert to both tables in one block. You should create another block for T_AERO. That will solve the problem.
0
 
SolveAuthor Commented:
Ok, going to try creat anothet block

Solveiga
0
 
SolveAuthor Commented:
It seems I wonn't implement my plans....
I desided only to check a value if user enter something new in combobox...

When do I have to check? On witch tregger. I will use that code witch henka wrote for me.

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
I desided only to check a value if user enter something new in combobox... - so you can use When-Validate-Item trigger for it.
0
 
SolveAuthor Commented:
In when-validate-item trigger I pust this code:

declare
      LVal NUMBER;
      LLab VARCHAR2(200);
      listAero ITEM := find_item('CMB_AERO');
yra number;                                      
statAero NUMBER;

begin

Llab := :T_DAL.cmb_aero;

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_PAV = Llab);

If yra = 0 then
n := show_alert('al_new_aero');
if n = alert_button1 then
      select Aviacija.s_aero.nextval into LVal from dual;
      insert into T_AERO(aero_id, aero_pav) values (Lval, Llab);
      :T_DAL.CMB_AERO := lval;
      statAero := Populate_Group('RG_AERO');
      Populate_List(listAero,'RG_AERO');
end if;

end if;
end;

But I always get that yra = 0, even if I select a value from combobox, what means that record exactly exists in DB.
What can it be wrong?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
Did you try this in SQL*Plus ? (put select null from Aviacija.T_AERO ... instead of select * from Aviacija.T_AERO ...):

select count(*) from dual where exists (select null from Aviacija.T_AERO where AERO_PAV = Llab);
0
 
SolveAuthor Commented:
Sorry, Helena, it was my mistake
Now it works. When it is not a new record LLab = AERO_ID, when I need to convert it to number
but if it is a new (user's entered value) LLab = text...
So if there any possibilities to check if it is a new record?
Mabe here is is why my trigger works not corectly?

Solveiga
0
 
SolveAuthor Commented:
I need to solve this from here:

LLab := :CMB_AERO;

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_ID = to_number(Llab));

When I select a value from combobox LLab = (e.g.) 42 this is a record's ID
But if I enter something new and want to check is such record in DB I have when LLab = (text I entered) 'My new aerodrom'
It is VARCHAR and when It would be klever to use such code

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_NAME = LLab)...

How to catch if it is an old record selected or new value entered?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
If it is for block - there is GET_RECORD_PROPERTY built-in there. The status of a new record (which is not in a table in a database) is NEW or INSERT.
0
 
Helena Markováprogrammer-analystCommented:
But if user enter a new value in a combo box then there is no returning value there (record group 'RG_AERO') so you know it is a new value.
0
 
SolveAuthor Commented:
I tried this, but in both cases block status is 'CHANGED' because I change a combobox.


Would it be normal solution if I handle exception INVALID_NUMBER ?
Because whene do :

select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_ID = to_number(Llab));
Trigger raise an unhandeled exception ORA-01722, because cannot convert LLab to number.

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
I think that you can handle an exception.
0
 
SolveAuthor Commented:
How to do undo when I want a value from combobox to be deleted?
Clear_item doesn work in when-validate-item :(

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
But there is another option you can use. If you want user to input only numbers then use Format Mask.
"Display item format mask symbols for CHAR values" - e.g. FM999
0
 
Helena Markováprogrammer-analystCommented:
You can set the item to NULL.
:your_item:=NULL;
0
 
SolveAuthor Commented:
nope...my plans to handle such excpetion was not successfuly...

What to do?
 Mabe I can when somehow to chek is LLab is from numbers or from characters??

Solveiga
0
 
SolveAuthor Commented:
I need to solve this priblem again

1. LLab := :CMB_AERO;

In case it was selected from combobox's values LLab = (e.g.) 42 and it's value is record's ID.

In case it was entered by user - Llab = TextEnteredByUser

In both cases LLab is characters, but in 1 case I can convert it to number and when have an ID.
...
 
1 case I need
select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_ID = to_number(Llab));
--and when to do something

2 caes I need
select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_NAME = LLab)...

How to catch if it is an old record selected or new value entered?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
Why don't you use Format Mask ? e.g. FM999

or you can use this test:

SQL> select decode(translate('A123456','1234567890','Y'),'Y','Y','N') from dual;

DECODE(TRANSLATE('A123456','12
------------------------------
N

SQL> select decode(translate('123456','1234567890','Y'),'Y','Y','N') from dual;

DECODE(TRANSLATE('123456','123
------------------------------
Y
0
 
SolveAuthor Commented:
I have never know about this :(
Now I am going to train

Solveiga
0
 
SolveAuthor Commented:
Could you give an example to my case with format mask?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
If the lenght of your item is 4 then the format mask is:
FM9999

All necessary informations you can find in the on-line help - "Format Mask property" topic.
0
 
SolveAuthor Commented:
I am afraid, that this is not what I need...
It has to be a way to designate is it value selectet from list or entered by user, isn't it?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
If you want to know if the value exists in a list then you can find it in a record group which is used for populating the list.
0
 
SolveAuthor Commented:
Good morning :)

Actualy I populate list from table so would it be the same if I find it in table?
But I am not 100% sure, that I can lookup for a record when I don't know what is my combobox value.
One time it is numbers, second time it is characters:(

So when I do fast select and value is selected from list:

LLab := :T_DAL.CMB_AERO;
select count(*) into yra from dual
where exists (select * from Aviacija.T_AERO where AERO_PAV = Llab);
I get yra=0, because there is no such record where AERO+PAV = 42 (cause it is ID)

But it works correctly if I enter new value, save it to DB (do insert and I have LLab = TextIEntered)

Is there any possibilities to get_list_element_value if it is combobox?
I need to know a list_index for that

Solveiga






Solveiga
0
 
Helena Markováprogrammer-analystCommented:
Of course, see GET_LIST_ELEMENT_COUNT example in the on-line help. You have to LOOP through the list.
0
 
SolveAuthor Commented:
According to that question I would like to ask one more... and my problem will be solved.

I succseeded to check if there is such value in DB, and if is not, I call my form for enter new aerodrom.
when I enter new record with all required fields. I save it and want this value to be in my other form's that Item
:T_DAL.cmb_aero.

Could you help?

Solve
0
 
Helena Markováprogrammer-analystCommented:
You can store the value in
1) a global variable
 or
2) in a package variable in a library attached to the both forms (this is better). You have to use CALL_FORM or OPEN_FORM with parameter SHARE_LIBRARY_DATA.
In such cases it is accessible to the both forms.
0
 
SolveAuthor Commented:
First I choice the first varian. I store value in a global

And how to assign it to my combobox when I close that form?

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
Do you mean to store it to the block item or list ?

If to the the block item - then something like this:
IF :=:global.your_variable is not null THEN
    :T_DAL.cmb_aero:=:global.your_variable;
    :global.your_variable:=NULL;
END IF;

if to the list - populate the list once again.
0
 
SolveAuthor Commented:
Yes...I understood,
but when I call_form, it doesn't allow me to save record. It say :
FRM-40403 A calling form has unapplied changes. Save not allowed...
when I pree exit form and it say
do you want to apply? I prees ok, when get a message FRM-40404 record applyed..

But it doesn't insert into DB and I dont see it in cimbobox. My global = null:(

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
Open your called form with OPEN_FORM built-in - with parameter session -
SESSION  Specifies that a new, separate database session should be created for the opened form.

0
 
SolveAuthor Commented:
You see, I ckeck that combobox value in when validate item trigger and I can't do open form
so I do call_form ...


FRM-40737:  Illegal restricted procedure  OPEN_FORM in when-validate-item trigger.

Cause:      Application design error.  A trigger tried to execute a restricted packaged procedure.

Action:      Remove the packaged procedure from the trigger text.

Level:      >25

Type:      Error
0
 
SolveAuthor Commented:
So now I am doing this... Have no choice :(

And closing this question...

Solveiga
0
 
SolveAuthor Commented:
Thank you Henka for your pacience very much!
Good luck :)

Solveiga
0
 
Helena Markováprogrammer-analystCommented:
Good luck with Oracle Solveiga :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.