Solved

when saving need to check if data exists in the oracle table.

Posted on 2016-10-03
33
59 Views
Last Modified: 2016-10-05
CREATE TABLE DSS.SALES_PROFILE_PARTS
(
  ID            NUMBER(38)                      NOT NULL,
  PSPEC_ID      NUMBER(38)                      NOT NULL,
  SPROF_ID      NUMBER(38)                      NOT NULL,
  KEYWORD       VARCHAR2(10 BYTE),
  ACTIVE        VARCHAR2(1 BYTE)                NOT NULL,
  USR_CREATED   VARCHAR2(32 BYTE)               NOT NULL,
  DT_CREATED    DATE                            NOT NULL,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE)

constraints

ALTER TABLE DSS.SALES_PROFILE_PARTS ADD (
  CONSTRAINT SPARTS_PK
  PRIMARY KEY
  (ID)
  USING INDEX DSS.SPARTS_PK
  ENABLE VALIDATE);

ALTER TABLE DSS.SALES_PROFILE_PARTS ADD (
  CONSTRAINT SPARTS_PSPEC_FK 
  FOREIGN KEY (PSPEC_ID) 
  REFERENCES DSS.PART_SPECS (ID)
  ENABLE VALIDATE,
  CONSTRAINT SPARTS_SPROF_FK 
  FOREIGN KEY (SPROF_ID) 
  REFERENCES DSS.SALES_PROFILES (ID)
  ENABLE VALIDATE);

Open in new window



Cannnot create unique constraint (PSPEC_ID , SPROF_ID) since we can enter same part number many times which will have tails.

 Now 2nd Table ( Detail)  - TAILS Table

CREATE TABLE DSS.SALES_PROFILE_PART_TAILS
(
  ID            NUMBER(38)                      NOT NULL,
  SPARTS_ID     NUMBER(38)                      NOT NULL,
  TAIL          VARCHAR2(25 BYTE),
  USR_CREATED   VARCHAR2(32 BYTE)               NOT NULL,
  DT_CREATED    DATE                            NOT NULL,
  USR_MODIFIED  VARCHAR2(32 BYTE),
  DT_MODIFIED   DATE
)

CREATE UNIQUE INDEX DSS.SPTAILS_PK ON DSS.SALES_PROFILE_PART_TAILS
(ID)
LOGGING
TABLESPACE MISC_I
PCTFREE    5
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE UNIQUE INDEX DSS.SPTAILS_UK ON DSS.SALES_PROFILE_PART_TAILS
(SPARTS_ID, TAIL)
LOGGING
TABLESPACE MISC_I
PCTFREE    5
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

Open in new window


The problem we are having is in the parts table the same part has to be entered with different tails. Again same part number without tails. But the same part number without tail can be entered only once. We do not want to have form validation, but write a procedure to validate this. First we should be able to enter part and save. This part may or may not have tail. Again we enter the same part number and save. Now we have to stop saying the part is already existing based on pspec_id and tail as input parameters. Any suggestions and help appreciated. Need a database procedure.
part-tail.jpg
0
Comment
Question by:anumoses
  • 12
  • 12
  • 3
  • +3
33 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 251 total points
ID: 41826990
With the limited information here, I would think you need a trigger (or more than one).  That would be the only way that you could prevent a record from being inserted.

A procedure, or really a simple query would do, would identify records that shouldn't be there, but wouldn't prevent them from getting there.

This looks like quite a large undertaking, so you might want to think about trying to get someone to do it through Gigs, but you would need a lot more information.  Like real requirements.  There is no foreign key on the SALES_PROFILE_PART_TAILS, which seems odd for a detail table, and you cannot surmise the relationship between the 2 tables.

Before going much further consider this situation.

Part gets inserted into SALES_PROFILE_PARTS with no tails.
Part gets inserted a second time into SALES_PROFILE_PARTS with no tails but is intended to have tails.  The tails are inserted after the part.

If that situation happens (i.e. details inserted after master and record with no details inserted first), then there is no way that you can prevent this from happening.  The reason is that the second insert of the part would fail because there is already that part with no details.  You would have to guarantee that the last record for the part would always be the one with no details.  Then if you wanted to insert again with a different set of details, you would have to delete the one with no details and insert it again later.
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 83 total points
ID: 41827052
If you are doing this in oracle forms and that is the only way parts and tails are modified you could separate the save and commit processing  :  
however not using the default forms behaviour you will have a lot work to prevent unwanted action !
using the save button issue the POST build-in that wil insert/update/delete your tables ,
call your procedure to check the modified data , if valid call a procedure that issues a COMMIT on the server/database side to COMMIT your data.
Pay attentention: if you use the forms COMMIT-build-in it will check for modifications in the form, but after the POST the form considers its internal state as not CHANGED (all modifications are transferred to the tables in the database) and then the processing of the COMMIT-build in stops.
If deletes and record/block clearing are supported you wil have to keep track of all the possible input parameters of the changed records even when that records are not available in the form blocks anymore.
It wil not be easy to deliver a user friendly feedback if you can change multiple records and  check afterwards : what record is wrong ?

