I haven't tested it again, but as far as I remember you need DEBUG privileges to use the debugger in SQL Developer:
GRANT DEBUG CONNECT SESSION TO YOUR_USER;
GRANT DEBUG ANY PROCEDURE TO YOUR_USER;
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.
Best Answer
I think as a DBA you will inevitably lose the fight to keep hands out of your database. Having said that I think we owe it to our customers to try and provide a product that they can use. There are dangers and pitfalls of even read-only access that any DBA should be aware of:
I think thats enough devils advocate but I guess in summary my answer would be:
"Never in production and never in QA. If you want to run code you write it you support it and then you package it in a deployable format so I can push it out. If you want to act like a developer I'm going to treat you like one."