I knew stackoverflow would help me for other than know what is the "favorite programming cartoon" :P This was the accepted answer by: Bill Karwin Thanks to all for the help ( I would like to double vote you all ) My query ended up like this ( this is the real one ) SELECT accepted.folio, COALESCE( inprog.activityin, accep...
Has anybody been successful in integrating the Enterprise Library v4.0 with SharePoint WSS 3.0? I created a very simple .ASPX page. It's only purpose will to be to connect to an Oracle database and display some values in a DropDownList. But right now, all it does is displays Hello World. I've added the necessary references and everyt...
What are some useful Oracle optimizations one can use for an Application that mostly writes (updates) to an Oracle database? The general usage pattern here is not web-serving or logging, as is most cases, but instead to persist complex state of a system, so the only times reading is needed is when the system starts up, after that its u...
When you create a procedure in Oracle PL/SQL, you cannot specify the maximum length of the varchar2 arguments, only the datatype. For example create or replace procedure testproc( arg1 varchar2 ) is begin ... ... end; Do you know the maximum length of a string that you can pass as the arg1 argument to this procedure in Oracle 10g ...
At the moment I pull data from remote MS SQL Server databases using custom-built JDBC connectors. This works fine but doesn't feel like the way to do it. I feel I should be able to put a JDBC connection string into tnsnames on the server and have it "just work". I've looked around a little for this functionality but it doesn't seem to b...
How is Oracle date implemented? Is it stored as milliseconds or something like that? ...
I need to grant select permission for all tables owned by a specific user to another user. Can I do this with a single command along the lines of: Grant Select on OwningUser.* to ReceivingUser Or do I have to generate the sql for each table with something along the lines of: Select 'GRANT SELECT ON OwningUser.'||Table_Name||'TO Rec...
I'm trying to speed up a data load which is controlled via a PL/SQL stored procedure. I've programmatically altered the indexes for the table I want to refresh to be unusable. I want Oracle to ignore these unusable indexes. I can issue the statement... ALTER SESSION SET skip_unusable_indexes = TRUE ...but I subsequently get the error...
Howdy. Consider the following: SQL> DECLARE 2 b1 BOOLEAN; 3 b2 BOOLEAN; 4 FUNCTION checkit RETURN BOOLEAN IS 5 BEGIN 6 dbms_output.put_line('inside checkit'); 7 RETURN TRUE; 8 END checkit; 9 10 PROCEDURE outp(n VARCHAR2, p BOOLEAN) IS 11 BEGIN 12 IF p THEN 13 ...
I've tried this, but it doesn't work: col * format a20000 Do I really have to list every column specifically? That is a huge pain in the arse. ...
Using the following query and results, I'm looking for the most recent entry where the ChargeId and ChargeType are unique. select chargeId, chargeType, serviceMonth from invoice CHARGEID CHARGETYPE SERVICEMONTH 1 101 R 8/1/2008 2 161 N 2/1/2008 3 101 R 2/1/2008 4 101 R 3/1/2008 5 101 R 4/1/2008 6 101 R 5/1/2...
Hi Guys, We're running a java/hibernate app going against ORACLE 10g in TESTING. Once in a while, we're seeing this error(ORA-00942: table or view does not exist). Is there a way to find out which table/view(s) ORACLE is talking about ? I know that I can add extra levels of logging in hibernate which will show all the SQL that it execu...
I'm no DBA, and I'm having a bit of a hard time understanding Oracle's transaction management process. From what I understood by reading some reliable-looking pages on the Internet (most notably this AskTom note -- but don't bother with the comments), when a transaction is commited, the new data is not reported on the actual data block ...
I wish to search a database table on a nullable column. Sometimes the value I'm search for is itself NULL. Since Null is equal to nothing, even NULL, saying where MYCOLUMN=SEARCHVALUE will fail. Right now I have to resort to where ((MYCOLUMN=SEARCHVALUE) OR (MYCOLUMN is NULL and SEARCHVALUE is NULL)) Is there a simpler way of sa...
This is from the Stream - AQ docs. You can register system events, user events, and notifications on queues with Oracle Internet Directory. System events are database startup, database shutdown, and system error events. User events include user log on and user log off, DDL statements (create, drop, alter), and DML statement triggers....
I’m running into problems using MSDTC and Oracle. It’s a .net application and I’m using the TransactionScope class to control the transactions. The problem is that, sometimes, if the transaction is rolled back (scope.Dispose is called without having called scope.Complete), it stays in “Aborting” state for a long time, not releasing the ...
I am using Oracle SQL (in SQLDeveloper, using the SQL Worksheet). I would like to print a statement before my select, such as PRINT 'Querying Table1'; SELECT * from Table1; What do I use to Print / show text output? It's not Print, because that gives me the error: Bind Variable "'Table1'" is NOT DECLARED. DBMS_OUTPUT.PUT_LINE is an...
I'm sorting out a series of SQL scripts for my company written in Oracle PL/SQL. I came across an essential script with a strangely placed slash near the bottom. It is checked into CVS this way. Is this a pure syntax error or does it have some function I'm not aware of. The slightly obfuscated script: set serveroutput on size 2000; ...
Is there some general guidelines online on how to tweak oracle for doing a high number of inserts and low number of reads? All the answers below are pretty good recommendations. I have to clarify the following things. I am using 10g and this is an absolute requirement that we use Oracle. I am also more interested in oracle instance para...
Does anyone knows of an automated tool that works for migrating table structure and data? The tool has to resolve concerns such as transforming PostgreSQL fields on cases where a direct equivalent isn't available. I.e, a field type like boolean being used. ...