Try to reconsider if modifying and saving 1 part/tail record at a time is not a better option.
And consider using triggers (on-statement-triggers to save the input parameters for example in a temporary table/after-statement-trigger to validate all saved input parameters against your databaseprocedure.  Then your check wil work also for other then forms modifications.
The problem of a nice feedback will remain.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827074
We do not want to do any forms validation. We want database validation. As per Johnsone, what kind of trigger (database) do I need to write? Please explain. I know we cannot have constraints. We dropped unique constraints as one part can be entered with multiple tails. One part can be enter once without no tail. But this has to be validated after the part is entered and saved. On save we have validate if part is entered once or not. Again, Tail can be entered before part. We stop that.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41827085
If I understand what you are doing, and that is a big if...

If a part with tails can be entered after a part with no tails, then you have a problem.  The problem is that the part with tails will be entered as a part with no tails (the insert of the part happens first).  This would break any validation that a trigger would be able to do as the trigger would work at the statement level.  You need to validate after the "transaction" has completed.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827251
Yes validation after the transaction has happened for parts. Can you help me with the trigger or your way of committing the transaction and do the validation for tails?
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41827309
Oracle doesn't have a transaction level trigger.  Only a statement level trigger.  You cannot do what you are asking for with triggers.  Like I said, a full requirements document would help someone determine what could be done.

My guess is that you are going to have to determine a way to pass all the information into a PL/SQL procedure/package and have the package do the validations and inserts within its own logic.  This will not prevent someone from doing direct SQL against the tables and breaking your business logic, but without being able to do it with constraints and/or triggers, there is nothing you can do about it.

You could probably redesign your table structure to support your business logic, but it doesn't sound like that is an option.
0
 
LVL 18

Expert Comment

by:Pawan Kumar Khowal
ID: 41827344
Why dont you simple variable technique. Before inserting check the values are there or not.

declare
     Pawan;
    begin
       select count(*) into Pawan from dual;
  
       if pawan >= 1 then
               
			   --- UPDATE Statement here 

       end if
	 end

Open in new window

0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 41827487
I think that you need to combine validation in forms and in database.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827830
Johnsone, I know there is a big hole as you have mentioned about backend processes. We have been in many meetings regarding the constraints and we had to drop these constraints as airlines will get parts with or without tails. This is where I am right now.

procedure tail_validate (in_sparts_id in number
                                          ,in_sprof_id  in number
                                          ,in_tail     in varchar2) is
                        
    v_sparts_id part_specs.id%type;
    v_tail sales_profile_part_tails.tail%type;
    v_tail_count number;
    v_event_msg varchar2(2000);
  Begin
       select count(*) into v_tail_count  
         from DSS.SALES_PROFILE_PARTS spp
                , DSS.SALES_PROFILE_PART_TAILS SPST
        where spp.id = spst.sparts_id
          and spp.id = v_sparts_id 
          and sprof_id = in_sprof_id
          and spst.tail = v_tail;
          
     if v_tail_count = 0 then
        null;
      else
        v_event_msg := 'Error inserting Part. Only One Part can be entered without Tail. Please Complete Tail before Saving';
     End if;
  End; 

Open in new window


sprof_id is the Customer sales profile.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41827866
I believe that the intent here is that the validation be in the database rather than in the form because if it is in the database you cannot bypass it with SQL.  However, the type of validation that you need cannot be done in such a way the database can prevent the insert.

There is going to have to be a procedure that does all the work.  Somehow gets the information it needs.  Then you are going to have to rely on restricted privileges from preventing direct SQL as much as you can.  There is no way to completely stop direct SQL from a privileged account or the object owner.

The procedure you have so far only looks like a validation.  That wouldn't prevent the insert, it is just a check.  If your goal is to simply have a procedure that does the check, then I don't see why that has to be a database procedure, why couldn't it be a procedure in the form?  That procedure wouldn't prevent an insert.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827874
The company is moving away from form validations. But one big hurdle is in the above procedure, tail can be null first time. So I am not sure how will this work. I am calling this procedure in the post forms commit. But not sure how to handle in_tail as null value.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827887
transaction
As you mentioned, I first saved one part. Second line same part but was able to save - Both no tail. So I am not sure how to make this work.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827914
@johnsone- There is going to have to be a procedure that does all the work.  Somehow gets the information it needs.  Then you are going to have to rely on restricted privileges from preventing direct SQL as much as you can.  There is no way to completely stop direct SQL from a privileged account or the object owner.

As you also mentioned in your previous answer there has to be some code that does all the checks and can prevent the transaction from being inserted. I am not sure how to do
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41827918
All your procedure does is set a variable to some fixed string.  Then that string is destroyed when the procedure completes.  You either need to have a return that the form can use, or throw an error.

You really aren't doing anything in the procedure.  You are doing a form validation by running a database procedure.  You really aren't accomplishing anything there.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41827923
Can you provide with some examples that I can look into?
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41827939
Examples of what?

Returning a value from a procedure (use an OUT parameter)? -> http://docs.oracle.com/database/121/LNPLS/subprograms.htm#LNPLS659

Throw an error (use RAISE_APPLICATION_ERROR)-> http://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS99960

Passing values in could be done with individual parameters or collections or a temporary table.  The options on getting the information in is endless.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
ID: 41827940
Found this example Can such thing be used?

Here Is The Test Example How To prevent inserting Duplicate Records

 

 

In sql u can write like this

 

CREATE PROCEDURE [dbo].[Insert_Test_Proc]

@DDa int,

@DDb int,

@DDc int,

@txtvalue varchar(100)

AS

Begin
SET NOCOUNT ON

 

IF EXISTS (SELECT * FROM [TableName] WHERE columnDDa = @DDa and columnDDb = @DDb and columnDDc = @DDc )

BEGIN

RAISERROR ('Record Already Exist',

16, -- Severity,

1 -- State,

)

RETURN -1

END

else

Begin

--Your Code of inserting
End

End

 

 

ie if duplicate record is going inside as per ur case Error message is raised 

Open in new window

0
 
LVL 6

Author Comment

by:anumoses
ID: 41827944
I will look into the links you have sent.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41827947
Looks like Transact-SQL code, so I highly doubt it.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41828057
procedure tail_validate (in_pspec_id in NUMBER
                        ,in_sprof_id in NUMBER
                        ,in_tail     in varchar2
                        ,out_value   OUT varchar2) is

                        
    v_part_count number;
    v_tail_count number;
    v_count number;
    v_event_msg varchar2(2000);
    
  Begin
       select count(*) into v_part_count  
         from DSS.SALES_PROFILE_PARTS
        where pspec_id = in_pspec_id 
          and sprof_id = in_sprof_id;

 
       select count(*) into v_tail_count
         from DSS.SALES_PROFILE_PARTS spp,
              DSS.SALES_PROFILE_PART_TAILS sppt
        where spp.id = sppt.sparts_id
          and pspec_id = in_pspec_id  
          and sprof_id = in_sprof_id
          and tail = in_tail; 
          
      exception
            when no_data_found then
        If in_tail  is null then 
          If v_part_count > 0 then
              out_value :=  'Error inserting Part. Only One Part can be entered without Tail. Please Complete Tail Information.';
          End if;  
        Else     
          if v_tail_count >= 2 then
              out_value :=  'Error inserting Duplicate Tail for the Same Part.';
          End if;
        End if;
  End;

Open in new window


In the pre-insert trigger at the database level, I am thinking of calling this procedure. What other things am I missing?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41828332
I wouldn't think you can use a database trigger to do what you are trying to accomplish.  Also, I believe that if you call that from a trigger you would get a mutating table error.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 83 total points
ID: 41828432
Your unique index: DSS.SPTAILS_UK on the DSS.SALES_PROFILE_PART_TAILS table should give you exactly what you are asking for with no procedures or triggers needed.  That will allow only one record to be entered for a SPARTS_ID value and a blank TAIL number, or any number of records to be entered for this same SPARTS_ID value, as long as each one has a different TAIL number.  But, it will not allow two records to be entered for the same SPARTS_ID value if they each have a blank TAIL number.  And, it will not matter if a record including a tail number is inserted first, or if a record with a blank TAIL number is inserted first.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41828457
I'm not sure of the whole architecture, because there is a lot more involved than just these 2 tables.  But it is the 2 tables and not just the one table with a unique constraint.

You cannot have more than one record in SALES_PROFILE_PARTS without corresponding records in SALES_PROFILE_PART_TAILS.  Seems the part number can be duplicated in the SALES_PROFILE_PARTS table, but can only exist once with 0 corresponding records in the SALES_PROFILE_PART_TAILS table.  You cannot do that with a constraint, that I am aware of.
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 83 total points
ID: 41829656
I was not able to take part in the discussion yesterday: some remarks when reading back>
1) I also think you cannot  do it with a constraint.
2) the mutating table problem will occur if using the procedure on row-level triggers, not if using the procedure on statement level triggers
3) Another way of returning the result of the procedure is to raise an error
in_pspec_id in NUMBER
                        ,in_sprof_id in NUMBER
                        ,in_tail     in varc
v_event_msg := 'Error inserting Part   ' || in_pspec_id || ';' || in_sprof_id  || ' . Only One Part can be entered without Tail. Please Complete Tail before Saving';
raise_application_error(-20010,v_event_msg);
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41829682
This cannot be done with a row level or a statement level trigger.  Because the main table is inserted first, once the part with no details in inserted if you have a statement level trigger, you would never be able to insert the part in the main table again.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41829722
Thanks for all the help and meaningful answers. With all the things we are doing and breaking oracle database ground rules, we arrived to this solution.

Wrote 2 functions. One for part and one for tail.
function part_validate (in_pspec_id in NUMBER
                       ,in_sprof_id in NUMBER) RETURN NUMBER is

                        
    v_part_count number;
    v_count number;
    v_event_msg varchar2(2000);
    out_value varchar2(2000);
    out_param number;
    
  Begin
       select count(*) into v_part_count  
         from DSS.SALES_PROFILE_PARTS spp
        where pspec_id = in_pspec_id 
          and sprof_id = in_sprof_id   
          and not exists (select tail
                            from sales_profile_part_tails  sppt
                           where spp.id =  sppt.sparts_id);


          If v_part_count >= 1 then
              out_value := 'Error inserting Part. Only One Part can be entered without Tail. Please Complete Tail Information.';
              out_param := 1;
              Else
              out_value := null;
              out_param := 0;
          End if;  
                        return(out_param);
  End; 

