I have to update a coulmn (CNTL_ID) in Table A using the output of Stored Proc.
CALL GETNEXTLOADID(NEXTID) gives us the next available ID.
(CALL GETNEXTLOADID(NEXTID)) T(NID)
SET CNTL_ID = T.NID
But it is not working.
Please correct this query or any other way to do this.
the definition of Stored Proc is:
REPLACE PROCEDURE GETNEXTLOADID(OUT NEXTID INTEGER)
LOCKING TABLE tbl1 FOR WRITE
SET CNTRL_ID = CNTRL_ID + 1;
SELECT CNTRL_ID INTO :NEXTID FROM tbl1;
Can you please explain? The only issue is to use the output of Store Proc to update a column in another table.
Its not going to work.
In your example it would not be needed as you could also
FROM TABLEA a,
SET CNTL_ID = T.CNTRL_ID
If tbl1 can be updated from different sessions at the same time then do the update and insert select the value in a volatile or global temp table within the SP.
Use the volatile or global temp table in your update in this case.