Home » SQL & PL/SQL » SQL & PL/SQL » How to control the inserting the new records when it cross the limits (11g, Window 8)
How to control the inserting the new records when it cross the limits [message #629421] Tue, 09 December 2014 02:37 Go to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Dear All.
I have a table std
Create table std
(
std_id varchar2(4) primary key,
std_name varchar2(14),
address varchar2(20),
email varchar2(15)
)


and i insert fiew rows
insert into std values ('B001', 'zahid', 'pesh', 'abc');
insert into std values ('B002', 'jamil', 'pesh', 'jbc');
insert into std values ('B005', 'zahid', 'pesh', 'ksd');
.



i want to alter this table so that it may accept only 30 rows, it count the std_id values, if(count(std_id)>30) it display error message, that you cant enter the new records.
Is it possible?
Please help me in this records.
Re: How to control the inserting the new records when it cross the limits [message #629422 is a reply to message #629421] Tue, 09 December 2014 02:50 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
What happens if there are 29 rows and two people try to insert a row, don't commit for a second and someone else comes along and inserts a committed row?

What happens to the two transactions?
Re: How to control the inserting the new records when it cross the limits [message #629423 is a reply to message #629422] Tue, 09 December 2014 02:58 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
in such case consistency problem may arises, for that we have to apply the lock that if one user want to update the records then the other user can update that till the 1st one complete his transection.
Re: How to control the inserting the new records when it cross the limits [message #629424 is a reply to message #629422] Tue, 09 December 2014 02:58 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
The easiest way is to have a primary key column populated via sequence with numeric values. Thus, put a CHECK constraint on the column not to allow values >= 30.

ALTER TABLE std
ADD CONSTRAINT check_std_num_rows
CHECK (std_id <= 30)
/
Re: How to control the inserting the new records when it cross the limits [message #629425 is a reply to message #629424] Tue, 09 December 2014 03:03 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
thanks Lalit Kumar, But i want that it must count the values of records for std_id every time, if the count(std_id<=30) it allow to insert the new records otherwise it display error message..
Re: How to control the inserting the new records when it cross the limits [message #629426 is a reply to message #629425] Tue, 09 December 2014 03:09 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Can rows ever be deleted?
Re: How to control the inserting the new records when it cross the limits [message #629427 is a reply to message #629425] Tue, 09 December 2014 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For such constraint you have to use complex mechanism as this is not supported in current dbms.
There are some products that allow you to define and implement in somewhat easy way (compare to what you have to do if you want to do it by yourself) like Rule Gen.

[Updated on: Tue, 09 December 2014 04:03]

Report message to a moderator

Re: How to control the inserting the new records when it cross the limits [message #629428 is a reply to message #629426] Tue, 09 December 2014 03:12 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
yes its possible that row may deleted...
Re: How to control the inserting the new records when it cross the limits [message #629429 is a reply to message #629428] Tue, 09 December 2014 03:21 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Well you could just lock the table in exclusive mode then do a simple plsql IF...THEN check.

But that will not scale in the slightest, however, because you'll be serializing access to an object.
Re: How to control the inserting the new records when it cross the limits [message #629430 is a reply to message #629425] Tue, 09 December 2014 03:33 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Bilal Khan wrote on Tue, 09 December 2014 14:33
thanks Lalit Kumar, But i want that it must count the values of records for std_id every time, if the count(std_id<=30) it allow to insert the new records otherwise it display error message..


Precisely. That is what the check constraint would also do. The id column on which the check constraint is to be implemented, will work like serial number. Once the value reaches 30, the check constraint won't allow any further inserts. You can catch this error, and have a fancy error message displayed to the user on your application.
Re: How to control the inserting the new records when it cross the limits [message #629433 is a reply to message #629430] Tue, 09 December 2014 03:39 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
but it is t necessary that values must be in serial order and start always from 1...
Re: How to control the inserting the new records when it cross the limits [message #629434 is a reply to message #629433] Tue, 09 December 2014 03:46 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Bilal Khan wrote on Tue, 09 December 2014 15:09
but it is t necessary that values must be in serial order and start always from 1...


Add a new column.
Re: How to control the inserting the new records when it cross the limits [message #629435 is a reply to message #629434] Tue, 09 December 2014 03:49 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Lalit Kumar B wrote on Tue, 09 December 2014 14:46
Bilal Khan wrote on Tue, 09 December 2014 15:09
but it is t necessary that values must be in serial order and start always from 1...


Add a new column.
its mean that no one other method is exist for it....
?????
Re: How to control the inserting the new records when it cross the limits [message #629437 is a reply to message #629435] Tue, 09 December 2014 03:52 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What problem are you trying to solve? Why should you restrict number of records in that table?
Re: How to control the inserting the new records when it cross the limits [message #629440 is a reply to message #629435] Tue, 09 December 2014 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bilal Khan wrote on Tue, 09 December 2014 10:49
...
its mean that no one other method is exist for it....
?????


Michel Cadot wrote on Tue, 09 December 2014 10:11

For such constraint you have to use complex mechanism as this is not supported in current dbms.
There are some products that allow you to define and implement in somewhat easy way (compare to what you have to do if you want to do it by yourself) like Rule Gen.

Re: How to control the inserting the new records when it cross the limits [message #629442 is a reply to message #629440] Tue, 09 December 2014 04:10 Go to previous messageGo to next message
Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan
Senior Member
Thanks Michel Cadot, now em studying about Rule Gen, As its new for me nd i never heared about it, if i found any problem then i will consult with u...
Re: How to control the inserting the new records when it cross the limits [message #629451 is a reply to message #629421] Tue, 09 December 2014 05:14 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
I think you can do this using the Rules Manager, there is a nice example here http://www.orafaq.com/node/2449 and of course there is the Rules Manager Developers Guide in the docs. If I remember correctly there are changes in 12.x, so you would need to be sure that any solution would be upwardly compatible.
Re: How to control the inserting the new records when it cross the limits [message #629464 is a reply to message #629421] Tue, 09 December 2014 08:38 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Bilal Khan wrote on Tue, 09 December 2014 03:37
if(count(std_id)>30) it display error message, that you cant enter the new records.


Displaying an error message does not make any sense as there are countless ways to insert (Forms, Sql*Plus, .net, java, sqldeveloper, etc.). An application will never see any message.
Re: How to control the inserting the new records when it cross the limits [message #629483 is a reply to message #629464] Tue, 09 December 2014 12:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
There are I would surmise, three basic ways to do this:

1. John mentioned the RULES MANAGER approach. I never would have thought of that. Nice one. Worth a look.

2. as was also mentioned, some kind of transaction management approach using locks of various natures could be used. There are several ways to write this kind of code but for completeness you will want to use various triggers to achieve it because you need a fully database driven approach or it won't work. And even then it is technically still possible to bypass these triggers with certain types of operations.

3. the last approach would be the SIMULATED COMMIT TIME TRIGGER approach.

None of these approaches is perfect. But then that goes with the fact that the problem you are trying to solve is an ugly one to begin with so most solutions to the problem will also likely be ugly solutions.

You will want to keep in mind some pain points in developing your solution. In particular you need to decide how commit processing will work. Do you need to know immediately that the rule has been violated by your transaction, or can you wait till the end of your transaction to check and see if the rule is violated. Waiting in theory allows for more volatility in the row count as it would be possible for example for committed transactions to add and delete rows during your transaction and as long as when you were done the count was good, your transaction would succeed. This particular problem really brings forward the notion of consistent reads and how you want to handle them. Do some reading about that and then decide on what you actually need.

Good luck. Kevin
Re: How to control the inserting the new records when it cross the limits [message #631870 is a reply to message #629451] Wed, 21 January 2015 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have just tried the third example of Rule Manager in the article John's mentioned (Requirement C) and it does not work in multi-session environment.
See the below execution with 2 sessions, time is set on to follow the sessions, actually the second session just come into play after "dbms_lock" call by the first session, so I stop its listing there post session 2 listing and afterwards go on with session 1 listing.
I just copied and pasted the example of the article but adding some indentation, did not include the rules for the 2 first requirements and changed the inserted empno and ename so the rows are inserted in the order of empno to ease the understanding of what I want to show.
In the end, session 1 first inserts empno 9990 to 9994 then session 2 inserts empno 9995 to 9999; ename are suffixed by 1 for session 1 and 2 for session 2.
I remind the requirement:

Quote:
KING changes the business logic as follows (Requirement C):
1. Hire the third ANALYST to Department ACCOUNTING(10) under CLARK (7782), else go to Department RESEARCH(20) under JONES (7566).
2. Hire the fifth SALESMAN to Department RESEARCH(20) under JONES (7566), else go to Department SALES(30) under BLAKE (7698).
3. Rest go to Department ACCOUNTING(10) placed under CLARK (7782) waiting for manual sorting.


SQL> create user test identified by test default tablespace ts_d01 quota unlimited on ts_d01;

User created.

SQL> grant create session, dba to test;

Grant succeeded.

SQL> grant execute on sys.dbms_lock to test;

Grant succeeded.

SQL> -- session 1
SQL> connect test/test
Connected.
TEST> set time on
09:54:35 TEST> create table emp as select * from scott.emp;

Table created.

09:54:35 TEST> /******* Step (1) Create the Event Object ********/
09:55:37 TEST> CREATE OR REPLACE TYPE AddEMP AS OBJECT (
09:55:37   2    empno NUMBER(4),
09:55:37   3    ename VARCHAR2(10),
09:55:37   4    job   VARCHAR2(9))
09:55:37   5  /

Type created.

09:55:38 TEST> /******** Step (2) Create the Rule Class *******/
09:55:38 TEST> BEGIN
09:55:38   2    dbms_rlmgr.create_rule_class (
09:55:38   3      rule_class =>'DeptAllocation',
09:55:38   4      event_struct =>'AddEMP',
09:55:38   5      action_cbk =>'DeptAlloc',
09:55:38   6      rlcls_prop => '<simple consumption="exclusive" ordering="rlm$rule.rlm$ruleid"/>',
09:55:38   7      actprf_spec => 'DEPTNO NUMBER(2), MGR NUMBER(4)');
09:55:38   8  END;
09:55:38   9  /

PL/SQL procedure successfully completed.

09:56:20 TEST> /**** Step (3) Modify the Callback Procedure ****/
09:56:20 TEST> CREATE OR REPLACE PROCEDURE DEPTALLOC (
09:56:20   2    rlm$event AddEmp,
09:56:20   3    rlm$rule DeptAllocation%ROWTYPE) is
09:56:20   4  BEGIN
09:56:20   5    INSERT INTO emp
09:56:20   6      (empno, ename, job, deptno, mgr, hiredate)
09:56:20   7    VALUES (
09:56:20   8      rlm$event.empno, rlm$event.ename,
09:56:20   9      rlm$event.job, rlm$rule.deptno,
09:56:20  10      rlm$rule.mgr, sysdate);
09:56:20  11  END;
09:56:20  12  /

Procedure created.

09:56:20 TEST> /**** Step (4) main procedure HIRE_EMP ****/
09:56:20 TEST> CREATE OR REPLACE PROCEDURE hire_emp
09:56:20   2    (ip_empno EMP.EMPNO%TYPE,
09:56:20   3     ip_ename EMP.ENAME%TYPE,
09:56:20   4     ip_job   EMP.JOB%TYPE) AS
09:56:20   5  BEGIN
09:56:20   6    dbms_rlmgr.process_rules (
09:56:20   7      rule_class => 'DeptAllocation',
09:56:20   8      event_inst => AddEmp.getvarchar (ip_empno, ip_ename, ip_job));
09:56:20   9  END;
09:56:20  10  /

Procedure created.

09:56:21 TEST> /**** Function ****/
09:56:23 TEST> CREATE OR REPLACE FUNCTION cnt_emp (ip_job VARCHAR2)
09:56:23   2    RETURN NUMBER
09:56:23   3  AS
09:56:23   4    lv_cnt  NUMBER;
09:56:23   5  BEGIN
09:56:23   6    SELECT COUNT(*) INTO lv_cnt
09:56:23   7    FROM emp
09:56:23   8    WHERE job=ip_job;
09:56:23   9    RETURN lv_cnt;
09:56:23  10  END;
09:56:23  11  /

Function created.

09:56:23 TEST> /**** Adding the function to Event Struct ****/
09:56:23 TEST> EXEC DBMS_RLMGR.ADD_FUNCTIONS('AddEmp','cnt_emp');

PL/SQL procedure successfully completed.

09:56:24 TEST> /**** Creating Rules ****/
09:56:31 TEST>
09:56:31 TEST> /*--5th Salesman to Research Department(20)--*/
09:56:31 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:31   2  VALUES ('Rule1', 20, 7566, 'job=''SALESMAN'' AND REMAINDER(cnt_emp(''SALESMAN'')+1,5)=0');

1 row created.

09:56:33 TEST> /*--3rd Analyst to Account Department(10)--*/
09:56:33 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33   2  VALUES ('Rule2', 10, 7782, 'job=''ANALYST'' AND REMAINDER(cnt_emp(''ANALYST'')+1,3)=0');

1 row created.

09:56:33 TEST> /*--Other Salesman to Operations Department(30)--*/
09:56:33 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33   2  VALUES ('Rule3', 30, 7698, 'job=''SALESMAN'' AND REMAINDER(cnt_emp(''SALESMAN'')+1,5)<>0');

1 row created.

09:56:33 TEST> /*--Other Analyst to Research Department(20)--*/
09:56:33 TEST> INSERT INTO deptallocation (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33   2  VALUES ('Rule4', 20, 7566, 'job=''ANALYST'' AND REMAINDER(cnt_emp(''ANALYST'')+1,3)<>0');

1 row created.

09:56:33 TEST> /*--All others to Account Department(10)--*/
09:56:33 TEST> INSERT INTO DEPTALLOCATION (rlm$ruleid, DEPTNO, MGR, rlm$rulecond)
09:56:33   2  VALUES ('Rule5', 10, 7782,'1=1');

1 row created.

09:56:33 TEST> COMMIT;

Commit complete.

09:56:37 TEST> /**** Sample Execution ****/
09:56:53 TEST>
09:56:53 TEST> EXEC hire_emp(9990, 'MANOJ1', 'SALESMAN');

PL/SQL procedure successfully completed.

09:56:54 TEST> EXEC hire_emp(9991, 'TAPAS1', 'ANALYST');

PL/SQL procedure successfully completed.

09:56:54 TEST> EXEC hire_emp(9992, 'ARUP1', 'SALESMAN');

PL/SQL procedure successfully completed.

09:56:54 TEST> EXEC hire_emp(9993, 'SUBRATA1','ANALYST')

PL/SQL procedure successfully completed.

09:56:54 TEST> EXEC hire_emp(9994, 'MADHU1','CLERK');

PL/SQL procedure successfully completed.

09:56:54 TEST> exec dbms_lock.sleep(60);


SQL> -- session 2
SQL> connect test/test
Connected.
TEST> set time on
09:57:03 TEST> EXEC hire_emp(9995, 'MANOJ2', 'SALESMAN');

PL/SQL procedure successfully completed.

09:57:04 TEST> EXEC hire_emp(9996, 'TAPAS2', 'ANALYST');

PL/SQL procedure successfully completed.

09:57:04 TEST> EXEC hire_emp(9997, 'ARUP2', 'SALESMAN');

PL/SQL procedure successfully completed.

09:57:04 TEST> EXEC hire_emp(9998, 'SUBRATA2','ANALYST')

PL/SQL procedure successfully completed.

09:57:04 TEST> EXEC hire_emp(9999, 'MADHU2','CLERK');

PL/SQL procedure successfully completed.

09:57:04 TEST> COMMIT;

Commit complete.

09:57:04 TEST>


09:57:54 TEST> -- session 1
09:57:54 TEST> COMMIT;

Commit complete.

09:57:54 TEST> SELECT empno, ename, deptno, mgr, job
09:57:54   2  FROM emp
09:57:54   3  order by empno;
     EMPNO ENAME          DEPTNO        MGR JOB
---------- ---------- ---------- ---------- ---------
      7369 SMITH              20       7902 CLERK
      7499 ALLEN              30       7698 SALESMAN
      7521 WARD               30       7698 SALESMAN
      7566 JONES              20       7839 MANAGER
      7654 MARTIN             30       7698 SALESMAN
      7698 BLAKE              30       7839 MANAGER
      7782 CLARK              10       7839 MANAGER
      7788 SCOTT              20       7566 ANALYST
      7839 KING               10            PRESIDENT
      7844 TURNER             30       7698 SALESMAN
      7876 ADAMS              20       7788 CLERK
      7900 JAMES              30       7698 CLERK
      7902 FORD               20       7566 ANALYST
      7934 MILLER             10       7782 CLERK
      9990 MANOJ1             20       7566 SALESMAN
      9991 TAPAS1             10       7782 ANALYST
      9992 ARUP1              30       7698 SALESMAN
      9993 SUBRATA1           20       7566 ANALYST
      9994 MADHU1             10       7782 CLERK
      9995 MANOJ2             20       7566 SALESMAN
      9996 TAPAS2             10       7782 ANALYST
      9997 ARUP2              30       7698 SALESMAN
      9998 SUBRATA2           20       7566 ANALYST
      9999 MADHU2             10       7782 CLERK

24 rows selected.

If we look at SALEMAN only, we see:
09:57:54 TEST> SELECT empno, ename, deptno, mgr, job
09:59:32   2  FROM emp
09:59:32   3  where job='SALESMAN'
09:59:32   4  order by empno;
     EMPNO ENAME          DEPTNO        MGR JOB
---------- ---------- ---------- ---------- ---------
      7499 ALLEN              30       7698 SALESMAN
      7521 WARD               30       7698 SALESMAN
      7654 MARTIN             30       7698 SALESMAN
      7844 TURNER             30       7698 SALESMAN
      9990 MANOJ1             20       7566 SALESMAN
      9992 ARUP1              30       7698 SALESMAN
      9995 MANOJ2             20       7566 SALESMAN
      9997 ARUP2              30       7698 SALESMAN

8 rows selected.

So the fifth row is indeed in department 20 but also the 7th one which should be in dept 30.
Same thing with ANALYST:
09:59:32 TEST> SELECT empno, ename, deptno, mgr, job
09:59:32   2  FROM emp
09:59:32   3  where job='ANALYST'
09:59:32   4  order by empno;
     EMPNO ENAME          DEPTNO        MGR JOB
---------- ---------- ---------- ---------- ---------
      7788 SCOTT              20       7566 ANALYST
      7902 FORD               20       7566 ANALYST
      9991 TAPAS1             10       7782 ANALYST
      9993 SUBRATA1           20       7566 ANALYST
      9996 TAPAS2             10       7782 ANALYST
      9998 SUBRATA2           20       7566 ANALYST

The third one is in dept 10 as required but the fifth one too which should in dept 20 and the 6th one is in dept 20 when it should be in dept 10.

The reason is clear: the count(*) in the function executed by session 2 can't see the rows inserted by session 1 and so session 2 acts in exactly the same way than session 1.

I do not mean Rule Manager does not work for this case or others, actually I don't know, I mean its usage is not so simple the article wants to show it.

[Updated on: Wed, 21 January 2015 10:29]

Report message to a moderator

Re: How to control the inserting the new records when it cross the limits [message #631909 is a reply to message #631870] Wed, 21 January 2015 10:12 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
why not an after trigger

custom@test>select count(*) from whb;

COUNT(*)
----------
30

custom@test>insert into whb values(1);
insert into whb values(1)
*
ERROR at line 1:
ORA-20000: too many rows
ORA-06512: at "CUSTOM.WHB_TEST", line 9
ORA-04088: error during execution of trigger 'CUSTOM.WHB_TEST'

CREATE OR REPLACE TRIGGER CUSTOM.WHB_TEST
AFTER INSERT
ON CUSTOM.WHB
REFERENCING NEW AS New OLD AS Old
DECLARE
tmpVar NUMBER;
BEGIN
select count(*)
into tmpvar
from whb;

if tmpvar > 30 then
raise_application_error(-20000, 'too many rows');
end if;
END whb_test;
/





Re: How to control the inserting the new records when it cross the limits [message #631910 is a reply to message #631909] Wed, 21 January 2015 10:17 Go to previous message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
It will not work in multisession environment. If table has 29 rows and 2 sessions are inserting table will end up with 31 rows.

SY.
Previous Topic: display group name
Next Topic: Want to split value based on space if no space want to show null in second column
Goto Forum:
  


Current Time: Tue Mar 19 02:15:09 CDT 2024