Click here to Skip to main content
14,110,342 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

48.3K views
195 downloads
19 bookmarked
Posted 5 Aug 2005
Licenced

Generic way to do primarykey & foreign key validation from frontend

, 5 Aug 2005
Rate this:
Please Sign up or sign in to vote.
Generic way to do primarykey & foreign key validation from frontend

Following is the way to do the primarykey & foreign key validation before the data is actually inserted or updated in tables.

following are the steps

Assumption is that at oracle side the table is having a proper defination of primary key and foreign key.

1)

create global temporary table pkfk_chk_columns (

col_name varchar2(30),

col_label varchar2(40),

col_value varchar2(1000)

) on commit delete rows

2) compile the attached procedure

create or replace procedure PKFK_CHK(tablename VARCHAR2,

PKey_FKey VARCHAR2,

retval OUT VARCHAR2,

errmsg OUT VARCHAR2) is

-- PKey_FKey := 'PK' or 'FK'

From any frontend application before you submit the request to save the data in oracle tables, you need to insert records in table pkfk_chk_columns

col_name :- Name of the field (in UPPERCASE)

col_label :- Label of the field

col_value :- VALUE of the field

For Primary Key check

Insert records in pkfk_chk_columns related to primary key fields

Call the stored procedure PKFK_CHK by passing relevant values

- tablename on which the primary key or foreign key needs to be checked

- PK :- Primary Key check

- retval and errmsg will be error code and userfriendly error message which can be thrown to enduser from the frontend application.

For Foreign Key check

Insert records in pkfk_chk_columns related to foreign key fields

- tablename on which the primary key or foreign key needs to be checked

- FK :- Foreign Key check

- retval and errmsg will be error code and userfriendly error message which can be thrown to enduser from the frontend application OR Pass the retval and error message to application error handler routine.

DO not insert records into this table if data is NULL or blank for the given field.

SO NO MORE ORA-xxx errors related to primary key or foreign keys..

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

SudhirKankal
Program Manager
India India
From RYK Science College & JDC Bytco Institute of Management Studies, Nasik. I am into Software Industry since 18 years. Manage small to large scale global deliveries. Working as Program Manager in Fundtech.

You may also be interested in...

Comments and Discussions

 
GeneralNice trick ... but ... Pin
Scott Walter5-Aug-05 5:35
memberScott Walter5-Aug-05 5:35 
GeneralRe: Nice trick ... but ... Pin
Keith Farmer5-Aug-05 16:16
memberKeith Farmer5-Aug-05 16:16 
GeneralRe: Nice trick ... but ... Pin
SudhirKankal9-Aug-05 6:23
memberSudhirKankal9-Aug-05 6:23 
GeneralRe: Nice trick ... but ... Pin
Keith Farmer9-Aug-05 6:27
memberKeith Farmer9-Aug-05 6:27 
GeneralRe: Nice trick ... but ... Pin
Scott Walter20-Aug-05 8:06
memberScott Walter20-Aug-05 8:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.190518.1 | Last Updated 5 Aug 2005
Article Copyright 2005 by SudhirKankal
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid