Videos matching: Oracle

For oracle,

Can anyone fixes the function below to let it works with "a number (10,2)"? Just this condition only.

Here I come with ...More

For oracle,

Can anyone fixes the function below to let it works with "a number (10,2)"? Just this condition only.

Here I come with the function..

CREATE OR REPLACE FUNCTION Fmt_num(N1 in NUMBER)
RETURN CHAR
IS
BEGIN
RETURN TO_CHAR(N1,'FM9,9999.99');
END;
/

And I can use this with the SQL statement as follow

SELECT Fmt_num(price) from A;

So if I have an Oracle query like this:

SELECT * FROM xyz WHERE REGEXP_LIKE(col1, '^[0-9]+$');

How can I call More

So if I have an Oracle query like this:

SELECT * FROM xyz WHERE REGEXP_LIKE(col1, '^[0-9]+$');

How can I call REGEXP_LIKE using LINQ to Entities with the Oracle.DataAccess client:

var q = from x in collection
        where ??Oracle.REGEXP_LIKE(x.col1, "^[0-9]+$")??
        select x;

Is something like this supported with the Oracle.DataAccess client?

I need to display rows 256 to 700 from a table 'customers'. Customers table can have rows in the millions. Customers table has primary key defined o...More

I need to display rows 256 to 700 from a table 'customers'. Customers table can have rows in the millions. Customers table has primary key defined on 'cust_id'

This is going to be long, so here's a quick summary to draw you in: my top-N query with COUNT STOPKEY and ORDER BY STOPKEY...More

This is going to be long, so here's a quick summary to draw you in: my top-N query with COUNT STOPKEY and ORDER BY STOPKEY in its plan is still slow for no good reason.

Now, the details. It starts with a slow function. In real life it involves string manipulations with regexps. For demonstration purposes, here's an intentionally stupid recursive Fibonacci algorithm. I find it to be pretty fast for inputs up to about 25, slow around 30, and ridiculous at 35.

-- I repeat: Please no advice on how to do Fibonacci correctly.
-- This is slow on purpose!
CREATE OR REPLACE FUNCTION tmp_fib (
  n INTEGER
)
  RETURN INTEGER
AS
BEGIN
  IF n = 0 OR n = 1 THEN
    RETURN 1;
  END IF;
  RETURN tmp_fib(n-2) + tmp_fib(n-1);
END;
/

Now some input: a list of names and numbers.

CREATE TABLE tmp_table (
  name VARCHAR2(20) UNIQUE NOT NULL,
  num NUMBER(2,0)
);
INSERT INTO tmp_table (name,num)
  SELECT 'Alpha',    10 FROM dual UNION ALL
  SELECT 'Bravo',    11 FROM dual UNION ALL
  SELECT 'Charlie',  33 FROM dual;

Here's an example of a slow query: use the slow Fibonacci function to select rows whose num generates a Fibonacci number with a doubled digit.

SELECT p.name, p.num
FROM tmp_table p
WHERE REGEXP_LIKE(tmp_fib(p.num), '(.)\1')
ORDER BY p.name;

This is true for 11 and 33, so Bravo and Charlie are in the output. It takes about 5 seconds to run, almost all of which is the slow calculation of tmp_fib(33). So I want to do a faster version of the slow query by converting it to a top-N query. With N=1, it looks like this:

SELECT * FROM (
  SELECT p.name, p.num
  FROM tmp_table p
  WHERE REGEXP_LIKE(tmp_fib(p.num), '(.)\1')
  ORDER BY p.name
)
WHERE ROWNUM <= 1;

And now it returns the top result, Bravo. But it still takes 5 seconds to run! The only explanation is that it's still calculating tmp_fib(33), even though the result of that calculation is irrelevant to the result. It should have been able to decide that Bravo was going to be output, so there's no need to test the WHERE condition for the rest of the table.

I've thought that maybe the optimizer just needs to be told that tmp_fib is expensive. So I tried to tell it that, like this:

