ansaurus

Question

Answer 1

A:

wallyk 2010-08-31 06:44:06

Likewise, I am just trying to execute the script one at a time, but it gives me an error too: Error report:SQL Error: ORA-00934: group function is not allowed here00934. 00000 - "group function is not allowed here"insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id, User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState, PreviousState, WorkflowTime, UserStamp, TimeStamp) values (max(StayWorkflow_Id)+1, 66311, 114593, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

muddu83 2010-08-31 06:53:56

muddu83 is not using a sequence. Even though, I'd like to mention that *alter sequence XY increment by 4* does NOT increment the sequence by 4 but changes the increment size for all future NEXTVAL calls. This can go badly wrong if you use a large number.

Codo 2010-08-31 07:42:20

Answer 2

+1 A:

As wallyk has pointed out, the way you are generating your IDs is fishy. Usually, it goes like this:

create sequence StayWorkflow_Id nocache;  /* you only do that once */

insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
        User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
        PreviousState, WorkflowTime, UserStamp, TimeStamp)
    values (StayWorkflow_Id.nextval, 98485, 129844, 1, '', '', 11, 7, 7,
            to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));


insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
        User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
        PreviousState, WorkflowTime, UserStamp, TimeStamp)
    values (StayWorkflow_Id.nextval, 66311, 114593, 1, '', '', 11, 7, 7,
            to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));


 ...

just use StayWorkflow_Id.nextval to get a new unique id each time, WITHOUT BEING AFRAID OF RACE CONDITIONS

ammoQ 2010-08-31 07:05:55

Thanks! We have a seperate SYSQLSEQUENCE table containing all the seq names and next value listed and I want the stayworkflow_id to take the next value from there and then update the sequence table with the next value and repeat this cycle for the next inserts...

muddu83 2010-08-31 07:27:36

But you aren't doing that, you're picking the highest value from existing table rows, not from your dedicated table. If rows have been deleted you could end up with a lower number than the sequence table holds. You could potentially avoid the race condition by selecting the next value from the sequence table with a `for update` clause, but as with your dates-stored-as-strings, you're making life harder than it needs to be.

Alex Poole 2010-08-31 08:19:01

Forgot to mention this on a previous question... aren't you losing some important information by using `HH` for the date format instead of `HH24`?

Alex Poole 2010-08-31 08:20:09

muddu83: Running your own Sequences in a table is a VERY VERY bad idea, especially in Oracle. Until you commit, other sessions do not see the new rows / the new values in SYSQLSEQUENCE, so you might end up with duplicate IDs very easily! But it doesn stop here: until the first session to insert some ID commits or rollback, the second session locks, because it has to wait whether or not the first session commits before it can report a unique constraint violation (you have unique constraint, do you?!?)

ammoQ 2010-08-31 09:07:13

Answer 3

+2 A:

Your code is not really PL/SQL, but mainly a SQL*plus script (in particular VARIABLE is neither PL/SQL nor SQL).

Why don't you make it pure PL/SQL:

DECLARE
  StayWorkflow_Id_max number;

BEGIN
  SELECT max(StayWorkflow_Id)+1 into StayWorkflow_Id_max  from MVStayWorkflow;

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max, 98485, 129844, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max+1, 66311, 114593, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max+2, 83742, 110157, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  insert into MVStayWorkflow (StayWorkflow_Id, Stay_Id, Passage_Id,
            User_Id, RespUnit_Id, Resource_Id, WorkflowAction, CurrentState,
            PreviousState, WorkflowTime, UserStamp, TimeStamp)
        values (StayWorkflow_Id_max+3, 74421, 98685, 1, '', '', 11, 7, 7, to_char(sysdate, 'YYYYMMDD HH:MI:SS'), 1, to_char(sysdate, 'YYYYMMDD HH:MI:SS'));

  update SYSQLSequence set nextValue = StayWorkflow_Id_max+4 where name = '_MVStayWorkflow_auto_';

  commit;
END;
/

Codo 2010-08-31 07:47:20

Thanks a ton, Codo! It works smoothly!!! :-)

muddu83 2010-09-01 08:04:01

ansaurus

tags:

views:

answers:

Whats wrong with this PL/SQL script?

related questions