Are there non obvious differences between NVL and Coalesce in Oracle? The obvious differences are that coalesce will return the first non null item in it's parameter list whereas nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second. It seems that NVL may just be a 'Base Case" version of...
Hi, To put it in a nutshell - In an oracle db, I want to make a column varchar2(16) which is now varchar2(8), without affecting the values presnet. I have already tried this and it does weird things. The query I tried was - alter table SOME_TABLE modify (SOME_COL varchar2(16)); But the values(some not all) already present in the table ...
Problem: I have a large database table (~500k records) which has a list of dates stored in a varchar2(15) column. These dates are stored in varying formats, ie. some are yyyy-mm-dd, some are mm/dd/yyyy, some are dd/mm/yy, some are mm/dd/yy, etc. Ie: 1994-01-13 01/13/1994 01/13/94 13/01/94 13/01/1994 etc I need to be able to shift thes...
Now I know this has been asked before. But I've made doubly sure that all parameters are correct and there are no typos. I still get the error. Could somebody please guide me here? I am nearing my wit's end! CREATE OR REPLACE PROCEDURE VWT.WUA_DELETE_FP_REQUEST (i_pLDAPUserName IN varchar2, i_pIReasonCode IN number, i_p...
I know with some connection pools, you can specify a test query the pool manager can use to validate a connection. Is there anything similar to that for asp.net? I'm having a problem where there's a firewall between the web app and the database, and it's closing idle connections - the result is that the user gets an "ORA-03135: connect...
I want to substract exactly 6 month from a given date. How do you do this in Oracle SQL? ...
Hi, I have a stored procedure that takes in two dates and passes back a cursor. The stored procedure compiles however I am having trouble writing the correct pl/sql to 'see' what is returned. I have tried the following: DBMS_OUTPUT.PUT_LINE('MY_CURSOR=' || MY_CURSOR) declaring local variables that represent the columns contained in ...
In Oracle, is there a function that calculates the difference between two Dates? If not, is a way to display the difference between two dates in hours and minutes? Check query below. SELECT Round(max((EndDate - StartDate ) * 24), 2) as MaximumScheduleTime, Round(min((EndDate - StartDate) * 24), 2) as MinimumScheduleTime, ...
What does the warning mean? Why is the second example worse than the first? SELECT product_id, prod.name name, sample_id FROM lims.sample JOIN lims.product prod USING (product_id) vs. SELECT product_id, prod.name name, sample_id FROM (SELECT sample_id, product_id FROM lims.sample) JOIN lims.product prod /* ADV...
I'm working on an ASP.NET project with an Oracle Database. We are using TOAD to add/manage the stored procedures -- and in general I like TOAD and Oracle. The one thing I've found frustrating is finding a simple way to test an Oracle Stored Proc, such as SQL Server's "exec [SP_NAME] Param1, Param2, ParamN" syntax. All of our stored proc...
I need to create a linked table from MS Access to a view in Oracle. I am creating the linked table from VBA using the following DoCmd.TransferDatabase acLink, "ODBC Database", [ODBC connect stuff], _ acTable, mySourceView, myDestinatonTable, , True This is working and creates a link to the Oracle view. However...
I'm working on importing data from our application into Oracle Financials GL. It seems simple with the GL_INTERFACE table, and many resources online, but I don't seem to understand it. A powerpoint presentation An import API I'm looking for a simple way to post a transaction of $X on a specific date, between 2 or more accounts. I'm ...
How can I write a log for sharepoint BDC List webpart? I want to know that which sql query bdc list webpart send to the database. Like I am filtering webpart on "Test". So, what would be the sql query? Either SELECT * FROM TABLE WHERE A='TEST' OR SELECT @ FROM TABLE? Is there any way to check what request sharepoint send to my Oracle da...
I was Wondering if there is anyway of suspending / resuming weblogic 10 jdbc datasources via the command line. I am aware that i can do this in the admin console, but because our app has many different datasources it is a bit of a pain. The reason behind this is that our testers are doing error flow tests and have to simulate the db goi...
What SQL would I need to use to list all the stored procedures on an Oracle database? If possible I'd like two queries: list all stored procedures by name list the code of a stored procedure, given a name ...
Hi, I want to load data into text file that is generated after executing "views" in Oracle?How can I achieve this in oracle using UNIX.for example- I want the same in Oracle on unix box.Please help me out as it alredy cosume lots of time. your early response is highly appreciated!! ...
I'm trying to use the schema update feature in nHibernate against an Oracle 11g database and it's throwing the following exception: "More restrictions were provided than the requested schema ('Tables') supports." It appears to be an ado.net exception, but I can't find much information for it. Any insight towards this exception would be...
How to get the output of a query into a flat file using Oracle on UNIX? For example: I have a TEST table; I want to get the content of the TEST table into a flat file and then store the output in some other folder in .txt format. ...
Doesn't have to be the same syntax, but same or similar functionality. Please provide the name used for the construct and a simple example. ...
Hi all, I want to restore and recover my oracle database to the current state. For example, I have a full backup on Sunday and I have all archive logs from Sunday to Wednesday. If I restore and recover from backup to the new host, my database is in the state of Sunday only. How can I apply all archive logs I have to recover my databas...