Need help with syntax for a procedure <-- complete noob

[Guest_imported]

I am having some difficulties getting a package to compile and I have to tell you that I am a complete noob to Oracle PL/SQL. I'm buying a book ASAP... any suggestions on that?

Here's the bastard code: Oracle 8i

CREATE OR REPLACE PACKAGE BODY FNDD_DBO.UPDATE_STATE_CODE
IS
PROCEDURE UPDATE_SAP_VW(lv_go IN NUMBER)
AS
CURSOR cur_sapvw IS
SELECT * FROM FNDD_DBO.VWSAP_TRANSFORM FOR UPDATE;

lv_sap_allowed number;

BEGIN
FOR cur_sapvw_rec IN cur_sapvw LOOP
SELECT FNDD_DBO.SAP_CTRY_STATE_XREF.STATE_ALLOWED
INTO lv_sap_allowed
FROM FNDD_DBO.SAP_CTRY_STATE_XREF
WHERE FNDD_DBO.SAP_CTRY_STATE_XREF.CTRY_CODE =
cur_sapvw_rec.country_cd;

IF lv_sap_allowed =< 0 THEN
UPDATE VWSAP_TRANSFORM SET VWSAP_TRANSFORM.COUNTRY_CD := NULL
WHERE CURRENT OF cur_sapvw;
END IF;

COMMIT;

END LOOP;
END;
END UPDATE_SAP_VW;
END;


Get These errors:
Encountered Symbol &quot;<&quot; when expecting one of the following....

Encountered symbol &quot;=&quot; when expecting one of the following...

Encountered the symbol &quot;END&quot;


Any help would be appreciated.

 

[karluk]

You may not be too far wrong. The line &quot;IF lv_sap_allowed =< 0 THEN&quot; should read &quot;IF lv_sap_allowed <= 0 THEN&quot;. Oracle can't handle the equal sign coming first.

Of course you may have other errors as well. Try fixing this one and see how far you get.

 

[Guest_imported]

That was the key to getting me through the rest of it. Now here is another question. I need to change the data in a view with this procedure without modifying the data from which the view is derived. Would procedure do this? So far when I execute it it doesn't appear to work. I know it's a me thing, I just don't know PL/SQL..

 

[Guest_imported]

I get this error specifically from this procedure:
***********************
ERRORS ENCOUNTERED ***********************
ERROR : ORA-01002: fetch out of sequence

 

[karluk]

I think the ORA-01002 error is caused by the commit statement in your loop. I don't think you can do a commit while you are in the middle of processing an update cursor.

 

[Guest_imported]

Ah, yeah. Found that off the net as well. What works actually is commiting a bulk of records instead of doing 1 record at a time.

This works..
-------------

BEGIN
         FOR cur_sapvw_rec IN cur_sapvw LOOP
              iCNT := iCNT + 1
              SELECT FNDD_DBO.SAP_CTRY_STATE_XREF.STATE_ALLOWED 
              INTO lv_sap_allowed
              FROM FNDD_DBO.SAP_CTRY_STATE_XREF
              WHERE FNDD_DBO.SAP_CTRY_STATE_XREF.CTRY_CODE = cur_sapvw_rec.country_cd;

         IF lv_sap_allowed =< 0 THEN
              UPDATE VWSAP_TRANSFORM SET VWSAP_TRANSFORM.COUNTRY_CD := NULL
              WHERE CURRENT OF cur_sapvw;
         END IF;
         
         IF iCNT > 500 THEN
              iCNT := 0;
              COMMIT;
         END IF 

         END LOOP;

 

[sem]

This works because you probably have less than 500 records. Please do not ignore Karluk's posting. Commit releases all locks.

 

[karluk]

I can see some value to setting up a loop counter to do periodic commits. That way you can avoid potential rollback segment problems and &quot;snapshot too old&quot; errors. However, if you want to make this work I think you will have to close and reopen the cursor after every commit. With the code that you have, that probably means setting up nested loops. The outer loop would manage the commits and the inner loop would process a batch of 500 updates.

If you know that your cursor won't be processing too much data I would avoid the complexity and just go with a single loop and either no commit at all or commit after exiting the loop.

 

[Guest_imported]

There are closer to 8,000 records that will be processed. So I wanted to avoid the rollback problems. I need to learn this. I primarily code in C++ and VB and I don't know PL/SQL.. that's sad. Oh well I am on my way... got to make some changes and I'll repost for tweaks.

 

[Guest_imported]

Question: If I do close and reopen the cursor, would the index position be managed? Or would I have to move forward through the cursor to get to my last processed record?