Which are the diferrences between the following two queries? Both returns different rows: with ordered_table as ( select * from table order by column1 ) select first_value(column2 ignore nulls) over (partition by column3) from ordered_table; and select first_value(column2 ignore nulls) over (partition by column3 order by column1) fr...
The Code: ResultSet rs = null; try { conn = getConnection(); stmt = conn.prepareStatement(sql); rs = stmt.executeQuery(); while (rs.next()) { Blob blob = rs.getBlob("text"); byte[] blobbytes = blob.getBytes(1, (int) blob.length()); String text = new String(blobbytes); The result: java.sql.SQLExc...
I asked this question in regard to SQL Server, but what's the answer for an Oracle environment (10g)? If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using something like MSSQL's Commom Table Expressions, perhaps? ...
The query is the following: with t as ( select 450 id, null txt , 3488 id_usr from dual union all select 449 , null , 3488 from dual union all select 79 , 'A' , 3488 from dual union all select 78 , 'X' , 3488 from dual ) select id , txt , id_usr , first_value(txt ignore ...
I'm fairly new to the language, and I was wondering if tail calls were optimized. In other language I could examinate the machine code or an intermediate representation and figure it for myself but I have no idea about how to do that in PL/SQL. Thanks in advance. ...
I have an SSRS report that uses an Oracle datasource. I have a query; select * from myTable t where t.date between Date '2009-08-01' and Date '2009-08-04' + 1 This query works, both in Oracle and from SSRS. It gets all rows from myTable when the t.date column is between 08/01/2009 and 08/04/2009. The t.date column is of type Date() N...
I used to think that Oracle does not index a row when one of the column values is null. Some simple experimentation shows this to be not the case. I was able to run some queries unexpectedly accessing only indexes even though some columns were nullable (which of course was a pleasant surprise). A Google search led to some blogs with co...
Hello, I have a performance issue when selecting data in my project. There is a table with 3 columns: "id","time" and "group" The ids are just unique ids as usual. The time is the creation date of the entry. The group is there to cummulate certain entries together. So the table data may look like this: ID | TIME | GROUP -----...
We have a table containing coordinates, among other things. We used to store these in two number fields (x and y), but we've now replaced this with an SDO_GEOMETRY field. For backwards compatibility, we have created a view (with the same name as the table used to have), where we do: create or replace view meas_pnt as select ..., m.posit...
I have a crontab job run at 00:00 to update my website database every night; To prevent the job from brreaking end user's operation on website, should I stop my website during the job is running? Is there any better alternate course? Thanks in advance! ...
Hi, I have a sample xml file created using Editplus( in windows). < ?xml version="1.0" encoding="UTF-8" ?> < badges > < row UserId="3714" Name="Teacher" Date="2008-09-15T08:55:03.923"/> < row UserId="994" Name="Teacher" Date="2008-09-15T08:55:03.957"/> < / badges> My goal here is to get this information into Oracle DB...
Is there a data-dictionary view or some other way of telling which (if any) sessions currently have tracing enabled (after a call to DBMS_MONITOR.SESSION_TRACE_ENABLE)? (At the minute I keep running an ls on the udump folder, but this isn't exactly foolproof) ...
Friends, I am looking to introduce a logging framework into our existing Oracle application to replace the use of DBMS_OUTPUT. The framework will be used primarly to aid debugging and would detail such things as starting x procedure, details of parameters, ending procedure x etc. It should also have the functionality to be turned on ...
I've two tables with the following fields: table1 : OTNAME table2 : SNCODE, description_text I'm trying to add the two columns of table2 to table1 and update the columns. My query is: alter table table1 add sncode integer alter table table1 add description_text varchar2(30) update table1 set snc...
Hi, I'm new to c# and .net development. I'm looking for a C# code generator that can generate the code for CRUD operations and entity objects against tables in a database. It needs to be: Support Oracle database; The generated code must be simple and easy to use. Thank you very much in advance! ...
I have a sql file test.sql used to run some SQL (create object / update / delete / insert) that can look like this CREATE TABLE test_dbi1 ( test_dbi_intr_no NUMBER(15) , test_dbi_name VARCHAR2(100); UPDATE mytable SET col1=1; CREATE TABLE test_dbi2 ( test_dbi_intr_no NUMBER(15) , test_dbi_name V...
hey.. i was trying java and oracle 9i connectivity for a long time i tried different ways the program is getting compiled very well at execution time its showing me an error stating " no classdeffounderror" wat shud i do?please sumbody provide me a solution to this(i tried setting the class path in all ways... )i need it as soon as posib...
I am using EJB3, JBoss AS 4.2.1 and Oracle 10g. The thing is every time i deploy to the AS the sequence is broken. Ex: when i am looking to the sequence from toad, its last value is 41 but the actual id which is set for the new entry is 1050. Do you have any idea or a known bug about this issue. Thanks. ...
Hi I am trying to insert user's input in oracle using Java JDBC. I have to use Insert query. Oracle's column type is varchar2. I am able to insert if user is not entering special character. when user enter special character like # ? / and $ it gives exception Please help to parse these special character. i need to use insert query ...
I'm reading a pipe delimited file with SQL Loader and want to populate a LAST_UPDATED field in the table I am populating. My Control File looks like this: LOAD DATA INFILE SampleFile.dat REPLACE INTO TABLE contact FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ( ID, FIRST_NAME, LAST_NAME, EMAIL, DEPARTMENT_ID, LAST_UPDATED SYSTIM...