CODEWITHSUNDEEP
sqloracle-databaseplsql
Hendrik Breezy
Hendrik Breezy

Reputation: 128

Stored Procedure Oracle SQL to process large queries

I am trying to implement a stored procedure which updates my table (over 10k rows) but only a certain amount of rows rownum

Current procedure:

       create or replace PROCEDURE P_UPDATETOANON AS 
BEGIN
         UPDATE PERSONENVERKEHR_VERTRIEBSSYS15.HIST_TEST
         SET TS_RESPONSIBLE = 'test'  
         WHERE TS_RESPONSIBLE != 'test' 
         AND length(TS_RESPONSIBLE) >  3;

END P_UPDATETOANON;

Error(3,10): PL/SQL: SQL Statement ignored
Error(3,48): PL/SQL: ORA-01031: insufficient privileges

How do I implement to only update a certain record of data?

Upvotes: 0

Views: 165

Answers (1)

Aleksej
Aleksej

Reputation: 22949

If you need to update only a given amount of rows, without any logic to decide which ones, you may simply need:

UPDATE PERSONENVERKEHR_VERTRIEBSSYS15.HIST_TEST
  SET TS_RESPONSIBLE = 'test'  
WHERE TS_RESPONSIBLE  != 'test'
  AND length(TS_RESPONSIBLE) > 3
  AND rownum < 100 /* for example */

It seems even that your user has no rights to update the table; you first need 

grant update on PERSONENVERKEHR_VERTRIEBSSYS15.HIST_TEST to MQCDBA

given by the owner of the table or other granted user.

Upvotes: 1

Related Questions