Apparently, it is not possible to use Oracle’s pseudo column rownum to select rows after a given number of rows, e.g. like SELECT * FROM tab WHERE rownum > 100; or in MySQL SELECT * FROM tab LIMIT 100,50; Oracle’s explanation for this (from the DB reference: ROWNUM): The first…
A colleague of mine had this interesting problem today: OdbcDataAdapter ORA-01000. When querying an Oracle database via ODBC from C# using the following code, an Oracle error ORA-01000: maximum open cursors exceeded occured after a certain number of iterations (300 in our case): static void Main(string[] args) { var connectionString…
Until today, I thought to be able to connect to an Oracle database from your Windows machine, you need to install the complete Oracle client including the Enterprise Manager etc. (>1GB). However, today I finally found a way to use Oracle via ODBC using only the InstantClient (ca. 50MB) on…
After I installed the latest version of PL/SQL Developer on my Windows 7 64bit machine today, I got the following error message when trying to connect to an Oracle database: Initialization error Could not initialize “D:\oracle\product\11.2.0\client_1\bin\oci.dll” Make sure you have the 32 bits Oracle Client installed. As I’ve found out,…
If you would like to verify the result of a function call in Oracle SQL*Plus you could simply include it in a SQL select statement like this: create or replace function testfunction(testparm IN number) return number is begin return(testparm); end testfunction; SQL> select testfunction(123) from dual; TESTFUNCTION(123) —————– 123 SQL>
Today I wanted to log some database output to a text file with UTL_FILE.PUT_LINE but when I called this procedure: CREATE OR REPLACE PROCEDURE writeLog(line varchar2) iS file utl_file.file_type; BEGIN file := utl_file.fopen(‘/tmp’, ‘test.txt’, ‘A’); utl_file.put_line(file, line); utl_file.fflush(file); utl_file.fclose(file); dbms_output.put_line(line); END writeLog; it produced an ORA-29280 error: invalid directory path:…
Here’s a small piece of SQL to generate SQL commands for killing all user sessions of a given user (you need to be sys/system): select ‘alter system kill session ”’ || sid || ‘,’ || serial# || ”’;’ from gv$session where username=’USER’ and machine=’PCNAME’;