I have a sequence used to seed my (Integer based) primary keys in an oracle table. It appears this sequence has not always been used to insert new values into the table. How do I get the sequence back in step with the actual values in the table? ...
I was researching some JDBC Oracle Connection Pooling items and came across a new(er) Oracle Pool implementation called Universal Connection Pool (UCP). Now this uses a new class, PoolDataSource, for connection pooling rather then the OracleDataSource [with the cache option enabled]. I am debating whether to switch to this new implemen...
Why isn't my PL/SQL duration function working correctly? In the query below, I manually calculate the 'hh:mm' the same way as in the function below. However, I get different results. Calling Query: WITH durdays AS (SELECT SYSDATE - (SYSDATE - (95 / 1440)) AS durdays FROM DUAL) SELECT TRUNC (24 * durdays) AS durhrs, MOD (TRUNC ...
Hey all, MySQL/MSSQL has a neat little inline if function you can use within queries to detect null values, as shown below. SELECT ... foo.a_field AS "a_field", SELECT if(foo.bar is null, 0, foo.bar) AS "bar", foo.a_field AS "a_field", ... The problem I'm running into now is that this code is not safe to run on an Oracle database,...
Here is the scenario: Oracle 10g database running Windows Vista Business. This is a production/live db. Nightly backups (Whole database, online backup, ARCHIVELOG mode) moved to different machine on the network. Hard disk dies. Setup OS and Oracle 10g on the new hard drive. Oracle does not have any db instances yet. Is there an eas...
Executing Oracle RAC cluster management commands such as $ORA_CRS_HOME/bin/crs_start requires root permissions. Using Solaris RBAC (Role-Based Access Control), one can give a non-root user permissions to execute those commands, but the commands still fail internally. Example: $pfexec /opt/11.1.0/crs/bin/crs_stop SomeArg CRS-0259: Owner...
Hi folks, So i'm having this problem. I have two tables (Oracle), one is called Destination and the other one Reserve. Reserve has a foreign key to the id of Destination (Because on reserve has one destination). And reserve tuples means all the reserves that all the users have done. I need a way to check the top 3 most visited Destinat...
The following query (V_TITRATION_RESULTS) is a view uses a row-to-column pivot which returns about 20,000 rows: SELECT test.created_on as "Created On", r_titr as "Titrator", r_fact as "Factor" FROM (SELECT test_id, MAX(CASE WHEN result_tmpl_id = 2484 THEN result END) r_titr, MAX(CASE WH...
I'm unsure how someone would break my SQL if I simply replace all incoming single quotes with double quotes. Can someone enlighten me for both Oracle and SQL Server examples? Thanks. string sql1 = "select * from users where user_id = '" + "O'Reily".Replace("'", "''").Replace("\", "") + "'"; ==> "select * from users where user_id = 'O...
How to create a partition table in oracle10g? ...
In our server/client-setup we're experiencing some weird behaviour. The client is a C/C++-application which uses OCI to connect to an Oracle server (using the OTL library). Every now and then the DB server dies in a way (yes this is the core issue, but from application-side we're unable to solve it but have to deal with it anyway), that...
We get *.dmp files from client which has some masked table data including indexes and constraints. I do have those table structures (including indexes and constraints) at my end. I want to import just the data without the indexes and constraints (present in the .dmp file) in Oracle10g using 'imp' command. I am aware of the 'imp' comman...
I am using Oracle.DataAccess.Client inside Powershell. What I need to do is INSERT a new row of data, then retrieve the auto-generated ID field of the newly-created row for another INSERT command, immediately following. What is the best way to do this? I am pretty new to SQL and Oracle. Here is some of my code: $conn = "My Connection St...
Sometime this weekend one of our databases (11.1.0.7) began having problems accessing certain folders on a portion of the network through Java. I have reproduced the problem using a small portion of java that does nothing but create a file object and attempt to delete the file. From the database this works for deleting local files and de...
I am using sqlDeveloper and trying to export a table to a CSV file. Some of the fields are CLOB fields, and in many cases the entries are truncated when the export happens. I'm looking for a way to get the whole thing out, as my end goal is to not use Oracle here (I received an Oracle dump - which was loaded into an oracle db, but am u...
How do you exclude a set of values when using a left outer join? Consider the following query: SELECT i.id, i.location, area.description FROM incident_vw i, area_vw area WHERE i.area_code = area.code(+) AND i.area_code NOT IN ('T20', 'V20B', 'V20O', 'V20P') The query executes, yet none of the NULL area code...
Hello! My workmate and I are trainees and we got an exercise to realize a project. We have decided us to create a customer management in Java. Now we have to choose a database. We are able to use Oracle, MySQL, PostgreSQL, HSQLDB and of course other Open Source databases. So, what database is recommend for us? I thought Oracle is too com...
I have a case where Oracle data (phone numbers) was encrypted using the Oracle DBMS_OBFUSCATION_TOOLKIT DESEncrypt function with a unique user generated hash key. SQL Example: update Phone set encrypted_phone = WEBX_ENCRYPT_DECRYPT.ENCRYPT( '212-555-1201', '8IcrEuOdDjRT5iDjqHLcsA==') where person_id = 120000...
I am trying to connect to my oracle database using PDO but I am getting Class PDO not found error. I have checked that PDO is enabled and it appears so. Still I am not able to trace why I am getting this error. Here is my configure command, cscript /nologo configure.js "--enable-snapshot-build" "--enable-debug-pack" "--with-snapshot-te...
We are using Spring SimpleJdbcCall to call stored procedures in Oracle that return cursors. It looks like SimpleJdbcCall isn't closing the cursors and after a while the max open cursors is exceeded. ORA-01000: maximum open cursors exceeded ; nested exception is java.sql.SQLException: ORA-01000: maximum open cursors exceeded spring The...