Hello I have a table named CITY in oracle SQL developer, my problem is how to run the procedure
-- start the script
SET SERVEROUTPUT ON
SET LINESIZE 400
SET TIMING ON
CREATE OR REPLACE PACKAGE BODY hotel AS
-- -----------------------------------------------------
-- Table city
-- -----------------------------------------------------
PROCEDURE fill_city(number_city NUMBER) IS
city VARCHAR2(100);
postna_st VARCHAR2(4);
BEGIN
FOR st IN 1..number_city LOOP
city:= dbms_random.string('a',100);
postal_number:= dbms_random.value(1000,9000);
INSERT INTO CITY(city, postal_number) VALUES (city, postal_number);
END LOOP;
END;
BEGIN
NULL;
END hotel;
/
SHOW ERRORS;
creating the procedure
CREATE OR REPLACE PACKAGE hotel AS
PROCEDURE fill_city(number_city NUMBER)
END hotel;
/
SHOW ERRORS;
and now how to execute the procedure?
--EXECUTE fill_city(10000) ;
Begin
fill_city(10000);
End;
I tried both but with luck.
I recived the following error
Error report:
ORA-06550: line 2, column 2:
PLS-00201: identifier 'fill_city' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Elapsed: 00:00:00.018
Best Answer
First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.
should correctly invoke your procedure.
Second, you have issues with the naming of your local variables. Normally, you would not create local variables like
city
andpostal_number
that are the same as the names of columns in tables in your database. That makes it far too easy to introduce errors in your code where you intend to refer to the local variable but scope resolution rules mean that you are really referring to the column name. For example, if you write the perfectly valid functionin your
WHERE
clause, both references todeptno
will resolve to the column in thedept
table, not to the parameterdeptno
. That means that no matter what value you pass in to the function, theSELECT
statement will return every row from the table and, thus, throw atoo_many_rows
error. Normally, you would come up with a convention on how to name variables and parameters that would not conflict with your table naming conventions. Prefixing parameters withp_
and local variables withl_
is one common convention. That turns our function into something like thisThe other option would be to use explicit scoping prefixes when referring to local variables (i.e.
get_dname.dname
andget_dname.deptno
) rather than altering the names of your local variables.