Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Need help with first Procedure

Need help with first Procedure

From: Kelly <kgrigg_at_acxiom.com>
Date: 1997/11/12
Message-ID: <64d8lj$35o_002@conway.acxiom.com>#1/1

Hello All,
 I am working up some PL/SQL to correct a mistake on a table I loaded. I decided to make it as a stored procedure just to get to know how to create and call them. When I try to run the script to create it I get the error:

LINE/COL ERROR

-------- -----------------------------------------------------------------
17/3     PL/SQL: SQL Statement ignored
18/7     PLS-00417: unable to resolve "REC_ADD_ASSOC.SECTION_NUMBER" as a
         column

I have looked at my books and examples I have had from classes, but, can' t figure out the problem. It looks to be something in my cursor loop, I'm guessing the record_name variable...one book says not to declare it since its scope is limited to the loop, but, Oracle: The complete reference has it declared. I have tried it both ways, but, I get the same error. Could anyone spot what I'm doing wrong? (The spacing is different in my example, I compressed it a little for the post).

Here is the code:

create or replace procedure correct_add_assoc (x_book_number IN number) IS

        cursor c_add_assoc is
                SELECT 
                a.book_book_number,a.book_book_pub_date,a.section_number,
                a.page_number,a.listing_number,a.individual_type
                FROM address_associations a
                WHERE a.book_book_number = x_book_number
                FOR UPDATE OF a.section_number,a.page_number,a.listing_number;

                v_section address_associations.section_number%TYPE;
                v_page address_associations.page_number%TYPE;
                 v_listing address_associations.listing_number%TYPE;

BEGIN
        FOR rec_add_assoc IN c_add_assoc LOOP
                v_section := rec_add_assoc.section_number;
                v_page := rec_add_assoc.page_number;
                v_listing := rec_add_assoc.listing_number;

                UPDATE address_associations
                SET rec_add_assoc.section_number = v_page, 
                rec_add_assoc.page_number = v_listing,
                rec_add_assoc.listing_number = v_section
                WHERE CURRENT OF c_add_assoc;

        END LOOP;
commit;
        

END;
/

Any help greatly appreciated!!

Kelly
kgrigg_at_acxiom.com

ps. Please cc by mail too, our news feed here is not reliable...

"If you've had half as much fun as me...

    ....Then I've had twice as much fun as you!!"

kgrigg_at_acxiom.com
cayenne_at_cei.net

Visit my Website at: http://www.cei.net/~cayenne/index.html Received on Wed Nov 12 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US