oracle

Saving a report to a file on a client PC?

Friends, I would be grateful for a quick sanity check! When calling Oracle Reports 10g from Oracle Forms 10g, is it possible to run an Oracle Report and save it on the users PC to a specified directory? (I know that if an Oracle Report is run in such a format as PDF the user can save the report to x location). To call the Oracle repo...

Query Performance, Multiple "OR's" vs. Separate Statements

Let's say I have an Oracle database table of 40MM 9-digit ZIP codes which includes the 4-digit ZIP code for each. I have a list of 800 5-digit ZIP codes and I need to find all the associated 9-digit ZIP codes. There are 40K 5-digit ZIP codes altogether. Assume we have indexed the 5-digit ZIP code field with a B*Tree (conventional) ind...

SQL LIKE query not working

I'm trying to run the following query against an Oracle DB, but the query is returning 0 records: select * from TABLE where upper(FIELD) like '%SEE COMMENT%' I know this field contains many records with 'See Comment" in it. For example, here is one of the records: =if(and(Robust_Mean>=20,Robust_Mean<=70),.03*(Robust_Mean+29), if(Ro...

How does one deal with multiple TimeZones in applications that store dates and times?

I realize this is a bit subjective, but I'm hoping to pick everyones brain here on how they deal with multiple timezones? There are a variety of similar questions here and an equally wide variety of accepted answers. How have you delt with this in apps you've built, and what issues did you have to overcome? ...

SQL Server version of Oracle's ADD_MONTHS()

In Oracle, you can use ADD_Months to add months on the fly in the sql statement. What is the MS SQL version. Oracle Example Select TestDate, TestFrequency, ADD_MONTHS(TestDate, TestFrequency) AS FutureTestDate FROM Tests Source : java's website ...

ojdbc14.jar vs. ojdbc6.jar

I noticed the following difference but did not see it documented anywhere. I'm wondering if others have noticed the same thing or can point me to some documentations that proves the same. Env:- Oracle 11g, JDK 1.6, iBatis, PL/SQL Scenario:- ojdbc14.jar: if pl/sql returns a variable of type DATE and I try to put that in a java.sql.D...

SQL Virtual Column

I am getting the 'SQL Error: ORA-01733: virtual column not allowed here' Here is my code: update ( Select g.FIRST300BB from ( select FIRST300BB, row_number() over (order by pitchandeventid) r from gamedata_me where hitterid = 5000 and (eventcode = 'BB') ) g where r between 0 and 300 ) t set t.FIRST30...

PHP, Apache & Oracle setup

Hi, We are planning to develop a web application on PHP & Oracle (11gR2) on Apache and the OS is RedHat AS5 update 3 (AS5.3) Linux. I would like to know the recommended versions of PHP and Apache to be used for such a setup. I have come to know that PHP and Oracle offer the best performance when Apache is running in prefork mode. In ...

Fetching fetch the first occurance from the result.

Hello, I have a oracle sql select distinct tab1.col1, tab2.col1 from table1 tab1 join table2 tab2 on tab1.col1 = tab2.col1 Here i get the as expected in terms of distinct values. For Example : The result rows are 1 2 3 4 5 6 Now i want to add one more join for table3. so my sql is select distinct ...

Removal of foreign key constraints, Referential integrity and Hibernate

Hi, My colleague mentioned that our client DBA proposed the removal of all foreign key constraints in our project Oracle DB schema. Initially I did not agree with the decision. I am a developer not a DBA. So later realized that there could be some reasons behind the decision. So I am trying get the pros and cons of this decision. Proj ...

convert query from sql to oracle

Hi i have the following sql query SELECT @weekenddtm = CONVERT(VARCHAR,DATEADD(DD,(7 - DATEPART(DW,@p_end_dtm)),@p_end_dtm),111) and i tried converting it into oracle using this query v_weekenddtm := CAST(p_end_dtm + NUMTODSINTERVAL((7-TO_NUMBER(TO_CHAR(p_end_dtm,'D'))),'DAY') AS DATE); but it is giving me error..any idea how t...

Storing Schedule in Database

How do i store routine or schedule in Database in efficient way.Such as i have 20 rooms and in this rooms 8-10,10-12,... this way classes are held.How do i store this sort of schedule in database in efficient way? ...

Viewing oracle's metadata about primary/foreign key constraints.

Which table contains detailed information(For example the table the foreign key is referring to) about the constraints? The tables 'all_cons_columns' , 'all_constraints' contains only the name of the constraints which isn't very helpful. I am currently using dbms_metadata.get_ddl() but it doesn't work on all the databases. Thanks. ...

JPA - EclipseLink - How to change default schema

I'm programming a web application using weblogic and oracle. the datasource is configured through JNDI, with a restricted database user who can DML into tables, but can't DDL. As you may guess, that user isn't the owner of those tables, but he's granted access. Let's say he is GUEST_USER The application is using JPA + EclipseLink, and ...

Advice for a UI migration from Oracle Forms to JSP

Hi there! I'm starting a new project and would like some advice. The purpose is to migrate the front-end of a web application developed with Oracle in the back-end (version 9) and Oracle Forms 6.0 in the front-end to JSP. The layout must be the same and it's going to be developed also Web Services in the future. What advices and/or r...

Oracle Stored Procedure call by PhP

Here the code: <?php include_once 'config.php'; // Connect to database $conn = oci_connect($dbuser, $dbpasswd, $dbhost."/".$dbname); if (!$conn) { exit ("Connection failed."); } $id = isset($_GET['id']) ? (int)$_GET['id'] : false; $type = isset($_GET['type']) ? strtoupper($_GET['type']) : "BLOG"; $stmt = oci_parse($conn, "beg...

Source code for specific stored procedure or function

I can use all_arguments and all_procedures to list the procedures and functions inside any given package and with DBMS_METADATA I can extract the DDL for that package. Is there an easy way (other than lots of instring and substring calls) to obtain the procedure or function source code separately for each separate block of code in a pack...

What happens when BEFORE INSERT TRIGGER fails in oracle

Hi, I have a small doubt regarding BEFORE INSERT TRIGGER in oracle, my trigger looks like this: CREATE OR REPLACE TRIGGER some_trigger BEFORE INSERT ON some_table REFERENCING NEW AS newRow FOR EACH ROW DECLARE some_var number(25, 4); BEGIN -- do some stuff :newRow.some_column :=some_var; exception when no_data...

Standard SQL alternative to Oracle DECODE

Is there an ANSI SQL equivalent to Oracle's DECODE function? Oracle's decode function is the IF-THEN-ELSE construct in SQL. ...

Why can't I use '??' operand for System.DBNull value?

I have an Oracle data table fetching columns that be null. So I figure to keep the code nice and simple that I'd use the ?? operand. AlternatePhoneNumber is a string in my C# model. AlternatePhoneNumber = customer.AlternatePhoneNumber ?? "" However, even with that code I still get the error. System.InvalidCastException: Unable to cas...