ORA-06550 and PLS-00103 error

75 pts.
Tags:
ORA-06550
Oracle
Oracle error messages
declare  rent_paid_date DATE;  rent_due_date DATE; create or replace procedure due_date(in_rn_ssn NUMBER, in_home_id NUMBER, in_rent_paid_date DATE, in_rent_due_date DATE, in_rental_fees NUMBER) as  fees rent_collector.late_fees%TYPE;  new_fees number := 0; begin  select sum(late_fees) into fees  from rent_collector  where rn_ssn = in_rn_ssn and  home_id = in_home_id and  rent_paid_date = in_rent_paid_date and  rent_due_date = in_rent_due_date and  rental_fees = in_rental_fees;  if rent_paid_date >= rent_due_date then   new_fees := rental_fees + (rental_fees*(10/100));   insert into rent_collector   values(in_rn_ssn, in_home_id, in_rent_paid_date, in_rent_due_date, in_rental_fees , new_fees);   insert into activity   values(fred_num.nextval, 'F', 'Added to Inventory');  else rent_paid_date < rent_due_date then   new_fees := rental_fees + 0;   insert into rent_collector   values(in_rn_ssn, in_home_id, in_rent_paid_date, in_rent_due_date, in_rental_fees , new_fees);   insert into activity   values(fred_num.nextval, 'F', 'Added to Inventory');  end if; end; / 

Answer Wiki

Thanks. We'll let you know when a new response is added.

There are several errors in your code.

First, if you are trying to create a stored procedure, remove everything that is before the CREATE OR REPLACE PROCEDURE command.

Second, every variable that is going to be referenced in the stored procedure code needs to be declared inside the procedure (as a variable or as a parameter), so using variable names that were declared outside the procedure (with the DECLARE command) will cause an error.

I think there are more errors, but they are difficult to identify because of the bad formatting of the code.

If you need further help, please post the complete error messages you get and post your code using the editor’s CODE tool.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kuranharuka
    Sorry, it doesn't come up like it suppose to be Here is what I'm trying to get help:
    declare
    	rent_paid_date DATE;
    	rent_due_date DATE;
    create or replace procedure due_date(in_rn_ssn NUMBER, in_home_id NUMBER, in_rent_paid_date DATE, in_rent_due_date DATE, in_rental_fees NUMBER) as
    	fees rent_collector.late_fees%TYPE;
    	new_fees number := 0;
    begin
    	select sum(late_fees) into fees
    	from rent_collector
    	where rn_ssn = in_rn_ssn and
    	home_id = in_home_id and
    	rent_paid_date = in_rent_paid_date and
    	rent_due_date = in_rent_due_date and
    	rental_fees = in_rental_fees;
    	if rent_paid_date >= rent_due_date then 
    		new_fees := rental_fees + (rental_fees*(10/100));
    		insert into rent_collector
    		values(in_rn_ssn, in_home_id, in_rent_paid_date, in_rent_due_date, in_rental_fees , new_fees);
    		insert into activity
    		values(fred_num.nextval, 'F', 'Added to Inventory');
    	else rent_paid_date < rent_due_date then
    		new_fees := rental_fees + 0;
    		insert into rent_collector
    		values(in_rn_ssn, in_home_id, in_rent_paid_date, in_rent_due_date, in_rental_fees , new_fees);
    		insert into activity
    		values(fred_num.nextval, 'F', 'Added to Inventory');
    	end if;
    end;
    /	
    
    create or replace procedure due_date(in_rn_ssn NUMBER, in_home_id NUMBER, in_rent_paid_date DATE, in
    *
    ERROR at line 4:
    ORA-06550: line 4, column 1:
    PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
    begin function package pragma procedure subtype type use
    <an identifier> <a double-quoted delimited-identifier> form
    current cursor
    
    75 pointsBadges:
    report
  • carlosdl
    Sorry, didn't see your last post until I submitted mine. It seems that this ELSE should be ELSIF:
    else rent_paid_date < rent_due_date then
    Let us know if you get more errors.
    69,920 pointsBadges:
    report
  • Kuranharuka
    I still get some error. I'm really bad at this:
    create or replace procedure due_date(in_rn_ssn NUMBER, in_home_id NUMBER, in_rent_paid_date DATE, in_rent_due_date DATE, in_rental_fees NUMBER) as
    	fees rent_collector.late_fees%TYPE;
    	new_fees number := 0;
    	rent_paid_date DATE;
    	rent_due_date DATE;
    begin
    	select sum(late_fees) into fees
    	from rent_collector
    	where rn_ssn = in_rn_ssn and
    	home_id = in_home_id and
    	rent_paid_date = in_rent_paid_date and
    	rent_due_date = in_rent_due_date and
    	rental_fees = in_rental_fees;
    	if rent_paid_date >= rent_due_date then 
    		new_fees := rental_fees + (rental_fees*(10/100));
    		insert into rent_collector
    		values(in_rn_ssn, in_home_id, in_rent_paid_date, in_rent_due_date, in_rental_fees , new_fees);
    		insert into activity
    		values(fred_num.nextval, 'F', 'Added to Inventory');
    	elsif rent_paid_date < rent_due_date then
    		new_fees := rental_fees + 0;
    		insert into rent_collector
    		values(in_rn_ssn, in_home_id, in_rent_paid_date, in_rent_due_date, in_rental_fees , new_fees);
    		insert into activity
    		values(fred_num.nextval, 'F', 'Added to Inventory');
    	end if;
    end;
    /
    
    SQL> select * from user_errors where name = 'DUE_DATE'; NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- ATTRIBUTE MESSAGE_NUMBER --------- -------------- DUE_DATE PROCEDURE 1 15 15 PLS-00201: identifier 'RENTAL_FEES' must be declared ERROR 201 DUE_DATE PROCEDURE 2 15 3 PL/SQL: Statement ignored ERROR 0 NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- ATTRIBUTE MESSAGE_NUMBER --------- -------------- DUE_DATE PROCEDURE 3 21 15 PLS-00201: identifier 'RENTAL_FEES' must be declared ERROR 201 DUE_DATE PROCEDURE 4 21 3 PL/SQL: Statement ignored NAME TYPE SEQUENCE LINE POSITION ------------------------------ ------------ ---------- ---------- ---------- TEXT -------------------------------------------------------------------------------- ATTRIBUTE MESSAGE_NUMBER --------- -------------- ERROR 0
    75 pointsBadges:
    report
  • carlosdl
    The error message is pretty explanatory: You don't have a local variable or parameter named 'RENTAL_FEES'. You have to declare any variable you want to use in your code.
    69,920 pointsBadges:
    report
  • Kuranharuka
    I fix it!! Thank you!
    75 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following