ASSOCIATE STATISTICS WITH FUNCTIONS tmp_fib DEFAULT COST (999999999,0,0);

That alters some of the cost numbers in the plan, but it doesn't make the query run faster.

Output of SELECT * FROM v$version in case this is version-dependent:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

And here's the autotrace of the top-1 query. It appears to be claiming that the query took 1 second, but that's not true. It ran for about 5 seconds.

NAME                        NUM
-------------------- ----------
Bravo                        11


Execution Plan
----------------------------------------------------------
Plan hash value: 548796432

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    55 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |           |       |       |            |          |
|   2 |   VIEW                  |           |     1 |    55 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|           |     1 |    55 |     4  (25)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL   | TMP_TABLE |     1 |    55 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1)
   3 - filter(ROWNUM<=1)
   4 - filter( REGEXP_LIKE (TO_CHAR("TMP_FIB"("P"."NUM")),'(.)\1'))

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
        593  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

We are planning to update our database to oracle 12c. Does anyone have any information as what are the exact difference between 11g and 12c. And wh...More

We are planning to update our database to oracle 12c. Does anyone have any information as what are the exact difference between 11g and 12c. And what all things need to be consider while doing migration?

Thanks

I'm not sure what the best approach is to get the result I'm looking for. In an oracle 10g database I'm trying to query all the users and produce a...More

I'm not sure what the best approach is to get the result I'm looking for. In an oracle 10g database I'm trying to query all the users and produce a calculated field for each row that has their supervisor IF their supervisor type is U. If it is not I need to walk up the tree until I find one that is. This query might need to recurse up multiple levels.

So for an Employee table like this:

Employee Table
+-------+----------+--------------+---------------+
| empno | empgroup |     user     | supervisor_no |
+-------+----------+--------------+---------------+
|     1 | E        | Joe Schmo    |             4 |
|     2 | E        | Mark Marin   |             3 |
|     3 | U        | Reed Patter  |             7 |
|     4 | E        | Martin Price |             7 |
|     7 | U        | Mary Wengert |               |
+-------+----------+--------------+---------------+

I'd like to see results like this where manager_no would be the calculated field

Results
+-------+----------+--------------+---------------+------------+
| empno | empgroup |     user     | supervisor_no | Manager_No |
+-------+----------+--------------+---------------+------------+
|     1 | E        | Joe Schmo    |             4 |          7 |
|     2 | E        | Mark Marin   |             3 |          3 |
|     3 | U        | Reed Patter  |             7 |          7 |
|     4 | E        | Martin Price |             7 |          7 |
|     7 | U        | Mary Wengert |               |            |
+-------+----------+--------------+---------------+------------+

Ok so I have been asked what I have tried. I am not saying this is the way it has to be done so if someone has a better suggestion I'm all ears but this is what I'm trying to achieve.

I envisioned two parts. The main query that gets all my results

select em.empno, em.empgroup, em.user, em.supervisor, (my subquery) as manager_no
from employee em

Query:

select * from employee em
connect by prior supervisor_no = empno
start with empno = 1

[Results][2]:

| EMPNO | EMPGROUP |     USERNAME | SUPERVISOR_NO |
---------------------------------------------------
|     1 |        E |    Joe Schmo |             4 |
|     4 |        E | Martin Price |             7 |
|     7 |        U | Mary Wengert |        (null) |

Ok, I found a way to filter on the group that might work but not sure if this is the most efficient route to go.

SELECT empno FROM (
SELECT empno FROM employee em
CONNECT BY PRIOR supervisor_no = empno
START WITH empno = 1 
order by level) d
WHERE d.empgroup = 'U' and rownum =1

I also created a fiddle if that helps. http://www.sqlfiddle.com/#!4/c8805/4

I'm using JPA to implement my entity framework backed by Hibernate as an ORM to work with Oracle DB.

Everything is glued up (Autowired) with...More

I'm using JPA to implement my entity framework backed by Hibernate as an ORM to work with Oracle DB.

