Is there a way to have a common operator for concatenation in Oracle, Postgres and SQL Server. In Oracle we uses '|', postgres uses '||' and sql server uses '+'. I've solved the problem in postgres by adding the custom operator '+' to support string concatenation. Is there a way to add the same operator in Oracle to support string con...
In an Oracle 10 database, I did a small test: SELECT REGEXP_REPLACE('İF', '[Iİ]F', 'DONE', 1, 0, 'i') FROM dual; This doe not seem to match the regex. However, when I remove the last parameter (case insensitive regex parameter), regex matches SELECT REGEXP_REPLACE('İF', '[Iİ]F', 'DONE', 1, 0) FROM dual; Below queries also returns "DO...
I have an SQL 2005 SSIS package that takes data from an Oracle DB Table, and transfers it to a SQL Server Table. I have set up an "Oracle Provider for OLE DB" for the Oracle connection and a "SQL Native Client" for SQL Server Connection. The Oracle and SQL connections will depend on the development and shipping stage, which are: Loca...
I wrote a PL/SQL script to set a sequence's value to the maximum value of a table's primary key: DECLARE max_idn NUMERIC(18, 0); seq_nextval NUMERIC(18, 0); increment_amount NUMERIC(18, 0); BEGIN SELECT MAX(mbr_idn) INTO max_idn FROM mbr; SELECT mbr_seq.nextval INTO seq_nextval FROM DUAL; increment_amount := max_id...
I am working with your typical three layered application that has a presentation layer, a business layer, and a DAO layer. It is a Java web application that uses Spring MVC, Spring Security, and Spring's transaction management from the business layer down. It is communicating with an Oracle 10g database. I have a business layer method ...
I am trying to create a unique constraint on two fields in a table. However, there is a high likelihood that one will be null. I only require that they be unique if both are not null (name will never be null). create unique index "name_and_email" on user(name, email); Ignore the semantics of the table and field names and whether tha...
Recently I was reading about Codd's 12 Rules, and I understood all except number 12, the 'non-subversion' rule. If anyone can explain me the rule (using an example, preferably), that would be very helpful. Thanks. ...
Hello, I've been trying without success to add a reference to a public synonym in a package or stored procedure in oracle for a while, and I'm wondering if there are solutions to this problem short of accessing the tables directly. For instance: CREATE OR REPLACE PROCEDURE test_func AS test_int INTEGER;<br> BEGIN<br> select coun...
Does LINQPad work with Oracle? Has anyone tried? What do you suggest? ...
Hi, I am currently tracking connection issues with jinitiator but have limited experience with this technology. I want the applet to connect via a proxy, all is good at first. Console Output: Oracle Jinitiator: Version 1.3.1.13 User has overriden browser's proxy settings Proxy Configuration: Manual Configuration Proxy 192.168.1.230:3...
I'm using Oracle SQL Developer to query an Oracle DB (not sure which version it is) and I'm going to use the SQL I make for a Crystal report. Many of the reports the previous developers have written don't use JOIN keywords to make the joins (and I'm not too familiar with JOIN keywords as a result). Many of the joins they make are mad...
Does anyone know of a good link that has a listing of the Oracle specific datatypes for a beginner? Thanks! ...
Hello. I wonder how could i print a row number for sql statement where is using order. Currently i tried ROWNUM but as i understand it works only for unsorted result set. SELECT rownum, a.lg_id, a.full_name, a.sort_order FROM activity_type_lang a where a.lg_id = 'en' order by a.full_name; TIA ...
I can understand that many years ago there would be this kind of limitation, but nowadays surely this limit could easily be increased. We have naming conventions for objects, but there is always a case that turns up where we hit this limit - especially in naming foreign keys. Does anybody actually know why this isn't a bigger size - or ...
I have problem with Oracle 9.2 and JMS. I created PL/SQL routine to send XML text (from file or CLOB) to queue, but this routine do not compile. My code looks like (filling message omitted): create or replace procedure jms_test(msg varchar2) is id pls_integer; message sys.aq$_jms_stream_message; ...
I have a table which has several one to many relationships with other tables. Let's say the main table is a person, and the other tables represent pets, cars and children. I would like a query that returns details of the person,the number of pets, cars and children they have e.g. Person.Name Count(cars) Count(children) Count(pets) J...
I have a large table, 1B+ records that I need to pull down and run an algorithm on every record. How can I use ADO.NET to exec a "select * from table" asynchronously and start reading the rows one by one while ado.net is receiving the data? I also need to dispose of the records after I read them to save on memory. So I am looking of a ...
By default Oracle uses indexes created. When I change to NLS_COMP=Linguistic and NLS_Sort=Binary_CI, I get full table scans. I'd read somewhere that creating an index using (nlssort(name, 'NLS_SORT=BINARY_CI')); Would work. As my attempt below shows, not so much. Even if I force it, the performance does not seem to be what I would e...
When writing a row-level trigger in Oracle, I know that you can use the OLD and NEW pseudo-records to reference the old and new state of the row that fired the trigger. I know that in an INSERT trigger OLD doesn't contain any data, but I'm not sure how this affects the evaluation of a WHEN clause for that trigger. For example, if I have ...
So let's say I have a few million records to pull from in order to generate some reports, and instead of running my reports of the live table, I create a temp where I can then create my indexes and use it for further data extraction. I know cached tables tend to be quicker / faster seeing as the data is stored in memory, but I'm curi...