Q

ORA-06550 error produced from function within package

I have this function within a package that keeps on producing the error:

ORA-06550: line 1, column 217:nPLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:nn begin case declare end exception exit for goto if loop modn null pragma raise"...

Here is the source code:

 
FUNCTION add_card_server_slots(cards_id NUMBER,
 
                                server_slot_id NUMBER,

                                server_slot_ver NUMBER,
 
                                server_page_ver NUMBER,
 
                                server_page_name VARCHAR2,
 
                                server_conds_id IN my_numeric_table) 

RETURN NUMBER IS csslots_id_assigned NUMBER; new_server_id NUMBER; len
NUMBER(8) := 0; s_conds_id VARCHAR2(100);
 
CURSOR csslots_assigned_cur IS
 
         SELECT id FROM card_server_slots WHERE card_id=cards_id AND slot_id=server_slot_id; BEGIN
 
  
 
         -- No need to throw exception if a record exist for this card, just update the current record.
 
         IF NOT csslots_assigned_cur%ISOPEN
 
         THEN
 
         OPEN csslots_assigned_cur;
 
         END IF;
 
         FETCH csslots_assigned_cur INTO csslots_id_assigned;
 
         IF csslots_assigned_cur%NOTFOUND
 
         THEN
 
                 -- Do the actual insertion in card_server_slots.
 
                 SELECT card_server_slots_id_SEQ.nextval INTO 
new_server_id FROM dual;
 
                 INSERT INTO card_server_slots
(id,card_id,slot_id,slot_version,page_name,page_version)
 
                 VALUES

(new_server_id,cards_id,server_slot_id,server_slot_ver,server_page_name,
server_page_ver);
 
('Passed 1st statement');
 
                 FOR i IN 1..server_conds_id.last LOOP
 
                        INSERT INTO card_sslots_conds (card_sslots_id, conds_id)
 
                        VALUES (new_server_id,server_conds_id(i));
 
                 END LOOP;
 
         ELSE
 
                 -- Update card_server_slots table.
 
                 --new_server_id:=csslots_id_assigned;
 
                 UPDATE card_server_slots SET 
 slot_version=server_slot_ver, page_version=server_page_ver,
 
                 page_name=server_page_name WHERE card_id=cards_id AND 
id=csslots_id_assigned;
 
                 FOR i IN 1..server_conds_id.last LOOP
 
                    IF len=0
 
                    THEN
 
                         len := length(TO_CHAR(server_conds_id(i)));
 
                         s_conds_id :=
 RPAD(TO_CHAR(server_conds_id(i)),len);
 
                    ELSE
 
                         len :=
 length(s_conds_id||TO_CHAR(server_conds_id(i)));
 
                         s_conds_id :=
 RPAD(s_conds_id,len,TO_CHAR(server_conds_id(i)));
 
                    END IF;
 
                    IF i!=server_conds_id.last
 
                    THEN
 
                         s_conds_id:= RPAD(s_conds_id,1,',');
 
                    END IF;
 
                 END LOOP;
 
         EXECUTE IMMEDIATE 'DELETE FROM card_sslots_conds WHERE
 card_sslots_id=:1 AND conds_id NOT IN (' || s_conds_id || ')' 
 
 USING new_server_id;
 
 ('Passed 2nd looping statement');
 
                 END LOOP;
 
         END IF;
 
         CLOSE csslots_assigned_cur;
 
         RETURN new_server_id;
 
    END add_card_server_slots;
 
 END update_card_pkg; -- end PACKAGE BODY update_card_pkg /

Any help would be appreciated. Thanks.


The code doesn't appear to be syntatically correct. In the code, you have two lines that appear as follows:

('Passed 1st statement');

and

('Passed 2nd looping statement');

These two statements will throw an error. They appear to be comments. If that is the case, then the line needs to start with '--' or the comment needs to be bracketed by '/*' and '*/' as follows:

-- ('Passed 2nd looping statement');

or

/* ('Passed 2nd looping statement');  */

Finally, at the end of the code are the following lines:

   END add_card_server_slots;

END update_card_pkg; -- end PACKAGE BODY update_card_pkg /

The first END corresponds to the CREATE FUNCTION and BEGIN statements. However, the second END has no corresponding BEGIN. It appears to be from a package body, yet you have included no package body.


This was first published in October 2004

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close