We have been having some debate this week at my company as to how we should write our SQL scripts. Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production. Now, we had a deploy recently that failed because it had used both a semicolon and a forward slash (...
I am using the thin Oracle JDBC driver ver 10.2.0 (ojdbc14.jar). I would like to configure its NLS_LANG setting manually. Is there a way? Currently it fetches this setting from the VM variable user.language (which is set automatically by setting the current locale, or on startup from the system environment). This is a problem when the ...
One of the columns in my table is a varchar that is supposed to contain only numeric values (I can't change the definition to a number column). Thus my SQL query: select to_number(col1) from tbl1 where ... fails because for some row the contents of the column are not numeric. What's a select query I can use to find these rows ? I'm ...
What is the best PHP framework to make applications has an Oracle database? ...
In case management and workflow systems this seems to come up a lot. The need to provide a comprehensive list of each business message in the 'system' and provide its possible cause(s) and corrective action(s). A vendor example is Oracle: All their errors have a naming convention (e.g. ORA-00237) and they have documented every possi...
Oracle START WITH ... CONNECT BY clause is applied before applying WHERE condition in the same query. Thus, WHERE constraints won't help optimize CONNECT BY. For example, the following query will likely perform full table scan (ignoring selectivity on dept_id): SELECT * FROM employees WHERE dept_id = 'SALE' START WITH manager_id is n...
I have a nice DataGridView showing what is basically some kind of log data to the user. The user can filter by district, region, settlement and street. So far, the ComboBoxes are linked to each other, so at first only the district has values, and only after picking one, the region one is filled with the proper values, etc. The new req...
We have an Oracle database that contains IP addresses stored as decimal integers - this is incredibly painful when manipulating the data by hand instead of via the web interface, yet hand manipulation is really handy as the network guys continually ask us to do strange things that the web interface authors did not anticipate. Could some...
How do I qualify a function or procedure call to indicate that it should be at global scope? I have my own scn_to_timestamp() in a package that needs to call the default global function of the same name. create or replace package px as function scn_to_timestamp(scn number) return timestamp; end px; create or replace package body p...
given this table: x y -- - 10 a 20 b 30 c I want the best way to to map values [10,20) -> a [20,30) -> b [30,inf) -> c Right now I'm using a query like: select y from foo where x=(select max(x) from foo where x<=21); Is there a better way to do this? Is there an analytic function that might help? Here's my te...
We have a big program, using BDE + oracle, developed by delphi, We use 3 threads to do DB operations, they are using separated TSession. (1 deamon thread, 1 data collect thread, 1 checking thread) We use this structure: TQuery -> TDatabase -> TSession We use this method to do query: try qu.close; //TQuery qu.sql.clear; qu....
Hi, I am not a programmer, but have a task of automatically copying one field in a table to another field in the same table (it's a long story... :-) ). This should be done on update and insert and I really do not know how to go about it. I should point out that data is entered to the DB through a user-interface which we do not have the ...
I'm having problems with a mammoth legacy PL/SQL procedure which has the following logic: l_elapsed := dbms_utility.get_time - l_timestamp; where l_elapsed and l_timestamp are of type PLS_INTEGER and l_timestamp holds the result of a previous call to get_time This line suddenly started failing during a batch run with a ORA-01426: num...
My company is now supporting Oracle for the first time, and most of my colleagues are SQL Server developers who haven't really worked with Oracle. I'm a little bit familiar with Oracle, but far from a genius on the subject. Unfortunately, that is enough to mean that I know more about Oracle than most of my co-workers, so I find myself ...
I get the following error when I try to run my project: java.sql.SQLException: Io exception: Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4)))) Any ideas? ...
Is there an Oracle function to return the data type of the parameter? Alternatively, what is the easiest way to determine the data type of all columns in a query that I've written? ...
Generally in programming, the floating point data types should not be compared for equality since the values stored are very often an approximation. Can two non-integer Oracle NUMBER values be compared reliably for equality since they are stored differently (base-10)? ...
I have an Excel file that I need to import into a (new) Oracle table. What is the best way to do this? ...
I'm using Oracle, and I have a very large table. I need to check for the existence of any row meeting some simple criteria. What's the best way to go about this using simple SQL? Here's my best guess, and while it may turn out to be fast enough for my purposes, I'd love to learn a canonical way to basically do SQL Server's "exists" in...
I want to be able to split csv strings in Oracle 9i I've read the following article http://www.oappssurd.com/2009/03/string-split-in-oracle.html But I didn't understand how to make this work. Here are some of my questions pertaining to it Would this work in Oracle 9i, if not, why not? Is there a better way of going about splitting c...