[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

INSERT RECORDS IN FORM

Posted on 2014-09-01
31
Medium Priority
?
3,194 Views
Last Modified: 2014-09-10
hi
i have a form , list box , then button
then trigger on when_button_press will loop a cursor and fill in the block like this
Go_Block('PO_DELV');
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
          	 next_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Open in new window


but i always got error message : record must be enetered or delte first
it sure from statement : next_record
how to solve this issue
0
Comment
Question by:NiceMan331
  • 18
  • 10
  • 3
31 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40297828
Try
Go_Block('PO_DELV');
clear_block(no_validate);
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
        	 create_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40297869
same result
the error still there
0
 

Author Comment

by:NiceMan331
ID: 40297875
sorry for the mis explanation
my form has 3 blocks
po_m  header
po_d details
po-delv details
and relationship between one to them

the trigger in the button will first fill one record only in the po_d , then filling related records for po-delv , like this
declare
 
  	
  Cursor c_ent IS
 select * From pr_d where PR_d_ID = :GENRAL.LST_IT;
 
  Cursor c_tran IS
 select * From PR_DELV where PR_d_ID =  :GENRAL.LST_IT;
 
 
   vk  c_ent%rowtype;
   vd  c_tran%rowtype;
   
  BEGIN
                                	
  Go_Block('po_d');
    next_record;
    	OPEN c_ent;
          loop
          	 fetch c_ent into vk;
          	  exit when c_ent%notfound;
          	 
     	SELECT NVL(MAX(Po_D_ID),0)+:system.cursor_record INTO :po_d.PO_D_ID FROM Po_D;
    	 :po_d.item_no:= vk.item_no;
      :po_d.PO_UNIT:= vk.Pr_UNIT;
      :po_d.PO_QTY:= vk.Pr_QTY;
      :po_d.PRICE := vk.PRICE ;
       :po_d.PR_ID := vk.PR_ID ;
      
      
      			
			

SELECT initcap(EUNAME) into :po_d.un_name FROM STK_UNIT where ucode = :po_d.po_unit;
	
     --   next_record;
   
  	  end loop;
     	
       CLOSE c_ent;
  
  
  Go_Block('PO_DELV');
clear_block(no_validate);
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
        	 create_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Open in new window


i dont think the problem in form po_d , because it insert only one record
but i think it is for po-delv because it has more than record
also , when the error appear , the cursor stands on new record of po_delv
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40297921
Try
declare
 
  	
  Cursor c_ent IS
 select * From pr_d where PR_d_ID = :GENRAL.LST_IT;
 
  Cursor c_tran IS
 select * From PR_DELV where PR_d_ID =  :GENRAL.LST_IT;
 
 
   vk  c_ent%rowtype;
   vd  c_tran%rowtype;
   
  BEGIN
                                	
  Go_Block('po_d');
    clear_block(no_validate);
    	OPEN c_ent;
          loop
          	 fetch c_ent into vk;
          	  exit when c_ent%notfound;
          	 create_record;
     	SELECT NVL(MAX(Po_D_ID),0)+:system.cursor_record INTO :po_d.PO_D_ID FROM Po_D;
    	 :po_d.item_no:= vk.item_no;
      :po_d.PO_UNIT:= vk.Pr_UNIT;
      :po_d.PO_QTY:= vk.Pr_QTY;
      :po_d.PRICE := vk.PRICE ;
       :po_d.PR_ID := vk.PR_ID ;
      
      
      			
			

SELECT initcap(EUNAME) into :po_d.un_name FROM STK_UNIT where ucode = :po_d.po_unit;
	
     --   next_record;
   
  	  end loop;
     	
       CLOSE c_ent;
  
  
  Go_Block('PO_DELV');
clear_block(no_validate);
      	OPEN c_tran;
          loop
          	 fetch c_tran into vd;
          	  exit when c_tran%notfound;
        	 create_record;
   
    	SELECT NVL(MAX(Po_DELV_ID),0)+:system.cursor_record INTO :Po_DELV.Po_DELV_ID FROM Po_DELV;			
			
 :PO_DELV.DEL_QTY:= vd.DEL_QTY;
      :PO_DELV.DEL_DATE:= vd.DEL_DATE;
      

   
  	  end loop;
     	
       CLOSE c_tran;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40298033
no effect also
0
 
LVL 21

Expert Comment

by:flow01
ID: 40298887
you are are still getting the same error ? (record must be entered or deleted first)
and you are getting it on the second time you execute the create_record in the po_delv ? (or the first ?)
sounds like you are creating an empty record , but i expect at least :Po_DELV.Po_DELV_ID  to be not null.
Add
   message('before validate record:' || :system.cursor_record || ' id:' || :Po_DELV.Po_DELV_ID, acknowlegde);
   validate_record;
   message('after validate record:' || :system.cursor_record || ' id:' || :Po_DELV.Po_DELV_ID, acknowlegde);
before the 'end loop'  to debug what is happening.
0
 

Author Comment

by:NiceMan331
ID: 40300338
the trigger not accepting the statement validate_record;
0
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40300353
where do you run the above codes? within one trigger?
0
 

Author Comment

by:NiceMan331
ID: 40300361
i invetigate the trigger like this
after the trigger insert into block po_d , and before insert into po-delv
it sent
record must be entered
then after i see first record inserted in po_delv
before validate record 1  id = 276
after validate record 1  id = 276
before validate record 2 id = 277
after validate record 2 id = 277
before validate record 3 id = 278
after validate record 3 id = 278
0
 

Author Comment

by:NiceMan331
ID: 40300378
yes , within one trigger , as per the advise of flow01
0
 
LVL 21

Expert Comment

by:flow01
ID: 40300447
Sorry, just checked: should be
validate(record_scope);

you did not answer to:  you are are still getting the same error ? (record must be entered or deleted first)
and
The answer to: you are getting it on the second time you execute the create_record in the po_delv ? (or the first ?)
is no, i get it on either the creation of the 4e record or elsewhere:
because I see 3 records displayed.
If you expect only 3 records the error is elsewhere : so what number do you expect ?

Check if your trigger reaches its end : add
  message('end of trigger', acknowlegde);
after the
CLOSE c_tran;
-- is "CLOSE c_tran" the last statement of the trigger or the last statement you showed us ?
0
 

Author Comment

by:NiceMan331
ID: 40300502
you are are still getting the same error ? (record must be entered or deleted first)
yes
 
you are getting it on the second time you execute the create_record in the po_delv ? (or the first ?)
if i understant your question well , i execute the trigget one time only , not 2 , but the code first insert in po_d without any error , it accure only in the second statement while inserting in po_delv
plz if i understood wrong explain to me
If you expect only 3 records the error is elsewhere : so what number do you expect ?
in my example here they are only 3 records , the 3rd one is the last record inserted
Check if your trigger reaches its end :
yes , it reached
0
 
LVL 21

Expert Comment

by:flow01
ID: 40300584
Sounds like the button pressed trigger does it's job.
When you get the error, you say your are in block po_delv:
can you see the records in that block after the error?  How many are there ? In what record is the cursor positioned ?
Are they all correctly filled.
And after the error are you able to navigate to another record in the same block ?
And to another block ?
0
 

Author Comment

by:NiceMan331
ID: 40300655
Sounds like the button pressed trigger does it's job.
exactly ,
When you get the error, you say your are in block po_delv:
yes
can you see the records in that block after the error?  
yes
How many are there ?
3 records exactly
In what record is the cursor positioned ?
at new record after 3 posted
Are they all correctly filled.
yes
And after the error are you able to navigate to another record in the same block ?
And to another block ?
yes , and i'm be able to save all records also
0
 
LVL 21

Expert Comment

by:flow01
ID: 40300759
What do you mean with 'at new record after 3 posted' ?
Is that an empty row at line 4?
And what do you mean by posted ?  Oracle forms has a POST-command that triggers the insert statements in the database without committing them, but I don't know how you could see in a form if a just filled record is also inserted in the database or not.
0
 

Author Comment

by:NiceMan331
ID: 40301074
What do you mean with 'at new record after 3 posted' ?
Is that an empty row at line 4?                         Yes exactly
And what do you mean by posted ?                Sorry , just wrong expression , I mean filled in the block.                                                 but I don't know how you could see in a form if a just filled record is also inserted in the database or not.   ,                                     You are correct , I just see records inserted after save it
0
 
LVL 21

Expert Comment

by:flow01
ID: 40301115
I think i found it:
if i'm in an empty block  and  try to create a new record i get the same message  and the  new-record is not created,  after filling the record the next create record is succesfull.

Thinking in the same line don't create the record at the first record fetched : there is already an empty record waiting to be filled after the clear_block:

                 fetch c_tran into vd;
                  exit when c_tran%notfound;
               create_record;
>>>
                 fetch c_tran into vd;
                  exit when c_tran%notfound;
                  if c_tran%ROWCOUNT > 1 THEN  -- first empty record is already there after clear block
                       create_record;
                  end if;

same action is probably needed on the c_ent loop (i don't know for sure why there is only 1 message but if you send 2 messages without the acknowledge option you will only see the last, probably the same feature)
0
 

Author Comment

by:NiceMan331
ID: 40301227
Yes , I think you are now correct , let me test it when I be in my office , thanx
0
 

Author Comment

by:NiceMan331
ID: 40302867
yes , i investigate it well , by selecting some records with diff conditions
 if c_tran%ROWCOUNT > 1 THEN  -- first empty record is already there after clear block
                       create_record;

Open in new window

this ststement not executed if data having one record only , then i removed this statement
then i tried many cases :

- the error occurd after finish fill the record in block po_d , and before going to block po_delv , then occured again after finish fill in block po_delv
i removed create_record from  statement of the block po-d
- no error occured in that place , but still in the po_delv
- i removed create_record from statement of po-delv
then i select data having one record of po_d and one record of po_delv
the triggere successed without any error
- i return back the statement create_record to po-delv only to test data having more than record of po-delv , here all records uploded and the error occured on the 10th record (the last one ) , and not after in an empty record as happened yesterday

now another problem , if the trigger uploaded one record of po_d and its po-delv
then i want to select to upload another record in po-d , here i need again to return back the statement create_record , otherwise it will overwrite
hope i explained it well
0
 
LVL 21

Expert Comment

by:flow01
ID: 40303261
It's not the answer to your question : but I would have taken another approach to begin with. And their is some rembrance of another question from you with a simular situation.  I would create a procedure to insert the records in the database and in de button-trigger call the procedure and requery the data, and change something in its own value to trigger commit-processing).
But
"this ststement not executed if data having one record only ,"
That was the intention , didn't it  succeed in loosing the message ?

extra po_d  :   avoid the clear_block  and  create a new record if there is an existing record
IF :po_d.PO_D_ID IS NOT NULL THEN
    create_record;
END IF;
(if there are uncommitted po_delv  with the first po_d you might get the 'do you want to commit'. I'm not for sure it will work but you could try the 'POST' command before starting this second step to insert that records in the database without committing them.
0
 

Author Comment

by:NiceMan331
ID: 40303323
I would create a procedure to insert the records in the database and in de button-trigger call the procedure and requery the data, and change something in its own value to trigger commit-processing).
yes sure i remeber , and at that time you advced me to call a database procedue from the form to fill the data in , yes , shall i do the same here ?
0
 

Author Comment

by:NiceMan331
ID: 40303379
but even i used that way , we still have to find a solution when need to add new record to po_d
because i'm selecting data from list box , and adding it to the block po_d , each value from list box will add one record only to po_d
and this statement
IF :po_d.PO_D_ID IS NOT NULL THEN
    create_record;
END IF;

Open in new window

can we change it to :
if block po_d is empty , then call the procedure to add record , if not , then create record
0
 
LVL 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 40304743
I don't know what your requirements are.  You can do this logic in oracle forms , but you can make it easier for your self if you use Forms to do what it is build for: showing and entering data to insert and modify:  so if there is a part of logic that add data that are not entered by a user, but depends on arguments and other database tables i would not try to create that records in forms , but create and insert it in a procedure and query  it by means of forms. (You can even have that procedure build in Oracle forms itself if you want the logic together in the form) . I try to keep my forms as empty as possible.
But  if you have it working now then spend your time on other things.
I would not mix 2 methods : work out the  action after selecting from the list box in the same way:  you only have to keep in mind  you don't need to create a new record if there is an empty one waiting for you.
0
 

Author Comment

by:NiceMan331
ID: 40307226
here are my tables
http://www.experts-exchange.com/Database/Oracle/Q_28384265.html
and here is the final description of the tables
tables of purchase reuqest : PR_M , PR_D , PR_DELV

Pr_m                         pr-d                                 pr_delv
Pr_id                          item                                qty 
Pr_date                     qty                                 delv_date

Open in new window

                                 
and tables of purchase order : PO_M , PO_D , PO_DELV
Po_m                         po-d                                 po_delv
Po_id                          item                                qty 
Po_date                     qty                                   delv_date

Open in new window

                                 
here is the scenario of the work
warehouse keeper will prepare purchase request contain :
purchase request data : number - date ,, etc  (PR_M)
purchase request details (PR-D) : each request may have one or more than one product , QUANTITY
then delivery schedual (pr_delv)

this document will go to purchasing department
they may make purchase order for items requested from one or more than one purchase request
so in the form of Purchasing , i have 3 blocks
PO_M will contain main data of the purchase request
then po_d  to contain details of items
and po_delv to contain delivery svedual
and i have non_database block having 2 list box
one list box contain data of table pr_m , if i select one record from it , it will display its pr-d in another list_box , and here if i select any record from this second list_box
it will bring its data and fill it in block po_d , and will also brong the data of pr-delv and fill it in block po_delv
then if again i select another item from list box , again fill it in new record of po-d and so on

i think give me a chance to adjust to use database procedure
0
 

Author Comment

by:NiceMan331
ID: 40311910
i created database procedure to insert data into table po_d , like this
CREATE OR REPLACE PROCEDURE upl_po_det(p_po IN number,p_poid in number) IS
    Cursor c_ent IS
 select * From pr_d where PR_d_ID = p_poid;
    vk  c_ent%rowtype;
     tr_id integer;
       BEGIN
      	SELECT NVL(MAX(Po_D_ID),0)+1 INTO tr_id FROM Po_D;
      	OPEN c_ent;
          loop
          	 fetch c_ent into vk;
          	  exit when c_ent%notfound;
                
   INSERT INTO po_d (po_id,po_d_id,item_no, PO_UNIT, PO_QTY, PRICE, PR_ID )
   values( p_po, tr_id , vk.item_no, vk.Pr_UNIT , vk.Pr_QTY , vk.PRICE ,vk.PR_ID );
     end loop;
    
       CLOSE c_ent;

  EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
      
     end;
/

Open in new window

note : that was the 1st part , just to test it working , i want to add codes to the same procedure to insert data of po_delv
here i have one issue
as per what i described above about the scenario of posting data in the form
user will first manually post data in block po_m
then he go to list box , select one record , in purpose to insert it in table po_d
the procedure not accept to insert into the child table unless it has record in the parent table
in here i have to commit the form before calling the procedure like this
when button press
COMMIT;
 upl_po_det(:Po_m.Po_id,:GENRAL.LST_IT);

Open in new window


1- how to avoid saving data if user decide later to cancel the entry ?
2-how to execute to query the record of po_d while still in the same po_m record
because now , the procedure successed , i closed the form then open it again , i query data depend on po_m , i found the child record of po_d there correctly
in our previous question , it was easy because the procedure bring the data for both parent & child tables , at that time we changed the form mode to query mode
then execute the procedure , then execute_query , data directly shown
0
 
LVL 21

Expert Comment

by:flow01
ID: 40312783
1.  In the BUTTON-pressed
      use the build-in POST instead of COMMIT
      the changes in the form / parent-block wil be made in the database without committing the data.
      Your procedure wil find the parent record.
2.   you can not query it from outside the block (and if you view the code oracle forms generates/uses for synchronizing a master-detail , you will see block changes to.

      go_block('PO_D' );
      execute_query;   -- quiries po_d-block (with standard parent-child design it will only select childs of the parent
      go_block('PO_M');
      -- if   the block 'PO_DELV') is a child of po_d it will probably be refreshed automatically ;
      -- if   the block 'PO_DELV') is a child of po_m  you will have to do a go_block/execute_query/go_block for that block to.
0
 

Author Comment

by:NiceMan331
ID: 40312856
Yes , po_delv is a child block of po_d.            , so , you mean after executing the procedure I will go block po_d and query it in same trigger ?
0
 
LVL 21

Expert Comment

by:flow01
ID: 40313164
the procedure inserts the children : they are not committed, but since you are in the same user session ,  that session  can query them.
0
 

Author Comment

by:NiceMan331
ID: 40314086
excellent
i really thank you , it is ok now
but i still didn't understand the logic
1- how the procedure understood the temporary value of pk of po_m and accept to create children record upon it
2-how the form bring the record inserted via procedure without commit

those just for my understanding
thanx again
0
 
LVL 21

Expert Comment

by:flow01
ID: 40314228
Essential is that oracle forms  opens en keeps the same database session until you logout.

Within a session all changes made by insert, update and delete are available to that session without issuing a commit.
So records you do insert you can also query : no commit needed.
Others sessions in the same database however cannot query the records you Inserted without a commit.

When you use the commit-buildin of oracle-forms it checks if there are changes in the blocks : if so it translates that changes into the corresponding insert, update en delete statements , executes that statements and issues a commit on the database.
By using the post-command same actions occur but the commit is not issued,  So you insert a po_m record in the oracle forms block  and with the post command that record gets inserted in the database. It's there for your session and that session can also insert children to it.

Your procedure   shares the same database_session: it can query the  po_m record you just 'POST'-ed and you are thus able to insert children-records referencing that records.

Once you leave the procedure you are back in oracle-forms but still within the same user-session. Once again you can query all inserted data within that session. If you use oracle-forms execute_query to do that, the records are queried and the result placed in the oracle-forms block.  
Others sessions however stil can't query po_m and details because you did not issue a commit.

When you now use the commit-buildin of oracle-forms it checks if there are changes in the blocks.
It know's that your po_m record is already inserted but not committed so it only will issue a commit to the database.
That commit wil commit all the changes of the session,  po_m inserted by forms and child-records inserted by your procedure.
If the oracle forms user cancels then 'rollback'-buildin will check if there are uncommitted changes in the block and if so, will issue a rollback-command to the database. That command wil undo all changes of the session including the insert of the child-records.

And now for tricky part: (maybe confusing but it gives the essentials)
If  you would use a COMMIT (*1) before calling the procedure, the child records will be still inserted and thereafter queried by Oracle forms but if you use another COMMIT within forms it will check and find that the po_m record is already inserted and committed and that the childrecords are all queried and not changed : on the database nothing will happen. You can then leave the forms-session without being asked to commit and the inserted child-records will not be committed and thus lost.  
You could  prevent that by changing something in the po_m record you already committed after calling the procedure
:PO_M.PO_DATE := :PO_M.PO_DATE;
The record is now again marked as changed . All commit-processing works normal : at commit time the record will be updated.
or
another possibility is to issue the database-commit your self:  don't use  the COMMIT of oracle-forms (it will have not effect as just explained) but call a procedure (server side)  that issues the commit.
example
CREATE OR REPLACE PROCEDURE serverside_commit
IS
BEGIN
    commit;
END;
-- problem wil be the choose the moment of executing the procedure: you still want the user to be able to cancel the operation *1)

*1) as you detected your self, with using the COMMIT-command you can't cancel the insert of the po_m record anymore:  i only toke it back in this storie to enlighten what happens in oracle-forms and database.
0
 

Author Comment

by:NiceMan331
ID: 40314951
Ok thanx for the good explanation
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question