Everything is glued up (Autowired) with Spring.

To generate the DB schema I've added this to the jpa-context.xml

<bean id="h8JPAVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="showSql" value="false" />
        <property name="generateDdl" value="true" />
</bean>

I've used sequence-generator to generate IDs for entities but for some reason when the schema is beeing created\updated, Hibernate doesn't create the Sequences them selves.

This is my mapping.xml file which injects the identity field

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
    version="1.0">

    <mapped-superclass class="com.myapp.entity.BaseModelEntity" access="FIELD">
        <attributes>
            <id name="id">
                <generated-value strategy="SEQUENCE" generator="MODEL_SEQ"/>
                <sequence-generator name="MODEL_SEQ" sequence-name="MYAPP_MODEL_SEQ"/>
            </id>
        </attributes>
    </mapped-superclass>
</entity-mappings>

Searching Google always give me results of how to use existing sequences, however I would like Hibernate to create them form me.

Thanks in advance for your help

I am trying to research this issue on the following two errors connecting to Oracle DBs:

1. Closed Connection
2. java.sql.SQE...More

i have a mapping configuration for a composite-key using fluent nhibernate and i need some help, because it is not working.

I have one clas...More

i have a mapping configuration for a composite-key using fluent nhibernate and i need some help, because it is not working.

I have one class called Agencia that have a composite key for two Int16 collumns that are mapped as number on a oracle 11g database.

[Serializable]
public class AgenciaPK
{
    [DisplayName("Código")]
    public virtual System.Int16 CdAgencia { get; set; }
    public virtual System.Int16 CdOrgaoArrecad { get; set; }

    public override int GetHashCode()
    {
        int hashCode = 0;
        hashCode = hashCode ^ CdAgencia.GetHashCode() ^ CdOrgaoArrecad.GetHashCode();
        return hashCode;
    }
    public override bool Equals(object obj)
    {
        var toCompare = obj as Agencia;
        if (toCompare == null)
        {
            return false;
        }
        return (this.GetHashCode() != toCompare.GetHashCode());
    } 
}

public class Agencia : AgenciaPK
{        
    public virtual OrgaoArrecad OrgaoArrecad { get; set; }
    [DisplayName("Nome")]
    public virtual string NmAgencia { get; set; }
    [DisplayName("Digito")]
    public virtual string NrDigitoAgencia { get; set; }
    [DisplayName("Número")]
    public virtual string NrAgencia { get; set; }
    [DisplayName("Descrição")]
    [StringLength(20)]
    public virtual string DsCompl { get; set; }
    [DisplayName("Logradouro")]
    public virtual string NmLograd { get; set; }
    [DisplayName("Bairro")]
    public virtual string NmBairro { get; set; }
    public virtual System.Nullable<short> IdSituacAgencia { get; set; }
    [DisplayName("Cep")]
    public virtual System.Nullable<short> NrCep { get; set; }
}

I have another class called Convenio that have a composite key composed by one string collumn (CdConvenio) and another is int CdOrgaoArrecad.

 [Serializable]
public class ConvenioPK
{
    [DisplayName("Código")]
    [StringLength(20)]
    public virtual string CdConvenio { get; set; }

    [DisplayName("Cód. Órgão arrecadador")]
    public virtual System.Int16 CdOrgaoArrecad { get; set; }
    public override int GetHashCode()
    {
        int hashCode = 13;
        if (CdConvenio != null)
        hashCode = hashCode ^ CdConvenio.GetHashCode() ^ CdOrgaoArrecad.GetHashCode();
        return hashCode;
    }
    public override bool Equals(object obj)
    {
        var toCompare = obj as Convenio;
        if (toCompare == null)
        {
            return false;
        }
        return (this.GetHashCode() != toCompare.GetHashCode());
    } 
}

