Lets just say you have a table in Oracle: CREATE TABLE person ( id NUMBER PRIMARY KEY, given_names VARCHAR2(50), surname VARCHAR2(50) ); with these function-based indices: CREATE INDEX idx_person_upper_given_names ON person (UPPER(given_names)); CREATE INDEX idx_person_upper_last_name ON person (UPPER(last_name)); Now, given_...
Hi everyone, I'm migrating a TSQL stored procedure to PL/SQL and have encountered a problem - the lack of a CONTINUE keyword in Oracle 10g. I've read that Oracle 11g has this as a new feature, but upgrading is not an option unfortunately. Is there any alternative to CONTINUE in 10g? I don't believe it's practical to restructure the lo...
I am trying this: Provider=MSDASQL.1;Persist Security Info=False;User ID=sys;Password=pwd;Initial Catalog=DATABASE;Data Source=OdbcDataSource;DBA Privilege=SYSDBA But I get the error: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed I'm using Delphi, but answers in any language are welcome. Clarification: I am ab...
Basically, I'm trying to selectively copy a table from one database to another. I have two different [Oracle] databases (e.g., running on different hosts) with the same schema. I'm interested in a efficient way to load Table A in DB1 with the result of running a select on Table A in DB2. I'm using JDBC, if that's relevant. ...
I work in an Oracle shop. There's a toolset that consists of roughly 1000 Oracle Forms (using the Forms builder from 6i, early 90's software) with Oracle 10g on the back end. It's serving roughly 500 unique people a month, with 200 concurrent connections at any given time during the work day. Obviously this is something that needs to ...
I've just discovered that Oracle lets you do the following: SELECT foo.a, (SELECT c FROM bar WHERE foo.a = bar.a) from foo As long as only one row in bar matches any row in foo. The explain plan I get from PL/SQL developer is this: SELECT STATEMENT, GOAL = ALL_ROWS TABLE ACCESS FULL BAR TAB...
I'm developing a web-mapping application for which the map view is provided by Oracle MapViewer as a MVMapView object. What i'm really trying to do is add mouse wheel functionality for zooming in/out when the mouse is over the viewable map. Unfortunately MapViewer doesn't have an onmousewheel event with this object so i'm trying to do th...
When we execute select count(*) from table_name it returns the number of rows. What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution? ...
I've installed Oracle XE with APEX, but forgot to write down the URL to access it. How may I determine the URL? ...
We get sometimes the following error from our partner's database: <i>ORA-01438: value larger than specified precision allows for this column</i> The full response looks like the following: <?xml version="1.0" encoding="windows-1251"?> <response> <status_code></status_code> <error_text>ORA-01438: value larger than specified precis...
Oracle database 11g. What is the easiest way to set up a full nightly database backup to a network drive (ie drive on another computer)? ...
I am receiving an error from the Oracle JDBC driver (ojdbc14_g.jar) when trying to obtain a connection to a 10g database. The driver has an oracle.jdbc.driver.OracleLog class which could help but the Oracle documentation is unclear how best to use it. Has anyone had any success using this class? If so, some guidance on its use would be a...
Using Oracle, if a column value can be 'YES' or 'NO' is it possible to constrain a table so that only one row can have a 'YES' value? I would rather redesign the table structure but this is not possible. [UDPATE] Sadly, null values are not allowed in this table. ...
In applying for jobs via agents I sometimes get blocked by an agent who says do you know software package X. When I reply that I know the similar package Y they might say unless you know X I cannot put you forward. The problem is that some of these agents don't know what they talking about, they are merely being used by their clients as...
SQL to find duplicate entries (within a group) I have a small problem and I'm not sure what would be the best way to fix it, as I only have limited access to the database (Oracle) itself. In our Table "EVENT" we have about 160k entries, each EVENT has a GROUPID and a normal entry has exactly 5 rows with the same GROUPID. Due to a bug we...
In an Oracle cluster (more than one machine co-operating to serve one database) will the "sysdate" function always return a consistent answer? Even if the servers' Operating System clock reports inconsistent values? ...
I have a query to monitor SGA (non-)utilisation: select sum(bytes)/1024/1024 " SGA Free " from v$sgastat where name='free memory'; That's great, but I need more detail about what exactly is using the space. Essentially, I'm looking for clues to tell me more precisely what might be wrong with this (rather large application). select * f...
select distinct constraint_type from user_constraints; C - C P R U Seems P means primary key and R means foreign key, correct? What are U and C? ...
I'm currently using Oracle Developer Tools for Visual Studio. The plug-in plays nicely with VS and I don't have to start another app. to write stored procs etc. I also like the ability to check-in, check-out (from VSS 2005) and run the scripts. But I have few complaints as well: It is inconvenient to use the server explorer to browse...
What is this thing called a KGL SIMULATOR and how can its memory utilisation be managed by application developers? The background to the question is that I'm occasionally getting errors like the following and would like to get a general understanding of what is using this heap-space? ORA-04031: unable to allocate 4032 bytes of share...