Oracle: Frequently Asked Questions 


What built-in functions/operators are available for manipulating strings?

The most useful ones are LENGTH, SUBSTR, INSTR, and ||: The example below shows how to convert a string name of the format 'last, first' into the format 'first last':
        SUBSTR(name, INSTR(name,',',1,1)+2)
    ||  ' '
    ||  SUBSTR(name, 1, INSTR(name,',',1,1)-1)
For case-insensitive comparisons, first convert both strings to all upper case using Oracle's built-in function upper() (or all lower case using lower()).


Can I print inside a PL/SQL program?

Strictly speaking PL/SQL doesn't currently support I/O. But, there is a standard package DBMS_OUTPUT that lets you do the trick. Here is an example:
-- create the procedure
CREATE PROCEDURE nothing AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('I did nothing');
    -- use TO_CHAR to convert variables/columns
    -- to printable strings
END;
.
RUN;
-- set output on; otherwise you won't see anything
SET SERVEROUTPUT ON;
-- invoke the procedure
BEGIN
    nothing;
END;
.
RUN;
Then you should see "I did nothing" printed on your screen.

DBMS_OUTPUT is very useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow (the default buffer size is 2KB). In that case, you can set the buffer size to a larger value, e.g.:

BEGIN
    DBMS_OUTPUT.ENABLE(10000);
    nothing;
END;
.
RUN;


Is it possible to write a PL/SQL procedure that takes a table name as input and does something with that table?

For pure PL/SQL, the answer is no, because Oracle has to know the schema of the table in order to compile the PL/SQL procedure. However, Oracle provides a package called DBMS_SQL, which allows PL/SQL to execute SQL DML as well as DDL dynamically at run time. For example, when called, the following stored procedure drops a specified database table:
CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS
    cid INTEGER;
BEGIN
    -- open new cursor and return cursor ID
    cid := DBMS_SQL.OPEN_CURSOR;
    -- parse and immediately execute dynamic SQL statement
    -- built by concatenating table name to DROP TABLE command
    DBMS_SQL.PARSE(cid, 'DROP TABLE ' || table_name, dbms_sql.v7);
    -- close cursor
    DBMS_SQL.CLOSE_CURSOR(cid);
EXCEPTION
    -- if an exception is raised, close cursor before exiting
    WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(cid);
    -- reraise the exception
    RAISE;
END drop_table;
.
RUN;


What is the correct syntax for ordering query results by row-type objects?

As a concrete example, suppose we have defined an object type PersonType with an ORDER MEMBER FUNCTION, and we have created a table Person of PersonType objects. Suppose you want to list all PersonType objects in Person in order. You'd probably expect the following to work:
    SELECT * FROM Person p ORDER BY p;
But it doesn't. Somehow, Oracle cannot figure out that you are ordering PersonType objects. Here is a hack that works:
    SELECT * FROM Person p ORDER BY DEREF(REF(p));


How do I kill long-running queries in sqlplus, Pro*C, and JDBC?

Sometimes it is necessary to stop long-running queries, either because they take longer to run than you'd like, or because you realize you've made a mistake. It is important kill off such queries properly so that they don't take up extra computational resources and prevent others from using the system, especially near project deadlines when resources are most strained.

As a general precautionary measure, please be sure to test your queries under sqlplus prompt before running them through CGI or JDBC.  It is much easier to kill a query in sqlplus than in CGI or JDBC.  If your test query takes a long time to run under sqlplus, you can simply hit Ctrl-C to terminate it.

Never close an ssh or telnet or xterm window without properly logging out.  Always quit your programs (including sqlplus), stop Java servlets, and type "exit" or "logout" to quit.  If you force-close your ssh/telnet/xterm window, there may still be processes running in the background, and you may be taking up system resources without knowing it.

If, for some reason, you cannot logout normally (for example, the system is not responding), you should open another window, login to the same machine where you have the problem, and kill the processes that is causing trouble:

Type "ps -aef | grep [username]" to find the Process IDs of your processes (replace [username] with your leland user name), and kill the  processes you want to terminate using "kill [processID]".  Always use the "kill" command without the -9 flag first.  Use -9 flag only if you cannot kill it otherwise.
If you closed the window by mistake and do not remember which sweet hall machine you were logged into, open another window immediately and log into any sweet hall machine, then type "sweetfinger [username]" (replace [username] with your actual leland user name).  It will give you the machine names you were on a few minutes ago.  Then, log in to the appropriate machine and kill your processes there.

If you issued a query through JDBC that is taking a long time to execute and you want to kill it, you should stop your Java servlet.  In most cases this will kill the query. You can also use the setQueryTimeout([time in seconds]) method on a statement object to stop queries that run too long.

If you issued a query through CGI that is taking a long time to execute, normally the CGI service will kill it for you within 10 seconds.  However, the above occasionally fails to work, and we do not know of any better way of killing runaway queries issued by JDBC or CGI (other than asking the administrator to kill them for you).  That's why we ask you to always test your queries under sqlplus first.  It is much easier to kill queries there.


In Pro*C, why do I get a strange "break outside loop or switch" error  message?

If you get an error message

"break" outside loop or switch

when compiling your Pro*C program, chances that you have the following statement somewhere before a loop:

EXEC SQL WHENEVER NOT FOUND DO break;

After the loop, you should insert the following statement:

EXEC SQL WHENEVER NOT FOUND CONTINUE;

This would cancel the previous WHENEVER statement. If you do not do this, you may get the error message at subsequent SQL calls.


This document was written originally by Jun Yang for CS145 in Spring, 1999. Additions by Antonios Hondroulis and Calvin Yang in Spring, 2002