How to run procedure in Oracle SQL developer

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

6 Points 0 Comments Share

Justin Cave Turn off AdBlock to see the correct answer
7 years ago
First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure ..

This site survives thanks to advertising. Disable AdBlock to see the whole correct answer at no cost!
We promise you will not find boring advertisements. Find out how to disable Adblock by clicking on this link.
Remember to update the page once you have disabled it.
10 Points 0 Comments
Justin Cave Correct answer
7 years ago
First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.
begin hotel.fill_city(10000); end; /
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 and postal_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 function
CREATE OR REPLACE FUNCTION get_dname( deptno IN NUMBER ) RETURN VARCHAR2 IS dname VARCHAR2(30); BEGIN SELECT dname INTO dname FROM dept WHERE deptno = deptno; RETURN dname; END;
in your WHERE clause, both references to deptno will resolve to the column in the dept table, not to the parameter deptno. That means that no matter what value you pass in to the function, the SELECT statement will return every row from the table and, thus, throw a too_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 with p_ and local variables with l_ is one common convention. That turns our function into something like this
CREATE OR REPLACE FUNCTION get_dname( p_deptno IN NUMBER ) RETURN VARCHAR2 IS l_dname VARCHAR2(30); BEGIN SELECT dname INTO l_dname FROM dept WHERE deptno = p_deptno; RETURN l_dname; END;
The other option would be to use explicit scoping prefixes when referring to local variables (i.e. get_dname.dname and get_dname.deptno) rather than altering the names of your local variables.
10 Points 0 Comments Share Donate

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM