I currently have a Database Command object in my Crystal Report that looks something like SELECT * FROM table WHERE field = {?EndDate} I want to change it so it looks more like IF {?EndDate} = DATE '1900-01-01' MyVariable = ADD_MONTHS(LAST_DAY(SYSDATE), -1) ELSE MyVariable = {?EndDate} SELECT * FROM table W...
I'm calling an Oracle Stored procedure which takes an array as an input parameter. Owa.vc_arr(varchar2) I'm making a call in php and getting an error. Any help would be greatly appreciated. Code which i'm using is given below. function findSupplier($ptype) { $proCall = 'BEGIN ICPISSAAPILIB.FIND_SUPPLIER(:P_PRODUCT_TY, :P_RESULTS); E...
I want to know why Oracle needs the size parameter in the definition of the varchar2. I think that is for constraint. Would it be a better option that oracle takes this parameter as an optional? I often have problems resizing old tables to largers sizes, because sometimes a value is bigger than the size definition of the varchar2 colu...
Given (ignore the lack of primary keys, foreign keys, etc - this isn't about table design and is just an example): Order: ---------- ID NUMBER; VENDOR NUMBER; PART NUMBER; Parts: ------------ ID NUMBER; VENDOR NUMBER; DESCRIPTION VARCHAR2(1000 CHAR); cursor c1 is select * from order o left join parts p on o.part = p.id; c_row c1%rowt...
Hi, I'm using a php script to save a binary stored in the server's temp directory into oracle as a blob. It works fine for filesizes < 7MB, any larger than that and it just doesn't save. The php post and file upload limit is 32MB. The script stops completely at lob->savefile($tempFile). The general code is below: $stid = oci_parse($con...
I'd like to loop through a list of strings and execute a function/procedure with each string as the argument. What's the best alternative to the following generic code (since it's not legal): set serveroutput on; begin FOR r IN ('The', 'Quick', 'brown', 'fox') LOOP dbms_output.put_line( r ); END LOOP; end; I assu...
I'm evaluating TeamCity, and am using it with HSQLDB. Now, I'm ready to use an external database, and I have the option to use either Oracle or SQL Server. Everything being equal, Oracle is the preferred option due to corporate policy. Which database will give me the smoothest experience together with TeamCity? ...
Hello everyone I want to start a test driven development with ASP.Net 3.5 (C#), NHibernate with Oracle and NUnit. Can any body refer me any tutorial about NHibernate with NUnit for beginning. Any book reference will also be appreciated. N.B. I have seen the summer of Nhibernate video series, but it works for SQL server only not oracle...
I'm trying to implement a front end for a reporting solution which is security dependent. The user has 12 levels of nested criteria to select from, the value of each affects all of the values below. So the criteria selection on the page (each is a dropdown) looks something like this: Criteria 1 Criteria 2 ... Criteria 12 There is a...
I'm facing a very complicated problem in my application that is solved when I update from toplink 10.1.3 to toplink 10.1.3.4. Now I must find out which resolved issue (list available here http://www.oracle.com/technology/products/ias/toplink/doc/10131/relnotes/rel_notes.htm#BABBHCAA) is responsible for the solution, so I can convince the...
How can i append a whitespace character- for example   /   (non-breaking space) to a field value querying oracle server? I tried SELECT myfield || ' ' FROM mytable but this is interpreted as a variable with the name #160. ...
How do i list all tables in a schema in Oracle SQL? ...
I have two procedures A and B. Procedure A performs certain tasks. Procedure B has to monitor how many times procedure A is called in a day. How to achieve this? ...
I'm trying to do a SELECT INTO using Oracle. My query is: SELECT * INTO new_table FROM old_table; But I get the following error: SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword" Any ideas what's wrong? Thanks! The Standard behavior of the above should be as I originally thought: However Oracle implemented...
This is driving me insane, can anyone help me understand why the following statements all return the following error? create table JMS_PENDING_MESSAGE (id number primary key, queuex nvarchar2(200), messagex nclob(1000)); create table JMS_PENDING_MESSAGE (id number primary key, queuex nvarchar2(200), messagex nclob(10000)); create table ...
What's a good off-the-shelf tool that can be used to perform replication between mysql and oracle in both directions? We're currently looking at DBMoto which is super, but I was hoping there was something less expensive. The requirements are: Easy to configure and maintain through a GUI. Table-level replication selection, with configu...
I have a number of Oracle triggers stored in a file which we upload to our DB using sqlplus. We want to use liquibase instead to manage this, but I don't really want to split out the triggers into separate files. The file looks like: create or replace trigger Trig1 ... ... end Trig1; / create or replace trigger Trig2 ... ......
I need to connect to two Oracle AQ queues in different databases. I'm using exactly similar code in both cases, except for the connection string of course. One of the queues is working fine, but the second one not. String db_string = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.y.z.2)(PORT=1521))(ADDRESS=...
Background Information Ordinal position notation, AKA ordinals, is column shorthand based on the column order in the list of columns in the SELECT clause, instead of either the column name or column alias. Commonly supported in the ORDER BY clause, some databases (MySQL 3.23+, PostgreSQL 8.0+) support the syntax for the GROUP BY clau...
I'm having a problem running this query: DELETE FROM [IRPROD]..[BUDGET_USER].[GL_EXP] WHERE FISCAL_YEAR = 2010 IRPROD is a linked server to an Oracle 10g database. It's linked using the Oracle OleDB provider. There are ~79000 records that need to be deleted. Running this query it deletes 54. Running it again gives me this error messag...