As a part of our build process we want to execute SQL Scripts consisting of DDL and DML statements against a fresh database instance. ADO.NET Connection/Command can't handle this without parsing and splitting the scripts. The sqlplus command line utility can execute scripts only interactively, and isn't suited for batch usage. What a...
Hi, I'm testing something in Oracle and populated a table with some sample data, but in the process I accidentally loaded duplicate records, so now I can't create a primary key using some of the columns. How can I delete all duplicate rows and leave only one of them? This is in Oracle Thanks ...
I have a query that I am generating the XML from in Oracle using the DBMS_XMLGEN package. As an example, I am using a cursor as follows: SELECT A.NAME primaryName, (CURSOR(SELECT B.NAME AS NAME FROM B WHERE B.ID=A.ID)) SYNONYMS FROM A I have a query context, qtx, and set the query to the above for that context. Calling: res...
I've got an Oracle 10g database, and I have a third-party jar file. I want to be able to run a SQL select query in my database that ultimately runs code in my third-party library to retrieve info for inclusion in a SQL result set. I see lots of tutorials on "Java stored procedures" and these seem to be a promising way to do this, but n...
In MySQL, I can create an access a session variable by using a single @. Example initialization: set @myVar = true; Some trigger containing this code: if (@myVar is not true) then execute something What is the equivalent in Oracle 10g? ...
Where is the best place to find custom compnonents? Ideally a repository, as opposed to finding a few here and there. We are currently about to re-design our look and feel UI for about 200 forms and we were hoping to find a lot of custom components... We were told they were everywhere by the pre-sales architect but even a simple google s...
I'm trying to return sequence.nextval to my program from a proc stored in a package. I'm pretty green when it comes to PL/SQL and I'm kind of at a loss as to what is happening. The error that is actually being return is PLS-00306: Wrong number or types of arguments in call to PROCGET_BOOKMARKID line 1, column 7 statement ignored. He...
My database background is mainly Oracle, but I've recently been helping with some SQL Server work. My group has inherited some SQL server DTS packages that do daily loads and updates of large amounts of data. Currently it is running in SQL Server 2000, but will soon be upgraded to SQL Server 2005 or 2008. The mass updates are running ...
Most of the tables (if not all) have a "blob" field in it. One of the table that stores the Logs of the user's action has now grown to 8 GB (about 5 million records). And our DBA has seen that this schema is now eating space exponentially. We investigated a bit and found out that there is one of the tables with (SYS_LOB) which takes ar...
Hi I'm writing a GUI for using the oracle exp/imp commands and starting sql-scripts through sqlplus. The subprocess class makes it easy to launch the commands, but I need some additional functionality. I want to get rid of the command prompt when using my wxPython GUI, but I still need a way to show the output of the exp/imp commands. ...
I installed Oracle 10g express. Installation went well, But the home page is not coming up. I can connect using sqlplus but not with Sql Developer. I checked tnsnames.ora and listener.ora everything looks fine. Listener is also started. I can always reinstall and see. But I thought better inquire the cause. Regarding the home page. Thi...
If I set a primary key in multiple columns in Oracle, do I also need to create the indexes if I need them? I believe that when you set a primary key on one column, you have it indexed by it; is it the same with multiple column PKs? Thanks ...
OK, the auditors have come knocking and I know one of their standard "exposures" is if your application allows concurrent logons by the same user. Let's put aside the quantification of that risk for a minute... The application I'm working with uses Oracle SSO for managing authentication, and as far as I know, it does not have an out-of...
Hi! I'm looking for the best way to store XMLs in a Oracle Database. Currently we use a BLOB type column but I'm pushing to move this column to a XMLType or an XMLType schema based column. I've just found this article XML: To CLOB or Object? from Oracle and read it but I haven't made up my mind yet. So has anybody experienced this same ...
I am trying to find out just how much temporary space each session connecting to my database is using, but I've not been able to figure it out. I am trying to determine if the number of extents in use by a particular session are overly high. I tried looking at V$TEMP_EXTENT_POOL and V$TEMP_EXTENT_MAP, but cannot seem to find anything wh...
I have a complicated HQL query. I would like to access the Oracle specific rownum column value as part of my returned results. How do I write my query (and/or change my hbm.xml) to support this? What I have tried so far does not work: modifying my hbm.xml <property name="rownum" type="int" update="false" insert="false" generated="ne...
After quite some time developing with Oracle Client 9.2.0.7, and the ODP.NET to go with it, targeting .NET 1.1, we are upgrading our code line to .NET 3.5 and we are also considering upgrading the Oracle Client version. I wonder if there are any "gotchas" as to whether specific versions should be used/not used? For example Oracle Clien...
In Oracle 10g, I have this SQL: select dog.id as dogId from CANINES dog order by dog.codename asc which returns: id -- 204 203 206 923 I want to extend this query to determine the oracle rownum of a dog.id in this resultset. I have tried select rownum from (select dog.id as dogId from CANINES dog order by dog.codename asc) wher...
I'm looking for a performance tool for testing of load in terms of data and number of users. This tool must support Oracle and MySQL. PS: I've tried DBtuna and Spotlight. ...
Hi! I'm trying to log all the errors in my database into a table. So as user sys i wrote the following code: CREATE TABLE servererror_log ( error_datetime TIMESTAMP, error_user VARCHAR2(30), db_name VARCHAR2(9), error_stack VARCHAR2(2000), captured_sql VARCHAR2(1000)); / CREATE OR REPLACE TRIGGER...