Sounds like the network buffer is being filled but not flushed. SQL*Net performance in this area is often managed by setting tcp.nodelay=yes in sqlnet.ora; also look at the send_buf_size and recv_buf_size parameters.
SQL*Dev working and not SQL*Plus are what pointed me to this type of troubleshooting approach. SQL*Developer uses jdbc behind the scenes to do its fetching.
hth!
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
SQL*PLUS and SQL Developer both are client application for Oracle database. These applications just validate the syntax of statements and submit to Oracle Database server for execution. As far as the question is concerned about the uses of
/
and;
in these application I would like to demostrate as best as I can.SQL*PLUS
In SQL*PLUS-
;
is used to end the current statement./
is used to end current statement as well as If you are in the SQL prompt(SQL>
) to run the command which are in the buffer.Examples:
To end and execute the statement-
To run the command which is in the buffer(Only the SQL command(not SQL*PLUS command) and the last executed command remains in the buffer).
Instead, you can use
RUN
command for the same purpose. The difference is/
doesnt show the command being executed butRUN
does.SQL*PLUS treats PL/SQL program as the SQL command except the
;
and the blank line. In normal SQL command if you insert an blank line then it terminates the command as shown below.But in PL/SQL block it doesnt terminate the program.
To terminate and execute the PL/SQL block we use the same
/
.SQL Developer
In SQL Developer we use
CTRL+ENTER
orF5
keys to execute commands. We dont need to use;
and/
to indicate the end of the command instead we useRun Statement(CTRL+ENTER)
orRun Script(F5)
.