public class Convenio : ConvenioPK
{
    [DisplayName("Órgão arrecadador")]
    public virtual OrgaoArrecad OrgaoArrecad { get; set; }
    [DisplayName("Data início")]
    [DataType(DataType.Date)]
    public virtual System.DateTime DtInicioConvenio { get; set; }
    [DisplayName("Data término")]
    [DataType(DataType.Date)]
    public virtual System.DateTime DtFinalConvenio { get; set; }
    [DisplayName("Tipo")]
    public virtual short TpConvenio { get; set; }
    [DisplayName("Tarifa")]
    public virtual short TpTarifaConvenio { get; set; }
    [DisplayName("Valor tarifa")]
    public virtual Double VlTarifaConvenio { get; set; }
    [DisplayName("Quantidade dias")]
    public virtual short QtDiasFloat { get; set; }
}

And when i access this class Agencia using this mapping class AgenciaMap, it works fine.

public class AgenciaMap : FluentNHibernate.Mapping.ClassMap<Agencia>
{        
    public AgenciaMap() {
        Table("SCI_AGENCIA");
        CompositeId()
            .KeyProperty(x => x.CdAgencia, "CD_AGENCIA")
            .KeyProperty(x => x.CdOrgaoArrecad, "CD_ORGAO_ARRECAD");
        Map(x => x.CdOrgaoArrecad).Column("CD_ORGAO_ARRECAD").Not.Nullable();
        Map(x => x.NmAgencia).Column("NM_AGENCIA").CustomType("AnsiString").Length(30).Not.Nullable();

        References<OrgaoArrecad>(x => x.OrgaoArrecad, "CD_ORGAO_ARRECAD").ForeignKey().Not.Update()
          .Not.Insert()
          .Cascade.None().Not.Nullable().Not.LazyLoad().Fetch.Join();
    }
}

But, when i try to access the class Convenio, using the class ConvenioMap:

 public class ConvenioMap : FluentNHibernate.Mapping.ClassMap<Convenio>
{        
    public ConvenioMap() {
        Table("CONVENIO");
        CompositeId()
           .KeyProperty(x => x.CdConvenio, "CD_CONVENIO")
           .KeyProperty(x => x.CdOrgaoArrecad, "CD_ORGAO_ARRECAD");

        Map(x => x.CdConvenio).Column("CD_CONVENIO").CustomSqlType("varchar2(20)").CustomType("AnsiString").Length(20).Not.Nullable();            
        Map(x => x.CdOrgaoArrecad).Column("CD_ORGAO_ARRECAD").Not.Nullable();
        Map(x => x.DtInicioConvenio).Column("DT_INICIO_CONVENIO").CustomSqlType("DATE").Not.Nullable();
        Map(x => x.DtFinalConvenio).Column("DT_FINAL_CONVENIO").CustomSqlType("DATE").Not.Nullable();
        Map(x => x.TpConvenio).Column("TP_CONVENIO").CustomSqlType("NUMBER(2,0)").Not.Nullable();
        Map(x => x.TpTarifaConvenio).Column("TP_TARIFA_CONVENIO").CustomSqlType("NUMBER(2,0)").Not.Nullable();            
        Map(x => x.VlTarifaConvenio).Column("VL_TARIFA_CONVENIO").CustomSqlType("NUMBER(15,2)").Not.Nullable();  
        Map(x => x.QtDiasFloat).Column("QT_DIAS_FLOAT").CustomSqlType("NUMBER(3,0)").Not.Nullable();

        References(x => x.OrgaoArrecad)
            .Column("CD_ORGAO_ARRECAD")
            .ForeignKey().Not.Update()
            .Not.Insert()
            .Cascade.None()
            .Not.Nullable()
            .Not.LazyLoad()
            .Fetch.Join();
    }
}

When i map a collumn that is the type string "CD_CONVENIO", i'm getting this error:

Wrong column type in SCI.CONVENIO for column CD_CONVENIO. Found: varchar2, Expected NVARCHAR2(255)

The specific part of my code that i'm in doubt is this:

CompositeId()
           .KeyProperty(x => x.CdConvenio, "CD_CONVENIO")
           .KeyProperty(x => x.CdOrgaoArrecad, "CD_ORGAO_ARRECAD");

