Informix timestamps in Oracle (via ODBC)
I need to select some values from an Informix database via Oracle ODBC. One of the columns is a timestamp, and when I just select it all I see in SQL*Plus is the date value. How do I get the time as well? ...
oracle
What is the best (fastest, easiest, most efficient) wayt to pass structured data into and out of Oracle
This is targeted at Technical Leads and Architects, but Anyone can answer this - all thoughts are welcome - have fun with it. I want to abstract a large database store (relational, OLTP) from the application layer by having the application consider the database as a service. The natural way of doing this is by calling sprocs, but tradit...
Running Oracle stored Procedure
I have a sql file that contains a simple procedure to print "Hi" like, CREATE OR REPLACE PROCEDURE skeleton IS BEGIN DBMS_OUTPUT.PUT_LINE('Hi'); END; When I try to execute this from sql file itself, it just gets compiled and it is not running. I added the as, CREATE OR REPLACE PROCEDURE skeleton IS BEGIN DBMS_OUTPUT.PUT_LINE(...
Prepared statement - using a function as part of the where clause
I am working with a Java prepared statement that gets data from an Oracle database. Due to some performance problems, the query uses a "virtual column" as an index. The query looks like this: String status = "processed"; String customerId = 123; String query = "SELECT DISTINCT trans_id FROM trans WHERE status = " + status + " AND FN_GE...
Pro*C passing a dynamic array to a PL/SQL procedure
I want to call a PL/SQL stored procedure from Pro*C which takes an array parameter (table of integer). All the documentation on the Oracle site assumed you are using a static array, or at least one defined in the same procedure as the query. I want to pass an array to a C function, that can then be sent up to the database. The problem he...
ora-00979 not a GROUP BY expression
PL/SQL is not my friend recently. I am getting the above error with the following query: SELECT cr.review_sk, cr.cs_sk, cr.full_name, tolist(to_char(cf.fact_date, 'mm/dd/yyyy')) "appt", cs.cs_id, cr.tracking_number from review cr, cs, fact cf where cr.cs_sk = cs.cs_sk and UPPER(cs.cs_id) like '%' || UPPER(i_cs_id) || '%' and row_delete...
mangento in postgres or oracle database ?
hi, i need an open source ecommerce solution, i think magento is an excellent ecommerce solution but i can't use mysql because is a customer requirement and magento only offer mysql database support. any tips to setup magento in postgres or oracle ?. thanks in advance !!! ...
Does PL/SQL have an equivalent StringTokenizer to Java's?
I use java.util.StringTokenizer for simple parsing of delimited strings in java. I have a need for the same type of mechanism in pl/sql. I could write it, but if it already exists, I would prefer to use that. Anyone know of a pl/sql implementation? Some useful alternative? ...
How to select in select where the inner select is a substring of return result
This is beset illustrate by an example that I think should work but doesn't: select * from TABLE_A where KEY in ( select substr(DYNAMIC_KEY,3) from TABLE_B where DYNAMIC_KEY like '$$%' and RECORD_ID='104251893783388824'); Basically, the inner select statement return a set of result which had a '$$' prefix. This is use as a lookup ke...
oracle merge statement
The following statement is in a Stored Procedure MERGE INTO table1 a USING (SELECT a.ROWID row_id, RTRIM(NVL(c.address_line1_text, '')) || ' ' || RTRIM(NVL(b.unit_number, '')) address_line1_text, RTRIM(c.city_name) city_name, RTRIM(c.state_code) state_code, RTRIM(c.basic_zip_code) basic_zip_code, NVL(b.unit_number, ' ') unit_n...
SQL query challenge
So here's yet another 'write a query to X' challenge. I'm monitoring a number of networked vending machines. Each machine has a number of parts, e.g. bank note acceptor, coin system, printer and so on. Problems with machine parts are logged in table, let's call it 'faults', which looks something like this (irrelevant fields omitted): ...
What are good candidates for creating indexes on beside pk and unique columns?
I'm always asking myself should I create an index or not in my Oracle tables, what criteria in a table makes the index worth creating? And is it also a criteria in the query? And are there (with respect to Oracle) different kinds of indexes you can create based on some factor(s)? ...
Unable to access oci.dll due to application type.
Hello, I am using attempting to create a connection to an Oracle database via entity framework in Visual Studio 2008. I am running 64 bit Vista Ultimate. At first, I had difficulty even making a connection to the db through Visual Studio, but eventually got things working by installing 10204_vista_w2k8_x64_production_db AND ODTwithODAC...
Oracle DB (PL/SQL) Refactoring Tools
Hi, I was wondering if there are any good refactoring tools for Oracle databases and especially for PL/SQL. I'm working on a project where half the developers are working with c# and the other half on the db where there is a large and complex schema and a large code base in pl/sql. We also use sql server but the database team work exclus...
Have PL/SQL Outputs in Real Time.
Is it possible to have Outputs from PL/SQL in real time? I have a pretty huge package that runs for more than an hour and I'd like to see where the package is at a particular time. Anyways, I currently do this with a log table, which gets filled up with hundreds of log descriptions per run, I'm just curious if this is possible. Thanks!...
Asking for opinions : Accent marks / diacritics in primary key
I have this application that uses natural primary keys. The database uses the WE8ISO8859P15 character set. So in my table City whe have primary keys like 'MEDELLÍN' and 'MÜNCHEN'. I have a hunch we are going to have a lot of trouble with this. The problems I see Interfacing this data to databases with another character set. I don't ...
How to append system time to the file name in oracle?
Hi , I am very new to oracle.I need to create a file with the system timestamp from oracle. Please let me know how do i do that. More over I need to write any exceptions or errors thrown by my pl/sql code to a file and exit after an error. How do i do this? Thanks, Priya.R ...
Cannot executing a SQL query through ODP.NET - invalid character error
I'm trying to execute a SQL query through ODP.NET to create a table, but I always get an ORA-00911 'invalid character' error. The Errors object in the exception always has the text "ORA-00911: invalid character\n", even if there are no linebreaks in the SQL query itself. The code I'm executing the SQL is this: using (OracleConnection c...
Killing an Oracle job. 10g specific
We're using a job scheduling system that runs on top of DBMS_JOB. It uses a master job to create one-time jobs. We deploy the same set of jobs to all our clients, but can specify which jobs should only run at certain clients. We get occasional problems with a process run by a job hanging. The main cause of this is UTL_TCP not timing out...
Oracle CLOB performance
I am running queries against an Oracle 10g with JDBC (using the latest drivers and UCP as DataSource) in order to retrieve CLOBs (avg. 20k characters). However the performance seems to be pretty bad: the batch retrieval of 100 LOBs takes 4s in average. The operation is also neither I/O nor CPU nor network bound judging from my observatio...