Home » SQL & PL/SQL » SQL & PL/SQL » I got a problem while writing a simple procedure (oracle 10.2.0.4.0)
I got a problem while writing a simple procedure [message #434376] Wed, 09 December 2009 01:07 Go to next message
masterlive
Messages: 4
Registered: December 2009
Junior Member
set   serveroutput   on;   

/* Create table and insert the values.*/
drop table i_cargroup cascade constraints;
drop table i_car cascade constraints;
CREATE TABLE i_cargroup
        (car_group_name VARCHAR(2) constraint cargroups_pk  
         primary key,
	 Rate_per_mile NUMBER(3),
	 Rate_per_day NUMBER(5,2));

CREATE TABLE i_car
        (Registration VARCHAR2(7) constraint cars_pk primary key,
         Model_name VARCHAR2(8) constraint cars_fk1 references  
         i_model(model_name),
         Car_group_name VARCHAR2(2) constraint cars_fk2  
         references i_cargroup(car_group_name),
	 Date_bought DATE,
	 Cost NUMBER(8,2),
	 Miles_to_date NUMBER(6),
	 Miles_last_service NUMBER(6),
	 Status CHAR(1));

INSERT into i_cargroup VALUES
	('A1',110,25);
INSERT into i_cargroup VALUES
	('A2',115,24.5);
INSERT into i_cargroup VALUES
	('A3',155,36.75);
INSERT into i_cargroup VALUES
	('A4',125,33.5);
INSERT into i_cargroup VALUES
	('B1',110,25.9);
INSERT into i_cargroup VALUES
	('B2',125,33);
INSERT into i_cargroup VALUES
	('B3',135,37.75);
INSERT into i_cargroup VALUES
	('B4',135,38.5);
INSERT into i_car VALUES
	('H266MHU','ASTON V8','A1','01-APR-91',63000,2597,1000,'A');
INSERT into i_car VALUES
	('G899VDU','BMW 635','B2','23-OCT-89',45500.3,18675,12667,'A');
INSERT into i_car VALUES
	('G123RMR','BMW 750','B3','16-NOV-89',56829,42400,25366,'A');
INSERT into i_car VALUES
	('E246WFC','RR SSPIR','B4','06-JAN-88',84500,52861,48253,'H');
INSERT into i_car VALUES
	('G994PBR','FERR TR','A4','31-MAR-90',102450,10662,6004,'A');
INSERT into i_car VALUES
	('H626RPG','JAG XJS','A2','26-SEP-90',32650.3,14533,12769,'A');
INSERT into i_car VALUES
	('G551JBA','JAG XJ6','A3','02-SEP-89',45500,13788,11950,'A');
INSERT into i_car VALUES
	('H203PBR','LAMB COU','A4','10-NOV-90',130250,7025,903,'H');
INSERT into i_car VALUES
	('F651DEK','RR SSPIR','B4','10-JAN-89',92500,29610,24460,'A');
INSERT into i_car VALUES
	('H311MHG','MERC 560','B3','09-NOV-90',60800,12450,11832,'A');
INSERT into i_car VALUES
	('F111ENT','P911 TC','A3','03-SEP-88',75200,30286,24502,'A');
INSERT into i_car VALUES
	('G202XRP','P944 T','A3','14-FEB-89',48350,28604,23666,'A');
INSERT into i_car VALUES
	('J516BTA','P944 T','A3','02-SEP-91',50301,1502,NULL,'A');
INSERT into i_car VALUES
	('H935CSA','FERR TR','A4','23-JUN-91',105600,3005,998,'H');
INSERT into i_car VALUES
	('J706BEG','JAG XJ6','A3','16-SEP-91',39250.5,2110,1132,'H');
INSERT into i_car VALUES
	('H875DES','BMW 750','B3','08-APR-91',58321,8354,973,'A');
INSERT into i_car VALUES
	('H235BMA','LAMB COU','A4','10-NOV-90',130250,9611,1156,'H');
INSERT into i_car VALUES
	('J185NED','P911 TC','A3','15-OCT-91',78350,875,NULL,'A');
INSERT into i_car VALUES
	('J644TNR','MERC 560','B3','10-OCT-91',65800,1821,1201,'H');
INSERT into i_car VALUES
	('J933RCE','FERR TR','A4','30-OCT-91',111200,957,NULL,'A');

/*The procedure start*/ 
show errors;
CREATE OR REPLACE PROCEDURE P_CARGROUP(car_group_name IN i_cargroup.car_group_name%TYPE)
AS
  v_car_group_name i_cargroup.car_group_name%TYPE;  
  v_rate_per_day i_cargroup.Rate_per_day%TYPE;
  v_rate_per_mile i_cargroup.Rate_per_mile%TYPE;
  v_registration i_car.Registration%TYPE; 
  v_model_name i_car.Model_name%TYPE;
  v_date_bought i_car.Date_bought%TYPE;
  v_cost i_car.Cost%TYPE;
  v_miles_to_date i_car.Miles_to_date%TYPE;
  v_miles_last_service i_car.Miles_last_service%TYPE;
  v_status i_car.Status%TYPE;
  CURSOR cargroup_cur 
  IS
   SELECT car_group_name, Rate_per_mile, Rate_per_day 
   FROM i_cargroup
   WHERE i_cargroup.car_group_name = car_group_name;
  CURSOR car_cur(v_name i_cargroup.car_group_name%TYPE) 
  IS SELECT
   Registration,Model_name,Car_group_name,Date_bought,Cost,
   Miles_to_date,Miles_last_service, Status 
   FROM i_car
   WHERE i_cargroup.car_group_name = v_name;
BEGIN 
    EXECUTE IMMEDIATE 
    'CREATE TABLE CARGROUPSTAT(registration    
    VARCHAR(7),miles_to_date NUMBER (6))';  
    EXECUTE IMMEDIATE'DELETE FROM CARGROUPSTAT';
    OPEN cargroup_cur;
    FETCH cargroup_cur INTO v_car_group_name, v_rate_per_day,  
    v_rate_per_mile; 
    DBMS_OUTPUT.PUT_LINE('Car group name / Rate per day / Rate  
    per mile'); 
    WHILE cargroup_cur%FOUND LOOP
      IF v_car_group_name = car_group_name AND v_rate_per_day < 
      (v_rate_per_mile*10) THEN      
        DBMS_OUTPUT.PUT_LINE        
(v_car_group_name||'   '||v_rate_per_day||''||v_rate_per_mile);
      END IF;
      FETCH cargroup_cur INTO v_car_group_name, v_rate_per_day, v_rate_per_mile;
    END LOOP;
    CLOSE cargroup_cur;
  OPEN car_cur(car_group_name);
  FETCH car_cur INTO v_registration, v_model_name,     
  v_car_group_name, v_date_bought, v_cost, v_miles_to_date,   
  v_miles_last_service, v_status;
  DBMS_OUTPUT.PUT_LINE('registration / model name / car group  
  name / date bought / cost / miles to date / miles last service 
  / status');
  WHILE car_cur%FOUND LOOP
  IF v_car_group_name = car_group_name AND v_registration LIKE   
  'F%' THEN
    EXECUTE IMMEDIATE'INSERT INTO CARGROUPSTAT (registration,   
    miles_to_date)VALUES(v_registration, v_miles_to_date)';
    DBMS_OUTPUT.PUT_LINE(v_registration ||'   '||  
    v_model_name||'   '||v_car_group_name||'    
    '||v_date_bought||'   '||v_cost||'   '||v_miles_to_date||'   
    '|| v_model_name||'    '||v_miles_last_service||' 
    '||v_status);
  END IF;
 FETCH car_cur INTO v_registration, v_model_name,  
 v_car_group_name, v_date_bought, v_cost, v_miles_to_date,  
 v_miles_last_service, v_status;
END LOOP;
CLOSE car_cur;
EXECUTE IMMEDIATE'SELECT * FROM CARGROUPSTAT';

EXCEPTION
WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE ('NO DATA FOUND');

END P_CARGROUP;

EXECUTE P_CARGROUP('A3');


Errors for PROCEDURE P_CARGROUP:
LINE/COL ERROR
53/1 PLS-00103: Encountered the symbol "EXECUTE"
53/25 PLS-00103: Encountered the symbol "end-of-file" when
expecting on e of the following: begin
function package pragma procedure s ubtype
type use <an identifier> <a double-quoted
delimited-ide ntifier> form current cursor
Warning: Procedure created with compilation errors.

// I have been looking into it for more than 6 hours, but I can't find where the errors come from. Any help will be appreciate. Thank u very much.


[Edit MC: add code tags, fix INSERT errors]

[Updated on: Wed, 09 December 2009 01:12] by Moderator

Report message to a moderator

Re: I got a problem while writing a simple procedure [message #434377 is a reply to message #434376] Wed, 09 December 2009 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You know that a program that is not formatted and indented is hard to read, and we do not know with line is line 53.

also '30-OCT-91' is a string not a date as the following proce it:
SQL> select to_date('30-OCT-91') from dual;
select to_date('30-OCT-91') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected


Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Use SQL*Plus and copy and paste your session.

Regards
Michel

[Updated on: Wed, 09 December 2009 01:13]

Report message to a moderator

Re: I got a problem while writing a simple procedure [message #434378 is a reply to message #434376] Wed, 09 December 2009 01:14 Go to previous messageGo to next message
masterlive
Messages: 4
Registered: December 2009
Junior Member
I am sorry, but I have done my best to format it.
Thank u fot your time.
Re: I got a problem while writing a simple procedure [message #434379 is a reply to message #434376] Wed, 09 December 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the test case.
SQL> CREATE TABLE i_car
  2          (Registration VARCHAR2(7) constraint cars_pk primary key,
  3           Model_name VARCHAR2(8) constraint cars_fk1 references  
  4           i_model(model_name),
  5           Car_group_name VARCHAR2(2) constraint cars_fk2  
  6           references i_cargroup(car_group_name),
  7    Date_bought DATE,
  8    Cost NUMBER(8,2),
  9    Miles_to_date NUMBER(6),
 10    Miles_last_service NUMBER(6),
 11    Status CHAR(1));
         i_model(model_name),
         *
ERROR at line 4:
ORA-00942: table or view does not exist

i_model missing in your test case.

As I was tested your procedure (omitting the previous FK) I did not get the same error than you:
SQL> sho errors
Errors for PROCEDURE P_CARGROUP:
LINE/COL
----------------------------------------------------------------------
ERROR
----------------------------------------------------------------------
19/6
PL/SQL: SQL Statement ignored
23/10
PL/SQL: ORA-00904: "I_CARGROUP"."CAR_GROUP_NAME": invalid identifier

The associated SELECT is on i_car not i_cargroup.

Anyway the following previous line:
WHERE i_cargroup.car_group_name = car_group_name;

is equivalent to:
i_cargroup.car_group_name is not null

As Oracle takes "car_group_name" for the table column name not for the parameter. Change the name of parameters in the way they don't match to column name.

Regards
Michel

[Updated on: Wed, 09 December 2009 01:30]

Report message to a moderator

Re: I got a problem while writing a simple procedure [message #434380 is a reply to message #434376] Wed, 09 December 2009 01:25 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
This may be not the error from Your message, but:
EXECUTE IMMEDIATE'SELECT * FROM CARGROUPSTAT';

should be
EXECUTE IMMEDIATE 'SELECT * FROM CARGROUPSTAT' INTO myvar;

with myvar of the correct TYPE.
Re: I got a problem while writing a simple procedure [message #434381 is a reply to message #434379] Wed, 09 December 2009 01:46 Go to previous messageGo to next message
masterlive
Messages: 4
Registered: December 2009
Junior Member
Thank you. I have done what you told me, I changed the parameter car_group_name to car__group_name. But the error changed to be:
PLS-00103: Encountered the symbol "NO" when expecting one of the following: * & = - + ; < / > at in is mod remainder not rem return returning <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between into using || multiset b ulk member SUBMULTISET_
Re: I got a problem while writing a simple procedure [message #434386 is a reply to message #434381] Wed, 09 December 2009 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many errors have been reported to you.
Did you fix all of them?
Did you try to fix the new ones that appear?

Without the new code we can't help more.
If you post it, post it formatted and correctly indented.

Regards
Michel

Re: I got a problem while writing a simple procedure [message #434456 is a reply to message #434376] Wed, 09 December 2009 05:51 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
If you are fixing anything:

Why do you need to use dynamic SQL to create and access CARGROUPSTAT table?

It looks like it doesn't belong here.

HTH
Re: I got a problem while writing a simple procedure [message #434464 is a reply to message #434456] Wed, 09 December 2009 06:00 Go to previous messageGo to next message
masterlive
Messages: 4
Registered: December 2009
Junior Member
It's a assignment and assessment of my course. The assignment requirement is below:


Write a single PL/SQL STORED PROCEDURE that retrieves data for cars in a particular car group and performs the specified operations. The detailed specification is as follows:

1.]Name the stored procedure P_CARGROUP and pass the following parameter into the stored procedure: car group name (5 marks for the header)
When you run the procedure, you should use the following car group: A3

2.Retrieve all rows and all columns from the I_CARGROUP table that belong to the specified car group (the value in the parameter passed into the stored procedure). Use an explicit cursor. Processing should only proceed if the rate per day is less than the rate per mile multiplied by ten. This task should not be done in the DECLARE section of the program and should not be part of a select statement. (5 marks for the cursor and 5 marks for the decision logic).

3.Display the car group name, rate per day and rate per mile. (5 marks)

4.Using a second explicit cursor, retrieve comprehensive details for each car that belongs to the car group specified in the input parameter. A formal parameter should be passed to the second cursor using a value that is retrieved by the first cursor. (10 marks for the cursor and parameter)

5.All cars whose registration begins with the letter F should be excluded. This task should not be done in the DECLARE section of the program and should not be part of a select statement. (5 marks)

6.Into a table name CARGROUPSTAT, insert the registration and miles to date for all cars selected. You should create this table and include the create statement in your submission. Delete all current rows from the table before you begin your insert and also add the current date to each row you insert. (5 marks)

7.Include an exception handler which: fires when no data is found and displays the message NO DATA FOUND; and, fires when any other error is encountered and displays the message FATAL ERROR ENCOUNTERED. (5 marks)

8.Write a select statement to display the contents of the CARGROUPSTAT table. (1 mark)

9.Submit all your code as well as the output from the display statement and the select statement on the CARGROUPSTAT table. (9 marks for the content of the CARGROUPSTAT table)


// That's why I have to create the table in the procedure and use dynamic SQL.
Re: I got a problem while writing a simple procedure [message #434479 is a reply to message #434464] Wed, 09 December 2009 06:19 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
That's why I have to create the table in the procedure and use dynamic SQL.

I would say that you have mis-read the requirement
Quote:
.Into a table name CARGROUPSTAT, insert the registration and miles to date for all cars selected. You should create this table and include the create statement in your submission. Delete all current rows from the table before you begin your insert and also add the current date to each row you insert. (5 marks)

It does not say that you should create the table inside the procedure, it says that the procedure should insert into the table and that you should include the create table statement in your submission (i.e. just include a standard create table statement to build the table but not inside the procedure
Re: I got a problem while writing a simple procedure [message #434487 is a reply to message #434464] Wed, 09 December 2009 06:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm with @pablolee on this - you've misunderstood what is required of you.

You really really don't want to do this sort of thing dynamically.
Re: I got a problem while writing a simple procedure [message #434489 is a reply to message #434487] Wed, 09 December 2009 06:34 Go to previous message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
I was originally going to suggest that he go for super bonus points by submitting the requirements along with a solution that doesn't include the dynamic create along with an explanation that such is a very poor practice (but maybe teach would have seen that as being a smart 4rs3) Very Happy
Previous Topic: execute a procedure automatically
Next Topic: Assistant Required
Goto Forum:
  


Current Time: Sat Sep 24 21:32:16 CDT 2016

Total time taken to generate the page: 0.10501 seconds