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()).
-- 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;
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;
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));
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.
"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.