oracle

Compare and Contrast Change Data Capture and Database Change Notification

Oracle has two seemingly competing technologies. CDC and DCN. What are the strengths of each? When would you use one and not the other? ...

What are some best practices for querying binary data from a database?

I'm being asked to add queryability to a database (Oracle) filled with mostly binary data. So I need to be able to query binary ranges within a blobs of a few kilobytes. I've never done this before, so I'm wondering what are some good practices and pitfalls to consider when starting a project like this. Thanks ...

Connection to Oracle without a username or password

Oracle has this concept of allowing database users to be identified by the operating system user who is running the program that is connecting to Oracle. See here. This allows you to do, as that user on a unix machine for example, a command such as: sqlplus / I am attempting to write a Java program for Oracle 10.2 which connects with...

Problems with globalization when using Oracle collections with thin JDBC

To summarise the issue: Retrieving strings in Java 1.5 (JDBC) works fine when the DB encoding is Western ISO 8859-2 When switching to an Eastern European ISO (e.g. ISO 8859-5), all the normal JDBC string conversions work, except the ones involving Oracle collections, e.g. nested tables of objects. Instead of proper strings, even simple...

How to choose and optimize oracle indexes ?

I would like to know if there are general rules for creating an index or not. How do I choose which fields I should include in this index or when not to include them? I know its always depends on the environement and the amount of data, but I was wondering if we could make some globally accepted rules about making indexes in Oracle. ...

Oracle Advanced Queuing - Queue Propagation

EDIT: It seems to be something with having the two queues in the same schema. I’m trying to experiment with queue propagation but I’m not seeing records in the destination queue. But that could easily be because I don’t have all the pieces in place. Does anyone have a test case they could post? I’ll include what I tried below. I found ...

"Pivoting" a Table in SQL (i.e. Cross tabulation / crosstabulation)

I'm working on trying to generate a report from a couple of database tables. The simplified version looks like this Table = Campaign CampaignID Table = Source Source_ID | Campaign_ID Table = Content Content_ID | Campaign_ID | Content_Row_ID | Content_Value The report needs to read like this: CampaignID - SourceID - ContentRow...

Alternatives to Toad

Hello. I'm currently using Toad for my day-to-day work on our databases (queries, updates, small scripts, browsing of db objects, etc.). My question is: since my version of Toad is old and buggy, which are the (possibly free, but not necessarily) alternatives to Toad? The database versions we are using are 10g and 9i. Thanks a lot. ...

postgresSQL mysql oracle diferences

I'm having to start building the architecture for a database project but i really don't know the differences between the engines. Anyone can explain whats the pros and bads of each of these three engines? We'll have to choose one of them and the only thing I actualy know about them is this: Mysql & Postgres: Are free but not so good ...

Differences in prepared vs. direct statements using Oracle ODBC

I'm using an Oracle database with a collation different to my OS language. I'm accessing the database using the ODBC driver. When I prepare a statement (e.g. a "select * from x where=?"), that involves special non-ASCII characters supported by the DB's collation, I'm finding the data row with the characters. When I execute the select dir...

How to query an Oracle table from SQL Server 2000?

A colleague would like to query a table in an Oracle database using SQL Server 2000, Enterprise Manager. He knows how to do the whole DTS thing, but doesn't want to go down that route for add hoc queries. Is there another way? ...

Oracle Text: How to sanitize user input

If anyone has experience using Oracle text (CTXSYS.CONTEXT) and wondering how to handle user input when the user wants to search for names that may contain an apostrophe. Escaping the ' seems to work in some cases, but not for 's at the end of the word - s is in the list of stop words, and so seems to get removed. We currently change s...

Oracle: How can I implement a "natural" order-by in a SQL query?

e.g, foo1 foo2 foo10 foo100 rather than foo1 foo10 foo100 foo2 Update: not interested in coding the sort myself (although that's interesting in its own right), but having the database to do the sort for me. ...

Database agnostic jdbc table import/export to files?

Is it at all possible to do database-agnostic table dumps/hydrates? I don't have any complicated constraints. I would also settle for db-specific ways, but the more pure jdbc it is the better (I don't want to resort to impdp/expdp). ...

Sybase Developer Asks: How To Create a Temporary Table in Oracle?

I'm familiar with Sybase / SQL server, where I can create a temp. table like this: SELECT * INTO #temp FROM tab1 , tab2 WHERE tab1.key = tab2.fkey SELECT * FROM #temp WHERE field1 = 'value' #temp only exists for the duration of this session, and can only be seen by me. I would like to do a similar thing in Ora...

Oracle PL/SQL - Are NO_DATA_FOUND Exceptions bad for stored procedure performance?

I'm writing a stored procedure that needs to have a lot of conditioning in it. With the general knowledge from C#.NET coding that exceptions can hurt performance, I've always avoided using them in PL/SQL as well. My conditioning in this stored proc mostly revolves around whether or not a record exists, which I could do one of two ways:...

Is it ok to set the sequence of a table to very large value like 10 million?

Is there any performance impact or any kind of issues? The reason I am doing this is that we are doing some synchronization between two set of DBs with similar tables and we want to avoid duplicate PK errors when synchronizing data. ...

Audit Revoke Operations

How can REVOKE operations on a table be audited in Oracle? Grants can be audited with... AUDIT GRANT ON *schema.table*; Both grants and revokes on system privileges and rolls can be audited with... AUDIT SYSTEM GRANT; Neither of these statements will audit object level revokes. My database is 10g. I am interested in auditing rev...

Converting between oracle.sql.TIMESTAMPTZ and standard JDBC classes for DbUnit

I'm running Oracle 10g and have columns with Type_Name TIMESTAMP(6) WITH TIME ZONE When inflated into java classes they come out as oracle.sql.TIMESTAMPTZ But DbUnit can't handle converting Oracle specific classes to Strings for writing to XML. I'm wondering if there's any easy way for me to convert (say, in my SELECT statement so...

Oracle database role - select from table across schemas without schema identifier

Which Oracle database role will allow a user to select from a table in another schema without specifying the schema identifier? i.e., as user A- Grant select on A.table to user B; B can then- "Select * from table" without specifying the 'A'. One of our databases allows this, the other returns a 'table or view does not exist' error. ...