Hi All, I have an application containing 4 MDB's each of which receives SOAP messages over JMS from MQ. Once the messages have been received we process the XML into an object model and process accordingly which always involves either loading or saving messages to an Oracle database via Hibernate. Additionally we have a quartz process w...
I am developing a rich client application that will use the Entity Framework (with DevArt's DotConnect for Oracle) to connect to a central Oracle database. However, I have found that this scenario requires every client machine to have the Oracle client installed in order to connect to the Oracle 10g server. Is there an easy way around ...
We recently changed physical DB's, new servers, new locations, same database schema and data and since the change over, whenever we try to connect directly to the DB in our own desktop type applications about half of the time we get this error: SQL*Loader-704: Internal error: ulconnect: OCIServerAttach[0] ORA-12545: Connect failed becau...
I'm using a SQL query that is similar to the following form: SELECT col1, col2 FROM table1 LEFT OUTER JOIN table2 ON table1.person_uid = table2.person_uid AND table1.period = table2.period And it's either way too slow or something's deadlocking because it takes at least 4 minutes to return. If I were to change it to this: SELECT col...
According to google and some other sources (e.g., http://www.enterprisedt.com/publications/oracle/result_set.html), if I want to call a stored-function that returns a ref cursor, I need to write something like this in order to access the ResultSet: String query = "begin ? := sp_get_stocks(?); end;"; CallableStatement stmt = conn.prepare...
I have an employee table called ID_EMPLOYEE_MASTER with the fields EMP_CODE, EMP_NAME, EMP_JOIN_DATE, EMP_STATUS, EMP_BASIC, EMP_ACCOUNT, EMP_BANK I want to query all the employees whose status is 'P' ie..PRESENT,THEIR BANK ACCOUNT ie.. EMP_ACCOUNT, THEIR BASIC ie EMP_BASIC,THEIR NAME ie.. EMP_NAME, then give a sequence number, then t...
I have defined the global nls_date_format on Oracle 10.2 XE as follows: alter system set nls_date_format='YYYY-MM-DD HH24:MI:SS' scope=spfile; When connecting on Windows, the clients override it with session specific format, so I need to run this line at the beginning of every session: alter session set nls_date_format='YYYY-MM-DD HH...
I have a web site that I developed on Vista using Vb.net9. It makes a connection to Oracle. for the connection I use System.Data.OracleClient. It works fine on my machine, and our test server, but it does not work on the production server. We installed the Oracle Client 11 on the server. The error is System.Data.OracleClient require...
I'm trying to access a large Oracle database through SQL Server using OPENROWSET in client-side Javascript, and not having much luck. Here are the particulars: A SQL Server view that accesses the Oracle database using OPENROWSET works perfectly, so I know I have valid connection string parameters. However, the new requirement is for ...
On another thread I saw a answer to a sql problem using Oracle's analytics. Is there a equivalent in PostgreSQL or generic SQL? ...
I have a simple function written in Oracle 9i (version 9.2.0.4.0) to simulate an in-line IF. For those interested, here's the code: create or replace FUNCTION IIF (testExpression NUMBER, trueResult NUMBER, falseResult NUMBER) RETURN NUMBER AS BEGIN /* A simple in-line IF function for use with SQL queries. If the...
For reasons that are beyond my control I need to store huge arrays (10000+ entries) in database rows, and it has to be easy to access each index of the array individually, which means I'd rather avoid serialization/blobs, if at all possible. So my first idea, and the actual question here, is can I increase max columns for Oracle in anyw...
The stored procedures being written here currently concats the parameters to the queries: 'Select * From Names Where Name = ' || prmName || ' Order By ' || prmSortField Is it possible to parameterize this query inside the stored procedure? Possibly like: query = 'select * From Names Where Name = @name Order By ' || prmSortField call(...
In Oracle 10g I have a table that holds timestamps showing how long certain operations took. It has two timestamp fields: starttime and endtime. I want to find averages of the durations given by these timestamps. I try: select avg(endtime-starttime) from timings; But get: SQL Error: ORA-00932: inconsistent datatypes: expected ...
Hi, How would one get columns information on table which he doesn't own, but has select granted? This is, without using DESCRIBE table_name. Consider this example: // user bob owns table STUDENTS grant select on students to josh; // now josh logs in, normally he would do describe bob.students; // but he's looking for something along ...
In Oracle Forms 6i, you could enter query mode in your form, and type & or :A in a field, and when you executed the query, a Query/Where dialog box would open, allowing you to enter more complex query or sorting criteria than just entering data in the fields allows. This doesn't seem to work in Forms 10g: I get a "FRM-40367: Invalid Cri...
I have a large query (not written by me, but I'm making some modifications). One thing that kind of bugs me is that I've got the same COALESCE function in about four places. Is there a way to factor that out, possibly by joining with a select from DUAL? Is there a performance benefit to factoring it out? Here is the query slightly bo...
I am working with a set of what is essentially Attribute/Value pairs (there's actually quite a bit more to this, but I'm simplifying for the sake of this question). Effectively you can think of the tables as such: Entities (EntityID,AttributeName,AttributeValue) PK=EntityID,AttributeName Targets (TargetID,AttributeName,AttributeValue) P...
I need to query the database to get the column/field names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would want to retrieve those field names from the query and nothing else. Please help? Thanks in advance. ...
I'm trying to implement the "blog this" function from Flickr using the BloggerAPI to my pl/sql based CMS. When Flickr sends me the posting transaction, the HTTP transaction looks like this: POST /pls/website/!pkg.procAPI HTTP/1.1 Host: www.mydomain.com Accept: */* User-Agent: Flickr Content-Type: text/xml; charset=utf-8 Content-Length:...