oracle

Purpose: drop all schema contents without dropping the user. <pre lang="sql"> DECLARE BEGIN FOR r1 IN ( SELECT 'DROP ' || object_type || ' ' || object_name || DECODE ( object_type, 'TABLE', ' CASCADE CONSTRAINTS PURGE' ) || DECODE ( object_type, 'TYPE', ' FORCE' ) AS v_sql FROM user_objects WHERE object_type IN ( 'TABLE', 'VIEW', 'PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SEQUENCE' ) ORDER BY object_type, object_name ) LOOP BEGIN EXECUTE IMMEDIATE r1....

First, assume your schema is called ‘D’. To see the cursor parameters: <pre lang="sql"> show parameter cursor; See this faq for the meaning of these parameters: http://www.orafaq.com/node/758 To check the amount of “open” and “closeable” run: <pre lang="sql"> select a.value, s.username, s.sid from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' AND username = 'D'; To find the session which is having the largest number of cursors:...

Migrating from ojdbc14.jar to ojdbc6.jar is not, as one might think, completely seamless. Here are at least 3 points which required code change: Changes in class hierarchy error: OracleTypes is not public in oracle.jdbc.driver; cannot be accessed from outside package <span class="error">[javac]</span> private static Integer OracleRefCursorType = new Integer(oracle.jdbc.driver.OracleTypes.CURSOR); Behaviour is also not the same: java.sql.SQLException: Could not commit with auto-commit set on at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:2356) at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:2403) at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334) at org....

There are many, many links, threads, bugs and discussions related to this since oracle 11g installation is no longer breeze at it was the case with oracle 10g, at least on Ubuntu. This is my short, minimal list of things to do to have oracle running on Ubuntu 14.04 12.04. Last updated 2014-05-01, install on 14.04 Last updated 2013-12-25, install on 12.04.3. 0. backup. If you have a previous oracle install backup your databases with expdp....

This is a very simple example demonstrating a method to achieve a reliable lock in a cluster when the cluster shares a database connection. This example uses an Oracle database which has the following table/row: <pre lang="sql"> create table locked(shortname varchar2(100) primary key, info varchar2(100)); insert into locked values('TestLock', ''); commit; <pre lang="java"> package com.mccsoft.diapason.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestLock implements Runnable{ static final String JDBC_DRIVER = "oracle....

This is just a short reminder to the cause and solution of the following exception (hibernate + jboss 4.2.2 GA + oracle 10): 2012-02-09 13:09:50,333 ERROR [Thread-3-org.hibernate.AssertionFailure] an assertion failure occured (this may indicate a bug in Hibernate, but is more likely due to unsafe use of the session) java.lang.NullPointerException at org.hibernate.engine.ActionQueue.afterTransactionCompletion(ActionQueue.java:176) at org.hibernate.impl.SessionImpl.afterTransactionCompletion(SessionImpl.java:424) at org.hibernate.jdbc.JDBCContext.afterTransactionCompletion(JDBCContext.java:225) at org.hibernate.transaction.CacheSynchronization.afterCompletion(CacheSynchronization.java:85) at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.afterCompletion(SynchronizationImple.java:136) at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.afterCompletion(TwoPhaseCoordinator.java:340) at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.cancel(TwoPhaseCoordinator.java:107) at com.arjuna.ats.arjuna.AtomicAction.cancel(AtomicAction.java:230) at com.arjuna.ats.arjuna.coordinator.TransactionReaper.check(TransactionReaper.java:202) at com.arjuna.ats.internal.arjuna.coordinator.ReaperThread.run(ReaperThread.java:107) 2012-02-09 13:09:50,339 WARN [Thread-3-com....

Assuming you have the following Oracle table: <pre lang="sql">create table icon(id number(19), icon blob); these are 2 methods I found for inserting the value of a file in the icon.icon column. Method 1 This is the most commonly found method: <pre lang="sql">CREATE OR REPLACE PROCEDURE load_icon (imagefile VARCHAR2) IS src_file BFILE; dst_file BLOB; icon_id NUMBER(19,0); BEGIN src_file := bfilename('TEMP', imagefile); -- insert a NULL record to lock INSERT INTO icon (ID, ICON) VALUES (icon_sequence....

After debuging for a few hours I’ve found the proper way of calling an Oracle function from hibernate and to use the return parameters. Here is the solution. The Oracle SQL function which returns -1 or 0: <pre lang="sql">create or replace function checkLastNPass (userId IN number, ... other params) RETURN SYS_REFCURSOR is st_cursor SYS_REFCURSOR; prevId number:= userId; ... other scope variables begin ... OPEN st_cursor FOR select -1 as retVal from DUAL; return st_cursor; ....

Update 11.11.2011, for ubuntu 11.10 oneiric oncelot: you will need version: cx_Oracle-5.0.4-11g-py27-1.x86_64.rpm if using 64bit and you installed oracle on 32 bit thus also installed libaio1:i386 the replace /lib/libaio.1.0.1 32bit with the corresponding version for 64bit or you’ll get: ``` import cx_Oracle ImportError: libaio.so.1: wrong ELF class: ELFCLASS32 ``` - if you try to use a newer version of cx\_Oracle you will get ``` import cx_Oracle ImportError: /usr/lib/python2.7/cx_Oracle.so: undefined symbol: PyUnicodeUCS2_AsEncodedString ``` End update....

Q: Assume someone, for compatibility is storing java.util.Date values in Oracle as long values (number of milliseconds since the standard base time known as “the epoch”, namely January 1, 1970, 00:00:00 GMT), how to read these dates in sql? A: select TO_CHAR(TO_DATE('19700101000000','YYYYMMDDHH24MISS') + NUMTODSINTERVAL(start_time/1000, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') from qrtz_triggers