REF CURSOR versus TABLE function in Oracle
Hi, I have PL-SQL Packages which return REF Cursors when data has to be retrieved as part of the CRUD operations. Would it be faster if these cursors were replaced with TABLE functions ? Thanks ...
oracle
Why can't an Oracle XMLType be sent over a DBLink?
I have some PLSQL code that calls a remote procedure in order to send an XMLType. What it does, though, is to devide the XMLType into multiple varchar2(4000) parameters, which the procedure accepts. The remote procedure is called via a DBLink. Why does the XMLType have to be split? Is this restriction applicable to recent database versi...
Oracle: how to enable flashback "as of" queries for all developers?
How do I enable flashback queries for all of my developers on my Oracle instance? Flashback queries are executed as of a particular time so that select * from mytable as of timestamp(sysdate-1); will show the contents of the table as of 24 hours ago. ...
PL/SQL: Procedure not working correctly in a package
I'm working on a package with some procedures in them and I'm running into a bit of trouble. When I try to test the procedure to convert gallons to liters or the other procedures, it just prints out what was declared in the unnamed block instead of converting the numbers. Any ideas? CREATE OR REPLACE PACKAGE eng_metric IS PROCEDURE ...
How to connect to Oracle using JRuby & JDBC
First approach: bare metal require 'java' require 'rubygems' require "c:/ruby/jruby-1.2.0/lib/ojdbc14.jar" # should be redundant, but tried it anyway odriver = Java::JavaClass.for_name("oracle.jdbc.driver.OracleDriver") puts odriver.java_class url = "jdbc:oracle:thin:@myhost:1521:mydb" puts "About to connect..." con = java.sql.DriverMa...
Defining Collections with variables
How can I define Collections in PL/SQL with variables? Eg. v_owner varchar(128) := 'SCHEMA_USER'; v_tablename varchar(128) := 'TABLENAME'; TYPE t_tab IS TABLE OF SCHEMA_USER.TABLENAME%ROWTYPE; v_tab t_tab; What I want is to use the variables instead of the names of the owner/table. Something like that: TYPE t_tab IS TABLE OF v_owne...
Retrieving a current and most recent previous value (Oracle)
Hi all, I'm facing a problem which I've spent a great deal of time trying to address, and although I have a solution, it's clunky and involves pl/sql processing, and I was wondering what others might come up with. I'm working with a dataset that creates a new row every time a record is changed, thus maintaining a history. The most up-to...
Why would a database trigger be invalid in an oracle DB?
I've created a trigger manually (By pasting the SQL from another oracle db into the management console) which auto increments the primary key of a table and when I look at the trigger in the object browser The trigger is listed as invalid. Why would this be the case? Here is the SQL that recreates the trigger: CREATE OR REPLACE TRIGGE...
Oracle - OCI query timeout
Is there any way to set a timeout on Oracle OCI calls (OCIStmtExecute for example) ? Thanks ...
oracle jdbc driver version madness
Why the heck does Oracle offer a different(!) version of the JDBC driver, e.g. ojdbc14.jar, for every(!) database version? The files all have different sizes and thus probably different content. background: We get a random and seemingly irreproducible error saying "invalid number" when saving data (we guess it's the Timestamp). But it's...
What is the maximum length of a table name in Oracle?
What is the maximum length of a table name in Oracle? For bonus points, what is the maximum length of a column name? ...
Restricting a LEFT JOIN
I have a table, let's call it "a" that is used in a left join in a view that involves a lot of tables. However, I only want to return rows of "a" if they also join with another table "b". So the existing code looks like SELECT .... FROM main ... ... LEFT JOIN a ON (main.col2 = a.col2) but it's returning too many rows, specifically o...
Setting up a diverse database testing environment
I'm writing some code that needs to work against an array of different database products. MySql Sql Server 2000 to 2008 PostgreSQL Oracle 9i & 10g Jet 4.0 (MS Access) MSDE Sybase Adaptive Server Anywhere Sybase Sql Anywhere Progress OpenEdge We have a set of generic integration tests that we need to run against each database produc...
Error trying to connect to Oracle from Rails on OS X
I went through all the steps described here to set up my OS X machine to allow me to connect to Oracle from a Rails app. Set up the database.yml file in my app: development: adapter: oracle_enhanced host: [SERVER IP ADDRESS] database: [ORACLE INSTANCE SID] username: xxx password: yyy encoding: utf8 Also tried it with the ...
Retrofitting record-level access restrictions in classic asp applications
Like the title says, I've been asked to come up with an estimate for retrofitting an existing asp application. The current security mechanism controls access to different parts of the application (page-level restrictions), but has no mechanism for flagging individual records as restricted. Assigning rights to a user (using the existing...
Oracle: flattening name value pairs into a table
Hi, I'm hoping someone can provide some advise for an easier way to deal with this problem. I am working on creating a flattened view of a highly normalized set of data. The goal of flattening is to provide a view which normal people can use to develop reports from. The source data contains a couple of tables as shown: CREATE TABLE ...
Things to watch out for in moving from Oracle Express to the Real Deal?
I'm writing an application which needs to work on an Oracle DB, we don't have an Oracle Db but our client does, so I've been developing/testing it using Oracle Express. Are there anythings I should watch out for in moving from the free version that might break my app when I run it against the Full fat edition? ...
subsonic.migrations and Oracle XE
Hello, Probably I'm doing something wrong but here it goes: I'm trying to create a database using subsonic.migrations in an OracleXE version 10.2.0.1.0. I have ODP v 10.2.0.2.20 installed. This is my app.config: <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> <section name="SubSonicService" type="SubSon...
What resources exist for Database performance-tuning?
What good resources exist for understanding database tuning on the major engines and advancing your knowledge in that area? The idea of this question is to collect the shed load of resources that invariably exist, so that people can have a "one stop" knowledge shop of the good, peer approved resources. General SQL Book: SQL Perform...
How come sqlplus not connecting?
My goal is to connect to an Oracle 9i instance from my OS X machine. I've followed the setup instructions here and got through them with no errors (eventually). However, I'm finding that sqlplus is unable to connect: [ ethan@gir ~ ]$ sqlplus xxx/yyy@zzz SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 17 10:13:08 2009 Copyright (c...