So, here is my question: What am i doing wrong?

I can't see my error. I marked the places SQL Developer is citing errors. Can you see where I am going wrong? ZCTA5_2000 is a valid table with da...More

I can't see my error. I marked the places SQL Developer is citing errors. Can you see where I am going wrong? ZCTA5_2000 is a valid table with data.

CREATE OR REPLACE TRIGGER zip_trigger
  BEFORE INSERT ON ZCTA5_2000
  FOR EACH ROW
DECLARE
  low_zip NUMBER(5);
  high_zip NUMBER(5);  <---  Error PL/SQL: SQL Statement ignored  ******
BEGIN  <-----Error PL/SQL: ORA-00933: SQL command not properly ended ********

  SELECT LOW_ZIP, HIGH_ZIP FROM ZIPTABLE
  INTO low_zip, high_zip
  WHERE :NEW.STATE = STATE_CODE;  <--- Error PLS-00103: Encountered the symbol "END"... 
    IF :NEW.ZIP < LOW_ZIP OR :NEW.ZIP>HIGH_ZIP
    END IF;
END;

why my query is taking too much time ( 30 - 60 minutes ) to execute if the cost of the query is very low ( 209 )

Here is the image of the Cu...More

why my query is taking too much time ( 30 - 60 minutes ) to execute if the cost of the query is very low ( 209 )

Here is the image of the Current Statement

Tks!

Isslerman

I have script1.php and its code looks like below

$con = oci_pconnect(connection_details);
if(!$con){
  //throw error;
}
$q1 = "SOME ...More

I have in the past written queries that give me counts by date (hires, terminations, etc...) as follows:

SELECT per.date_start AS "D...More

I am having a hierarchical table like below. I want to set the FLAG attribute based on the CODE values. The possible Code ...More

I am having a hierarchical table like below. I want to set the FLAG attribute based on the CODE values. The possible Code values are R, NR, RN and NF. The FLAG will be set to 'Y' iff the corresponding code value is consistent with that of parent i.e

if parent.code = 'R' and child.code = 'R' then flag = 'Y'
if parent.code = 'NR' and child.code = 'NR' then flag = 'Y'
if parent.code = 'RNR' and child.code = 'R' or 'NR', then flag = 'Y'.

Here's my table:

create table My_BOM(assign_Id varchar2(50) not null primary key, 
parent_assign_Id varchar2(50) references My_BOM(assign_Id), 
code varchar2(50) not null, 
flag varchar2(1) default 'Y');


insert into my_bom values ('T1',null,'RNR','Y');
insert into my_bom values ('T2','T1','R','Y');
insert into my_bom values ('T3','T2','NR','Y');
insert into my_bom values ('T4','T3','R','Y');
insert into my_bom values ('T5','T1','NF','Y');
insert into my_bom values ('T6','T5','R','Y');
insert into my_bom values ('T7','T1','NR','Y');
insert into my_bom values ('T8','T7','RNR','Y');
insert into my_bom values ('T9','T8','R','Y');
insert into my_bom values ('T10','T1','RNR','Y');

How to set the FLAG? This is how I tried but this query also returns the T9 record which I don't want because the chain is broken in between i.e their is inconsistency in the top levels of the CODE. so, T9 should n't come as part of the result.

select connect_by_root(assign_Id), assign_Id desc_assign_Id, code, flag
from my_bom
where
--exists (select null from my_bom mb where mb.parent_assign_Id = prior my_bom.assign_Id 
--connect by mb.parent_assign_Id = prior mb.assign_Id) and 
DECODE( CODE,
  'R', 'R', 'NR', 'NR', 'RNR',
  :bind_var, NULL) = PRIOR DECODE( CODE,
  'R', 'R', 'NR', 'NR', 'RNR',
  :bind_var, NULL)
  start with parent_assign_Id is null
connect by parent_assign_Id = prior assign_Id
order by desc_assign_Id;

