We use MS Access as a front-end to Oracle tables, via ODBC, and it has been working well. But we're trying to use the DEFAULT constraint in an Oracle table. When we open the linked table in Access, we see existing data just fine, but when we try to add a row, without keying any value into column(s) that have an Oracle DEFAULT (expecting...
Hi, I'm storing a simple java.util.date in an Oracle XE database via hibernate. When testing with JUnit if I can retrieve the correct value, I get an error like this: junit.framework.AssertionFailedError: expected:<Sun Dec 28 11:20:27 CET 2008> but was:<2008-12-28 11:20:27.0> The value is stored in an Oracle Date column (which...
MERGE INTO PAGEEDITCONTROL A USING (SELECT '1585' AS PAGEID ,'admin' AS EDITUSER ,sysdate AS EDITDATE FROM DUAL) B ON (A.PAGEID = B.PAGEID) WHEN MATCHED THEN UPDATE SET A.EDITUSER = B.EDITUSER ,A.EDITDATE = B.EDITDATE WHEN NOT MATCHED THEN INSERT ( ...
Hello. I hope someone could help me on this. I want to add a month to a database date, but I want to prevent two jumping over month on those days at the end. For instance I may have: Jan 31 2009 And I want to get Feb 28 2009 and not March 2 2009 Next date would be March 28 2009 Jun 28 2009 etc. Is there a function that...
To help users, I would like my code to discover Oracle databases on the LAN. I thought to do this by first detecting all hosts, then checking each host to see if it is listening on Oracle's default port. Any ideas how to go about this? Preferably in Java, but any language or algorithm would do. ...
It seems vsize() and length() return the same results. Does anyone know of a practical example of when to use vsize instead of length? select vsize(object_name), length(object_name) from user_objects Result: /468ba408_LDAPHelper 20 20 /de807749_LDAPHelper 20 20 A4201_A4201_UK 14 14 A4201_PGM_FK_I 14 14 A4201_PHC_FK_I 14 14 ...
Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause? I have a comma delimited list of many of IDs that I want to use in an IN clause, Sometimes this list can exceed 1000 items, at which point Oracle throws an error. The query is similar to this... select * from table1 where ID in (1,2,3,4,...,...
I have a stored procedure in Oracle that returns a select statement cursor reference. I want to be able to pass column names and sort direction (example: 'CompanyName DESC') and be able to sort the results, or pass a filter such as 'CompanyID > 400' and be able to apply that to the select statement. what is the best way to accomplis...
When running a query like "insert into table " how do we handle the commit size? I.e. are all records from anotherTable inserted in a single transaction OR is there a way to set a commit size? Thanks very much, ~Sri PS: I am a first timer here, and this site looks very good! ...
I have a coworker looking for this, and I don't recall ever running into anything like that. Is there a reasonable technique that would let you simulate it? SELECT PRODUCT(X) FROM ( SELECT 3 X FROM DUAL UNION ALL SELECT 5 X FROM DUAL UNION ALL SELECT 2 X FROM DUAL ) would yield 30 ...
Do you use any free tool to generate query for oracle database. Tool that autocomplete and suggests the table names and column names. ...
I can write: update my_table set xml = updateXML(xml, '/a/b', '1') where document_id = 123 Now what if in the same update query I also want to set /a/c to 2 (in addition /a/b to 1)? I am tempted to write: update my_table set xml = updateXML(xml, '/a/b', '1'), xml = updateXML(xml, '/a/c', '2') where document_id = 123 But th...
When I try to create a trigger in schema A for a table located in schema B, I get an ora error : insufficient privileges. What privileges do I need? ...
We've a table with a varchar2(100) column, that occasionally contains carriage-return & line-feeds. We should like to remove those characters in the SQL query. We're using .. REPLACE( col_name, CHR(10) ) .. which has no effect, however replacing 'CHR(10)' for a more conventional 'letter' character proves that the REPLACE function work...
In Oracle SQL Developer, one can list the data in a table using the Data tab when viewing a table. There is also a 'Sort...' button to set the sort order of the data you are viewing. This can be very handy for viewing some data on the fly. The problem: I set a sort order for viewing a particular table which is not supported by the inde...
We are using an oracle database in a project. Most of the tables represents classes or objects in the application. The application currently doesn't have a substantial amount of documentation. I am using StarUML to make up some class diagrams and such for other developers on the project to increase their understanding of the overall p...
I needed to change the SID of an Oracle XE database (not the Service Name) to match a production database. When I tried searching online, most of the pages were describing changing or adding a service name through tnsnames.ora; that's not what I needed to do. ...
I have the following Oracle function: function get_job_no return number is V_job_no number; begin select appwork.tlm_corphier_job.nextval into V_job_no from dual; return V_job_no; end get_job_no; PLEASE NOTE: 1) This is a FUNCTION, not a procedure 2) This is returning a NUMBER, not a VARCHAR 3) I ...
I see Oracle has a comprehensive Integration Repository online (http://irep.oracle.com/). Question is... how can one call these procedures? Do I have to do it through PL/SQL? Or do all these functions/procedures have corresponding web services exposed automatically? ...
In an Oracle 10g environment, I have a statement that needs to be executed several million times based on the results of a cursor. For flexibility, the current code has the statement as a constant in the package body. Here is a simplified version of the code. Please keep in mind this is all within the same package: c_Stmt CONSTANT VARC...