function tail_validate (in_pspec_id in NUMBER
                       ,in_sprof_id in NUMBER
                       ,in_tail     in VARCHAR2) RETURN NUMBER is

                        
    v_tail_count number;
    v_event_msg varchar2(2000);
    out_value varchar2(2000);
    out_param number;
  Begin
       select count(*) into v_tail_count
         from DSS.SALES_PROFILE_PARTS spp,
              DSS.SALES_PROFILE_PART_TAILS sppt
        where spp.id = sppt.sparts_id
          and pspec_id = in_pspec_id  
          and sprof_id = in_sprof_id
          and tail = in_tail;


          If v_tail_count = 1 then
              out_value :=  'Error inserting Duplicate Tail for the Same Part.';
              out_param := 1;
          Else
              out_value := null;
              out_param := 0;
          End if; 
            return(out_param); 
  End; 

Open in new window


In the form on the pre-insert trigger, called these functions

select dss.pkg_cust_requirements.part_validate (:sales_profile_parts.pspec_id
                                                 ,:sales_profile_parts.sprof_id) into v_out_param from dual;
         
                                                 
      If v_out_param = 1 then
      	msg_alert('Only One Part/FSC can be entered without Tail. Please Complete Tail Information.','E',false); 
      End if;

Open in new window


  declare 
  	
  	v_out_param number;
Begin  	

  select dss.pkg_cust_requirements.tail_validate (:sales_profile_parts.pspec_id
                                                 ,:sales_profile_parts.sprof_id
                                                 ,:sales_profile_part_tails.tail)  into v_out_param from dual;
                                                 
      If v_out_param = 1 then
      	msg_alert('Error inserting Duplicate Tail for the Same Part/FSC.','E',true); 
      End if;
      

End;

Open in new window


So this is what I have come up with.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 83 total points
ID: 41829730
So, now are you going to accept one or more of the responses here as being helpful?  Or, are you going to close the question and say that you solved it yourself?
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 41829733
Anumoses, as I see your post (ID: 41829722) you call validation DB functions from Forms. I think that it is a good practice because these functions can be used by forms and also by another DB procedures or other applications. We do the same.
But it is not "The company is moving away from form validations" :).
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 251 total points
ID: 41829741
I have to agree that this is not moving away from forms validations.  You are doing the validation in the form by calling a database function.  All you did was move the validation code from the form to the database.  Based on my limited knowledge of forms, you really haven't accomplished much other than move the code from the front end to the back end.
0
 
LVL 22

Assisted Solution

by:Helena Marková
Helena Marková earned 83 total points
ID: 41829747
Johnson, you are right. The advantage is that the same validation can be used in more places.
0
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 83 total points
ID: 41829758
Another advantage is the forms stay smaller and simpler this way, so they load faster.  I agree that this isn't really moving away from forms validation.  It simply moves the logic involved in forms validations to the database.  I've been working with Oracle Forms for 25 years and that has almost always been my approach (and the recommendation of published authors who have Oracle Forms experience).
0
 
LVL 34

Expert Comment

by:johnsone
ID: 41829773
My understanding (which could be incorrect) is that you can create libraries with forms and keep these kinds of procedures in those libraries and they can be re-used that way.  So it really isn't adding a whole lot by putting it in the database.

Faster load time is probably the best argument for moving to the database.

I guess my understanding of the question was incorrect.  I thought we were trying to move the entire process into the database.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41829776
Thanks for all the contributions
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now