oracle

referential integrity constraint is automatically disabling in oracle.

when am going to insert data into a child table for which i have added constraint(foreign key) the constraint is automatically disabling. can u please help me.......... ...

Map-like object type in PL/SQL?

Hi there, I want to write a map-like object type in PL/SQL. What I mean is a key-value-pair list, where a value can be another key-value-pair list. Simple, or so I thought. Here are the two simplified CREATE OR REPLACE TYPE TKey AS OBJECT ( name varchar2(240), value_text varchar2(2000), value_map TMap ) CREATE OR REPLACE T...

Help with SQLPLUS please? How to make SQLPLUS startup with DEFINE `OFF` initially?

I have a batch script that invokes PLSQL with connection details, which works fine but I still have to explicitly SET DEFINE OFF when I connect. I would like to enhance my simple batch script to pass the SET DEFINE OFF command to SQLPLUS so that once I am connected, I will no longer have to issue that command manually. echo set define o...

ORM for ASP.net, ORACLE, looking for a solution.

I'm fairly new to trying ORM solutions, but I'm trying to come up with some sort of solution to create objects from our existing database model in ORACLE. I've been looking at ORM solutions, but I'm not sure I have a grasp as to what exactly they do. Can someone explain what ORM solutions can do more clearly? Some of the options they ha...

Adding Oracle ASM disk group

I am attempting to install an Automatic Storage Management instance on an Oracle 10g install on Windows Vista. I have tried using manual ASM addition via asmtoolg and also via the interactive DBCA gui. I have created and stamped 4 2GB partitions, but ASM will not recognize these and allow me to create a disk group. I'm stuck and canno...

Oracle OCIEnvNlsCreate error when connecting from Windows service

As noted by the question's title, I'm trying to connect to an Oracle database from a Windows service and I am getting the following error message: System.Exception: OCIEnvNlsCreate failed with return code -1 but error message text was not available. Other applications on the same computer are connecting to Oracle without any issue...

How to obtain the list of Oracle's SIDs

I have the host, port, user id and password but missing SID for connecting to Oracle DBMS. How can I find the list of SIDs on that server? ...

Oracle: loading a large xml file?

So now that I have a large bit of XML data I'm interested in: http://blog.stackoverflow.com/2009/06/stack-overflow-creative-commons-data-dump I'd like to load this into Oracle to play with. How can I directly load a large XML file directly into Oracle? Server-side solutions (where the data file can be opened on the server) and client...

Deferrable Constraints in SQL Server

Do any versions of SQL Server support deferrable constraints (DC)? Since about version 8.0, Oracle has supported deferrable constraints - constraints that are only evaluated when you commit a statement group, not when you insert or update individual tables. Deferrable constraints differ from just disabling/enabling constraints, in that...

How can I optimize a dynamic search query in Oracle

I am writing a stored procedure to perform a dynamic search that spans 10+ database tables. With millions of records in each table and a dynamic set of search parameters*, I am having some trouble optimizing the procedure. Is there a "best practice" for building these kinds of queries? E.g. Use strings to build a dynamic query, use a h...

Does installing Visual Studio change garbage collection in .Net?

I'm trying to debug some resource leaks - oracle connections specifically. On my local machine, as I step through the Page_Load(), I can see the connections created in the db by monitoring v$session. As soon as I step out of Page_Load, all the connections are closed. Same code running on the dev server (W3k, IIS6) does NOT release the...

Update the results of a SELECT statement

Oracle lets you update the results of a SELECT statement. UPDATE (<SELECT Statement>) SET <column_name> = <value> WHERE <column_name> <condition> <value>; I suppose that this could be used for updating columns in one table based on the value of a matching row in another table. How is this feature called, can it efficiently be used fo...

OracleClient command - best practices around USING and disposing of command

According to the MSDN documentation for the OracleClient.OracleCommand: Public Sub ReadMyData(ByVal connectionString As String) Dim queryString As String = "SELECT EmpNo, DeptNo FROM Scott.Emp" Using connection As New OracleConnection(connectionString) Dim command As New OracleCommand(queryString, connection) con...

Can I get ORA-08177 if there's only one connection to the database?

I've been tasked with running the unit tests on a storm backend for oracle so that we can see if the backend is of sufficient quality to use in production. One problem that I'm running into is that I'm getting ORA-08177 (can't serialize access for this transaction) if I connect in serializable mode. The problem goes away when I use rea...

Oracle 'printf' equivalent

Is there an equivalent or alternative to the following? SELECT mix_type || ' (' || mix_num || ')' as description FROM acid_batch WHERE mix_num < 10 Does Oracle have something like printf style formatting? SELECT printf("%s (%s)", mix_type, mix_num) as description, FROM acid_batch WHERE mix_num < 10 ...

Oracle 10g PL/SQL- Select results as update column values

Is there a way to easily/elegantly update multiple columns in a table with record values from a query in Oracle 10g? I have a table (call it Source) which has for simplicities sake, 2 columns, ID and KEY. ID Key ---- ---- 1 1000 2 1000 3 5000 4 1000 .. 101 8000 102 9000 103 7000 104 ...

Obtain id of an insert in the same statement

Hello Is there any way of insert a row in a table and get the new generated ID, in only one statement? I want to use JDBC, and the ID will be generated by a sequence or will be an autoincrement field. Thanks for your help. John Pollancre ...

System.Data.OracleClient namespace discontinued?

I just read this article today about how Microsoft decided to remove the System.Data.OrcaleClient namespace from .NET 4.0 library. I used this as my primary tool for working with Oracle databases. When I decide to upgrade my applications from the .NET 2.0 to .NET 4.0, what would bee some alternative options for working with Oracle data...

Working with date ranges in PHP/Oracle

Hi guys, I need to work out how many different instances occur on a different day, from many different ranges. Probably best to explain it with an example. 18-JAN-09 to 21-JAN-09 19-JAN09 to 20-JAN-09 20-JAN-09 to 20-JAN-09 Using the three examples above, I need it to collect this information and display something a little like... 1...

How to install Java class on Oracle on Solaris

I have simple java class that calculates SHA1 and I want to use it from PLSQL procedures in Oracle. Following advises from : SHA1, SHA256, SHA512 in Oracle for free without using DBMS_CRYPTO (my simple case use unly SHA1 and do not need any jars and consist of only sha1.class that imports java.security and java.io) I compiled sha1.java ...