"bind_var" will be either 'R' or 'NR'

Here's the SQL Fiddle:http://sqlfiddle.com/#!4/009ad/2/0

When I upload a *.cvs file into Oracle Express using the data upload tool, the column mapping changes. I have uploaded the same data be...More

When I upload a *.cvs file into Oracle Express using the data upload tool, the column mapping changes. I have uploaded the same data before, to test things out.

I then deleted all the data in the table and now want to load in fresh data for further testing. The mapping looks good in the *.cvs file before I upload it but when I get to the last step the columns have changed position in the *.cvs file.

Any idea why does this happen and what can I do to prevent it?

We are using HSQLDB with Oracle database syntax(jdbc:hsqldb:mem:TestDB;sql.syntax_ora=true) for our datalayer unit tests.

(We realize this i...More

We are using HSQLDB with Oracle database syntax(jdbc:hsqldb:mem:TestDB;sql.syntax_ora=true) for our datalayer unit tests.

(We realize this is not ideal and it would be better if we could test against an actual Oracle db. However this is not an option, since we want to make sure we can run our automated tests anywhere. In a later step, all queries are also tested in integration tests on an actual Oracle database, which is setup similar to the production database.)

Is there any way to support the Oracle outer join (+) notation for queries with HSQLDB?

In my own opinion we should not use the (+) notation but the standard outer join notation instead. However, the guys that are in charge of tuning the queries use this notation, so communication with them would get more difficult/errorprone. Although it might be an option if no solution is found, this is not the question here.

I found the following thread: http://comments.gmane.org/gmane.comp.java.hsqldb.user/5756 With the QueryRewrite patch mentioned there, I would have the necessary hook to handle the (+) myself, but that interface never made it to the official HSQL release.

Is there another workaround to this for HSQL?

We could add the query rewriting somewhere in our unit tests themselves(instead of at the test db level), but it would be better if we can avoid that.

If there is no workaround for HSQL, is there any other in-memory database/database starteable from a jar, that supports this (+) outer join notation?

I have this query :

(SELECT OBJECT_ID from cr_object_group_entries_vw where object_group_id IN
    (SELECT ITEM FROM TABLE(CR_FN_SPL...More

I'm querying a web service that returns a date from an Oracle database that looks like this: /Date(1369519200000)/ but I haven'...More

I'm querying a web service that returns a date from an Oracle database that looks like this: /Date(1369519200000)/ but I haven't the slightest clue what the value of this Long represents, so I don't know how to convert it into a javascript date object.

Please help

Im very beginner in psql and i have a question.

Here is the code:

SET serveroutput ON
ACCEPT myVariable PROMPT "Input value:...More

Working on adding unit testing around database code on a Java legacy project. Some of SQL (Oracle) contains analytics functions .. (i.e. parition o...More

Working on adding unit testing around database code on a Java legacy project. Some of SQL (Oracle) contains analytics functions .. (i.e. parition over syntax). Is there any in memory SQL DB options out there (preferably open-source) that support these functions?

Any other solutions? I would prefer not to hit the real database (even if I rollback the data).

I am currently doing this excel via VBA, but if I can get some help to do it with in query would be really save me lots of time.

This is 1 o...More

I am currently doing this excel via VBA, but if I can get some help to do it with in query would be really save me lots of time.

This is 1 of like 5 things I need to do for this report I am working on.

Tables
I am reading from 3 different tables

TableA   TableB  TableC

Fields from tableA

OUT_ID

Fields from tableB

timestamp
event_type
worker
operator_id
new_date

Fields from tableC

worker

Query

Select
       TableA.OUT_ID,
       TableB.timestamp,
       TableB.new_date,
       TableB.event_type,
       TableC.worker,
       TableB.operator_id

From TableA left outer join
        TableB 
           ON TableA.OUT_ID = TableB.OUT_ID and
                 TableB.event_type in ('WORKER_RET_CMT_CHANCE','RET_CHANCE','WORKER_STATUS_CHANCE') Left OUTER JOIN 
              TableC 
                ON TableB.worker = TableC.worker
 where  TableA.time_stamp > {?PickDate} //parameter in crystal report to pick date
    and TableA.time_stamp < {?RestDate}  //parameter in crystal report to pick date 
   order by TableA.OUT_ID, TableB.timestamp

Here is what I need to do.

In Event_type column if RET_CHANCE has WORKER_RET_COMMENT_CHANCE in next row then check if OUT_ID is same, if it is same than check if TIMESTAMP difference is less than 10 seconds, if all this is true than copy WORKER number from yellow cell to red cell.

Please see image blow and attached excel file for sample. Download excel file via dropbox

I have two oracle users and I am creating different schema for them. I mean each schema has different tables, types etc. I wanted to create both sch...More

I have two oracle users and I am creating different schema for them. I mean each schema has different tables, types etc. I wanted to create both schemas by flyway maven plugin, first I had two maven plugins, but then I tried also to have two separate profiles:

<profiles>
    <profile>
      <id>database</id>
      <build>
        <plugins>
          <plugin>
            <groupId>com.googlecode.flyway</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <configuration>
              <url>jdbc:oracle:thin:@devdb2:1521:ZOOMUTF</url>
              <table>SCHEMA_UPDATES</table>
              <outOfOrder>true</outOfOrder>
              <locations>
                <location>db/callrec</location>
              </locations>
              <user>cr_5199_mensik_mvn</user>
              <password>callrec</password>
              <serverId>callrec</serverId>
            </configuration>
            <executions>
              <execution>
                <id>compile</id>
                <phase>compile</phase>
                <goals>
                  <goal>migrate</goal>
                </goals>
              </execution>
              <execution>
                <id>clean</id>
                <phase>clean</phase>
                <goals>
                  <goal>clean</goal>
                </goals>
              </execution>
            </executions>
          </plugin>
        </plugins>
      </build>
    </profile>

    <profile>
      <id>database_wbsc</id>
      <build>
        <plugins>

          <plugin>
            <groupId>com.googlecode.flyway</groupId>
            <artifactId>flyway-maven-plugin</artifactId>
            <configuration>
              <url>jdbc:oracle:thin:@devdb2:1521:ZOOMUTF</url>
              <table>SCHEMA_UPDATES</table>
              <outOfOrder>true</outOfOrder>
              <locations>
                <location>db/wbsc</location>
              </locations>
              <user>sc_5199_mensik_mvn</user>
              <password>wbsc</password>
              <serverId>wbsc</serverId>
            </configuration>
            <executions>
              <execution>
                <id>wbsc_compile</id>
                <phase>compile</phase>
                <goals>
                  <goal>migrate</goal>
                </goals>
              </execution>
              <execution>
                <id>wbsc_clean</id>
                <phase>clean</phase>
                <goals>
                  <goal>clean</goal>
                </goals>
              </execution>
            </executions>
          </plugin>
        </plugins>
      </build>
    </profile>
</profiles>

Then I execute:

mvn clean -Pdatabase,database_wbsc

But result is that only second profile is executed twice:

[INFO] [clean:clean {execution: default-clean}]
[INFO] [flyway:clean {execution: clean}]
[INFO] Cleaned schema "sc_5199_mensik_mvn" (execution time 00:00.023s)
[INFO] [flyway:clean {execution: wbsc_clean}]
[INFO] Cleaned schema "sc_5199_mensik_mvn" (execution time 00:00.018s)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESSFUL
[INFO] ------------------------------------------------------------------------

If I switch order of profiles in xml (not in maven execution command) then the second user is used.

How can I execute both profiles but with their configuration?

I'm working on a jasper report which takes one parameter based on which it should have a different where clause. Below is the query I'm using but se...More

I'm working on a jasper report which takes one parameter based on which it should have a different where clause. Below is the query I'm using but seems there's a syntax error somewhere.

  select customer.name
         customer_order.name
  from customer, customer
  where customer.orders > 0
    and customer_order.customer_id = customer.id
    and customer_order.name in
        (
           case when (<paramter> = 1) then ('order1', 'order2')
           else  (select order_name from customer_order)
           end
         );

I appreciate any help, thanks

I am new to the oracle database and I am trying to use PIVOT to convert rows into columns. I have following tables..

table 1
solid  ...More

I am facing with a challenging problem in database design for a data warehouse have to be realized with 3 layers:

• staging (ST...More

I am new to Oracle SOA Suite 11.1.1.4... Currently I am working on User defined XPath Functions. As part of this, I will prepare a jar file and need...More

I am new to Oracle SOA Suite 11.1.1.4... Currently I am working on User defined XPath Functions. As part of this, I will prepare a jar file and need to deploy into weblogic server(10.3) domain lib folder(fusionmw\soasuite\11gR1\11.1.1\sp4\user_projects\domains\DOMAIN_NAME\lib). When ever this jar is altered, I need to replace this in server domain lib and restart the server to load the changes. But I feel this is not a good practice in production server to restart.

Is there a way to auto load updated jar into server's domain so the changes will reflect immediately instead of restarting the server.

Thanks in advance, Swamy.

I want to calculate present Age from Date of Birth in my oracle function. for that i am using (Today-Dob)/30/12.

But this is not accurate as...More

I want to calculate present Age from Date of Birth in my oracle function. for that i am using (Today-Dob)/30/12.

But this is not accurate as some months will have 31 days.

I want to get the correct date with maximum precision How ca i do that??

I'm trying to use the Oracle ODP.NET 11g (11.1.0.6.20) Instant Client on my ASP.net project as a Data Provider but...More

I'm trying to use the Oracle ODP.NET 11g (11.1.0.6.20) Instant Client on my ASP.net project as a Data Provider but when I run the aspx page I get a "The provider is not compatible with the version of Oracle client" error message. Any help would be appreciated.

I've referenced the Data Provider in Visual Studio 2005 and the code behind looks like this:

using Oracle.DataAccess.Client;
..

OracleConnection oOracleConn = new OracleConnection();
oOracleConn.ConnectionString =
    "Data Source=MyOracleServerName;" +
    "Integrated Security=SSPI";
oOracleConn.Open();

//Do Something

oOracleConn.Close();

The error for the page looks like this:

Exception Details: Oracle.DataAccess.Client.OracleException: The provider is not compatible with the version of Oracle client

Source Error: 
Line 21: 
Line 22: 
Line 23:             OracleConnection oOracleConn = new OracleConnection();
Line 24:             oOracleConn.ConnectionString =
Line 25:                 "Data Source=MyOracleServerName;" +

[OracleException (0x80004005): The provider is not compatible with the version of Oracle client]
   Oracle.DataAccess.Client.OracleInit.Initialize() +494
   Oracle.DataAccess.Client.OracleConnection..cctor() +483

Stack Trace: 
[TypeInitializationException: The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception.]
   Oracle.DataAccess.Client.OracleConnection..ctor() +0
   Boeing.IVX.Web.RoyTesting.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\CE218C\Desktop\IVX.Net\Web\IVX\RoyTesting.aspx.cs:23
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436

Being used to (and potentially spoiled by) MSSQL, I'm wondering how I can get at tables size in Oracle 10g. I have googled it so I'm now aware that ...More

Being used to (and potentially spoiled by) MSSQL, I'm wondering how I can get at tables size in Oracle 10g. I have googled it so I'm now aware that I may not have as easy an option as sp_spaceused. Still the potential answers I got are most of the time outdated or don't work. Probably because I'm no DBA on the schema I'm working with.

Would anyone have solutions and or recommendations?

Thanks, Rollo

I know the statement:

create table xyz_new as select * from xyz;

Which copies the structure and the data, but what...More

I know the statement:

create table xyz_new as select * from xyz;

Which copies the structure and the data, but what if I just want the structure?