I am interested in pre-parsing a user's input via Java for valid CTXCAT syntax before it is used to search against an Oracle CATSEARCH implementation (http://www.oracle.com/technology/products/text/htdocs/ctxcat_primer.html). The reason being is that Oracle returns a generic parse exception if the query contains any invalid syntax (such...
We have an sql build script, with oracle 10.2. At the end of the script we have this: @$ORACLE_HOME/rdbms/admin/utlrp.sql Which works on Linux but not on Windows. So we created a second file with this line: @%ORACLE_HOME%/rdbms/admin/utlrp.sql Surely there is a better way. How can I refer to an environment variable in a cross-platf...
The message that drives me crazy is ORA-01008 - Not all variables bound. Is there a way to know which one of the 42 possible variable names I have misspelled without staring at the monitor till my eyes pop out? Update: I use ADO.NET to access the database. Perhaps it does lose some information in Oracle exceptions, as Justin Cave has s...
Hi all, Hopefully someone can shed a little light on an issue that I'm currently having with an Oracle DB - I'm sure it's something simple!! I've managed to recreate this in a sample, so here is the DB structure: CREATE TABLE MyTable( ixMyTable NUMBER, clobData CLOB ) / CREATE OR REPLACE PACKAGE PKGTEST AS PROCEDURE DoSo...
I am just starting to learn ADF and I am tasked with examining how ADF's gantt chart works. I have completed Oracle's tutorial of it, however I feel that all the tutorial teaches is how to use the tutorial. Basically I am looking for a good tutorial to show how from an empty project: create a collection of dummy data [ not from a data...
Given three tables Dates(date aDate, doUse boolean), Days(rangeId int, day int, qty int) and Range(rangeId int, startDate date) in Oracle I want to join these so that Range is joined with Dates from aDate = startDate where doUse = 1 whith each day in Days. Given a single range it might be done something like this SELECT rangeId, aDate...
While entering an SQL statement in Oracle SQL Developer, I noticed that I have two choises. I can either "Execute Statement" or "Run Script". A similar choise seems to be available in SQL Maestro as well, altough named "Execute query" and "Execute as script". What exactly is the difference between the two? ...
I've gotten a request to show a person's local time based on their phone number. I know our local GMT offset, so I can handle USA phones via a database table we have that links US zip_code to GMT offset (e.g. -5). But I've no clue how to convert non-US phone numbers or country names (these people obviously don't have a zip code). If y...
Some people don't like sequences on Oracle. Why? I think they are very easy to use and so nice. You can use them in select, inserts, update,... ...
I'm optimising a bespoke database layer in a messaging system to increase performance. I'm using the ORACLE OCI OCIDescribeAny function to get meta-data about the columns in a database table. How do I check the meta-data using an OCI call to see if a column has a default value set (in its schema definition). ...
I am trying to retrieve an oracle field with datatype clob into a VB array without reformating the retrieval SQL string (simply SELECT * FROM my_table). I am using an ADO recordset. The clob values are lost when I close the connection or do any other action with the recordset. How can I achieve this. ...
I have a relatively simple select statement in a VB6 program that I have to maintain. (Suppress your natural tendency to shudder; I inherited the thing, I didn't write it.) The statement is straightforward (reformatted for clarity): select distinct b.ip_address from code_table a, location b where a.code_item = b.which...
One of apps I take care of in checking a Oracle DB table every x seconds to see if there are new data to process (other real-time app is populating it). Our new client business process forces our real-time up to populate this table with lots of records in a same time (say 10000) but just few times a day. Next time my app checks if there ...
When I define a report region's SQL as SELECT * FROM some_table, all is fine until new columns are added to some_table -- then it breaks with a "ORAxxx No data found" error. It is easy to remediate, as it's enough to Apply Changes on the region again, even without making any changes. However, it does not make for a robust application. ...
I want to implement paging in a gridview or in an html table which I will fill using ajax. How should I write queries to support paging? For example if pagesize is 20 and when the user clicks page 3, rows between 41 and 60 must be shown on table. At first I can get all records and put them into cache but I think this is the wrong way. Be...
This package uses two unique features of Oracle, REF_CURSOR and a package global variable. I would like to port the functionality from Oracle to Postgresql or MySQL. PACKAGE tox IS /*=======================*/ g_spool_key spool.key%TYPE := NULL; TYPE t_spool IS REF CURSOR RETURN spool%ROWTYPE; /*=======================...
I have created two identical tables which list all the exceptions with regards to certain rules of products in the data base. table one is for week 1 table two is for week two the exception are just view on a weekly basis and no correction is made in the data base. week twos data includes the exceptions in week one i want to exclude week...
I have a table with about 2,000,000 rows. I need to query one of the columns to retrieve the rows where a string exsists as part of the value. When I run the query I will know the position of the string, but not before hand. So a view which takes a substring is not an option. As far as I can see I have three options using like ‘% %’...
How can i enable tracing in a odp.net client from the connection string? Just adding the parameters traceFileName, TraceLevel and TraceOption ? ...
I want to create a stored procedure that takes the name of a table and a row_id and can serialize it to an xml string. The table may contain clobs and blobs. Can I do this in PL/SQL, or do I have to use Java? The main objective for this is to have a table with all updates and deletes on some tables, keeping the X latest versions, or X...