Videos matching: Oracle

I want to call a function inside a procedure and get it's value so I can use within the procedure but I have no idea how to do that ?? Any ideas ??...More

I want to call a function inside a procedure and get it's value so I can use within the procedure but I have no idea how to do that ?? Any ideas ?? My function is the following one , this function returns a collection of employees' ids whose salary out of the max or min salary , I want to use this value in a procedure to update the salaries of those employees to the minimum salary ??

I'm using the latest ORMLite to query against Oracle database. I create all DAOs in Spring and have @DatabaseField(id = true) annotation for my key ...More

I'm using the latest ORMLite to query against Oracle database. I create all DAOs in Spring and have @DatabaseField(id = true) annotation for my key field. In Spring configuration my JDBC url is like jdbc:oracle.... But when I execute my query by queryById or queryForEq etc ORMLite always try to use the column name with lower case and double quotes which defined in my entity class, so that Oracle doesn't like it and throws an exception (ORA-00904: "id": invalid identifier). Anyone knows the solution?

I do reporting out of our General Ledger which has separate tables for actual and budget amounts. I merge data by unioning three queries together i...More

I do reporting out of our General Ledger which has separate tables for actual and budget amounts. I merge data by unioning three queries together in a subquery and summing the results of the subquery.

I'm wondering if there's a better way of doing this.

Test data and query are below.

-- Create actuals table
create table actuals
(
gl_key number not null,
amount number not NULL
);

-- Create budgets table
create table budgets
(
gl_key number not null,
amount number not NULL
); --add actuals data
INSERT INTO actuals (gl_key, amount) VALUES (300001000, 10000);
INSERT INTO actuals (gl_key, amount) VALUES (300002000, 50000);
INSERT INTO actuals (gl_key, amount) VALUES (300003000, 20000);
COMMIT; --add budgets data
INSERT INTO budgets (gl_key, amount) VALUES (300001000, 7500);
INSERT INTO budgets (gl_key, amount) VALUES (300003000, 20000);
INSERT INTO budgets (gl_key, amount) VALUES (300004000, 5000);
COMMIT; --merge query
WITH act_bud AS
(SELECT act.gl_key, act.amount AS act_amount, 0 AS bud_amount
FROM actuals act
LEFT OUTER JOIN budgets bud
ON act.gl_key = bud.gl_key
WHERE bud.gl_key IS NULL
UNION
SELECT bud.gl_key, 0 AS act_amount, bud.amount AS bud_amount
FROM budgets bud
LEFT OUTER JOIN actuals act
ON bud.gl_key = act.gl_key
WHERE act.gl_key IS NULL
UNION
SELECT act.gl_key, act.amount AS act_amount, bud.amount AS bud_amount
FROM actuals act
INNER JOIN budgets bud
ON act.gl_key = bud.gl_key)
SELECT gl_key, SUM(act_amount) AS act_amount, SUM(bud_amount) AS bud_amount
FROM act_bud
GROUP BY gl_key
ORDER BY gl_key


this function check if all employees have their salary between the correct range of maximum and minimum salary. I want to update it so it would retu...More

this function check if all employees have their salary between the correct range of maximum and minimum salary. I want to update it so it would return the employees' ids whose salary is out of range ?? I tried and define a collection but it keeps giving me error . Any Ideas ??

create or replace function min_max return number as
cursor job_cur is
    select job_id, min_salary, max_salary from jobs;
cursor emp_cur(p_job_id jobs.job_id%type) is
    select employee_id, salary from employees where job_id = p_job_id;
Type id_table is table of number index by binary_integer;
return_id id_table;
i number := 1;
begin
for job_rec in job_cur loop
    for emp_rec in emp_cur(job_rec.job_id) loop
        if (emp_rec.salary > job_rec.max_salary)
            or (emp_rec.salary < job_rec.min_salary) then
            --return 0;
            return_id(i).emlpoyee_id := emp_rec.employee_id; // error here
            i := i+1;
        end if;
    end loop;
end loop;
--return 1;
return return_id;  // error here
end min_max;

Here is the updated code but it's not working . The errors : Error(15,17): PL/SQL: Statement ignored. Error(15,30): PLS-00487: Invalid reference to variable 'NUMBER'. Error(21,5): PL/SQL: Statement ignored. Error(21,12): PLS-00382: expression is of wrong type.

IMO, this query should return A=1,B=2,

SELECT regexp_substr('A=1,B=2,C=3,', '.*B=.*?,') as A_and_B FROM dual
<...More

IMO, this query should return A=1,B=2,

SELECT regexp_substr('A=1,B=2,C=3,', '.*B=.*?,') as A_and_B FROM dual

But it returns whole string A=1,B=2,C=3, instead. Why?

fiddle

I wrote a function in pl-sql to check if all employees have their salary between the correct range of maximum and minimum salary. But it's giving me...More

I wrote a function in pl-sql to check if all employees have their salary between the correct range of maximum and minimum salary. But it's giving me error like this : Error(9,3): PL/SQL: Statement ignored. Error(9,16): PLS-00306: wrong number or types of arguments in call to '>'

CREATE OR REPLACE FUNCTION MIN_MAX_SAL RETURN NUMBER AS 
cursor emp_cur is select salary from employees ;
emp_sal emp_cur%rowtype;
min_sal jobs.min_salary%type;
max_sal jobs.max_salary%type;
BEGIN
select min_salary , max_salary into min_sal , max_sal from jobs;
for emp_sal in emp_cur loop
if ((emp_sal > max_sal) or (emp_sal < min_sal)) then
return 0;
end if;
end loop;
RETURN 1;
END MIN_MAX_SAL;

what is the wrong ??

I learned the basics of writing a function and procedure in pl-sql but I don't know how to test if it's working , and if not How can I debug it . An...More

I learned the basics of writing a function and procedure in pl-sql but I don't know how to test if it's working , and if not How can I debug it . Any Ideas please . thanks all. Here is the function I'm trying to test it

CREATE OR REPLACE FUNCTION MIN_MAX_SAL RETURN NUMBER AS 
cursor emp_cur is select salary from employees ;
emp_sal number;
min_sal jobs.min_salary%type;
max_sal jobs.max_salary%type;
BEGIN
select min_salary , max_salary into min_sal , max_sal from jobs;
for emp_sal in emp_cur loop
if (emp_sal > max_sal or emp_sal < min_sal) then
return 0;
end loop;
RETURN 1;
END MIN_MAX_SAL;

Any help ?

I have a performance issue on the following oracle query:

select tab_a.col1,tab_a.col2,tab_b.col1 etc etc from tab_a,tab_b
where 
ta...More

We store some application parameters in our database and I am looking for a method via SQL to extract/parse the parameters out. An example of this ...More

We store some application parameters in our database and I am looking for a method via SQL to extract/parse the parameters out. An example of this data would be the following:

PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]

So for example the value for Projection would be "Transverse" and quotes would be part of that. I figured the easiest approach to getting this out would be using the REGEXP_SUBSTR function. This is my current attempt:

SELECT REGEXP_SUBSTR('PROJS["AA",ZZZZ[PARAMETER["One",1]],PROJECTION["Transverse"],UNIT["Two",2]]', 'PROJECTION\[[^][]*\]') FROM dual;

That though returns:

PROJECTION["Transverse"]

I realize that I could then just strip out the unneeded characters but I was curious if someone could recommend any REGEX tweaks so that the SELECT query instead returns simply "Transverse" for this example. Projection is not always what I'd want to pull out though, for example if I wanted Unit then I'd need returned: "Two",2

This is using Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

Many table designs I see around has a id column as a primary key. For example log_id in some Logging table, event_id in some event table and so and ...More

Many table designs I see around has a id column as a primary key. For example log_id in some Logging table, event_id in some event table and so and so. This column would have no dependency on any other column in any other table and uniquely identifies the record. From the look up perspective often times the columns used to look up information are other columns in the table that could be indexed as well (status/event_type/etc etc). So, what is the need to have such a id column representing the record in the table? If I were to remove such id column from a log table and may be instead use composite key what crime am I committing? Why is it so prevalent practice to have such a unique id column in a table where otherwise that column is not utilized in the application? Hoping to hear experts views. :o

UPDATE: Thank you all for quick replies! Primarily I would like to understand why it is so common practice to have a surrogate key instead of composite key in tables such as audit tables (there are other examples but trying to keep conversation focused). In such a table I could easily identify unique record by combination of event,userid and timestamp. Still most of the designs I researched online utilizes keys such as event_id. I am trying to understand why that if there is any real reason? In fact, wouldn't that mean consuming up unnecessary storage of db?

I need to make an Oracle select query. In the select query I need to manipulate one column by passing it into a stored procedure which returns an ou...More

I need to make an Oracle select query. In the select query I need to manipulate one column by passing it into a stored procedure which returns an out parameter that I need. I cannot figure out how to do this.

Example

Select someCol, StoredProc1(someCol2, outParameter) as tmpColumn
FROM TABLE X

I am unable to convert this stored procedure to a function. I'd rather not do the first select statement then do n+1 selects calling the stored procedure once I have the original results.

Obviously the above query doesn't work, and I'm doubtful that what I'm asking is possible from a lack of google results....so as a last ditch effort, is there any way to do what I'm attempting?

I am just curious on how to make sure that the query returns null values too along with other values.

UPDATE: The fo...More

I am just curious on how to make sure that the query returns null values too along with other values.

UPDATE: The following query is supposed to output a table with a location id, visit date visit time and bus name for each visits. The query below gives the output but does not include the nulls like in the table below. The result should include nulls also.

There are 3 tables in total: Location1, bus & visit. So I am using this(below) query to extract IDs and join all the tables. The query looks for visits only to Florida.

For instance, take the following query:

SELECT LOC.LOC_ID, VISIT.VISIT_DATE, VISIT.VISIT_TIME, BUS.BUS_NAME 
FROM BUS, LOCATION1, VISIT
WHERE VISIT.BUS_ID = BUS.BUS_ID 
AND VISIT.LOC_ID = LOCATION1.LOC_ID 
AND BUS.BUS_NAME IN (
SELECT BUS_NAME 
FROM BUS 
WHERE BUS_ID IN (
SELECT BUS_ID 
FROM VISIT 
WHERE LOC_ID IN (
SELECT LOC_ID 
FROM LOCATION1 
WHERE LOC_NAME='Florida')));

Let us suppose, there are visits which have not yet been assigned a bus and thus some values are null.

So, from what I believe using IN operator will return set of values but not the null values. What if i use exists instead?

The table can look like this:

 Loc_name   Loc_id         visit_date          visit_time              bus_name
  Florida     1            26-mar-2009           2:00pm                  xyz
  Florida     3            29-jul-2010           3:00pm                  abc
  Florida     8            22-may-2013           2:50pm                  (null)
  .
  .
  .

hope someone can help me with this.

thanks

When I call my function from my package like the following, it works. It is a bit complex but there are a lot of conditional variable.

More

When I call my function from my package like the following, it works. It is a bit complex but there are a lot of conditional variable.

resultval := TG_PKG_ENTITY_TOTAL.user_entity_total('0012345678',
    '01-JUL-1955','30-JUN-2013',amounttype,accountrec,deptrec,appealrec,
    allocschoolrec,isunrestricted,pledge_exclude,pledgetypes_exclude,
    honormem_exclude) ;

If I try to use it as part of a select statement, it does not work. Any ideas how to fix this? I think it may have to do with the external references to custom data type. But I would think the first example would fail also but it works.

The following fails.

SELECT e.id_number,TG_PKG_ENTITY_TOTAL.user_entity_total(e.id_number,
    '01-JUL-1955','30-JUN-2013',amounttype,accountrec,deptrec,appealrec,
    allocschoolrec,isunrestricted,pledge_exclude,pledgetypes_exclude,
    honormem_exclude) AS SoftCredit
FROM advance.entity e

*FAIL means that it raises error 6550 wrong number or type of argument

The following types are defined in a package called TG_PKG_TYPES

TYPE string_array IS TABLE OF VARCHAR2(500);

TYPE account_rec IS RECORD
(condition VARCHAR2(20), 
 account_array string_array);

TYPE dept_rec IS RECORD
(condition VARCHAR2(20), 
 dept_array string_array);

TYPE allocschool_rec IS RECORD
(condition VARCHAR2(20), 
 school_array string_array);

 TYPE appeal_rec IS RECORD
(condition VARCHAR2(20), 
 appeal_array string_array);



 TYPE amounttype_rec IS RECORD
 (GiftAmt                BOOLEAN :=FALSE,
  GiftCreditAmt          BOOLEAN :=FALSE,
  PaymentAmt             BOOLEAN :=FALSE,
  PayCreditAmt           BOOLEAN :=FALSE,
  CompanyMatch           BOOLEAN :=FALSE,
  EmployeeMatch          BOOLEAN :=FALSE,
  EmpSpouseMatch         BOOLEAN :=FALSE,
  EmpMatchClaim          BOOLEAN :=FALSE,
  EmpSpouseMatchClaim    BOOLEAN :=FALSE,
  PledgeAmt              BOOLEAN :=FALSE,
  PledgeCreditAmt        BOOLEAN :=FALSE);

FUNCTION PARAMS:

FUNCTION user_entity_total( id_number VARCHAR2 := NULL,
                            i_start_date VARCHAR2 :=NULL, 
                            i_end_date VARCHAR2 :=NULL,
                            i_amount_type tu_pkg_types.amounttype_rec :=NULL,
                            i_account IN tu_pkg_types.account_rec :=NULL,
                            i_dept IN tu_pkg_types.dept_rec :=NULL,
                            i_appeal IN  tu_pkg_types.appeal_rec :=NULL,
                            i_allocschool IN  tu_pkg_types.allocschool_rec :=NULL,
                            i_isunrestricted CHAR :=NULL,
                            i_excludepledge BOOLEAN :=FALSE,
                            i_excludepledgetypes BOOLEAN :=FALSE,
                            i_excludehonormem BOOLEAN :=FALSE) RETURN NUMBER;

I am trying to debug/test my application on a Windows 8 64-bit virtual machine running on Oracle VM VirtualBox 4.2.12 (current release as of this po...More

I am trying to debug/test my application on a Windows 8 64-bit virtual machine running on Oracle VM VirtualBox 4.2.12 (current release as of this post) running on a Windows 7 64-bit host.

Everything works fine, but two things do not work.

1. I am not able to get clipboard sharing to work, even if I select Devices | Shared Clipboard and any of the options besides disabled. I get nothing.

2. The other issue is that I cannot get my USB stick to read, even if I select Devices | USB Devices | and make sure that it is checked. nada.

Everything else works okay though. I was wondering if anyone else experienced this problem and knows of a solution.

I am trying to do something like this:

SELECT * FROM 
(SUBQUERY A) A 
JOIN (SUBQUERY B) B 
ON A.FIELD = B.FIELD

In...More

I am trying to do something like this:

SELECT * FROM 
(SUBQUERY A) A 
JOIN (SUBQUERY B) B 
ON A.FIELD = B.FIELD

Individually, A runs really fast (about 5 seconds) and B runs in about 15 -20 seconds. But when I try to make this join, it just take several minutes to run. I know that query runs B for each line of A. Those subquerys involves very big tables, but return a small quantity of registers.

I want to know a way to force A and B run individually, then buffer the results and finally run the join query just in the results

Thanks!

Write an anonymous PL/SQL block to raise the salaries with 5%. Take care of the following:

• The employees with the lowest salary receiv...More

Write an anonymous PL/SQL block to raise the salaries with 5%. Take care of the following:

• The employees with the lowest salary receives the salary raise of 5% first. Next the employee with the second lowest salary and so on. The employee with the highest salary gets his of hers salary raise last.
• The maximum amount to be spend on the salary raise is € 15.000,-. If this amount is reached the salary raise is stopped. All other employees will not receive a raise! All employees who already got the raise keep it.
• Display how many employees received the salary raise.
• Display the exact amount spend on the salary raise.

I have to create a sql query for a new report. The problem is that I have to do 11 Left Outer Join !!! Oh yes this is the ...More

I have to create a sql query for a new report. The problem is that I have to do 11 Left Outer Join !!! Oh yes this is the bad part. When I execute the query Oracle generate this error :

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

I am sure that this error is triggered because there are a lot of reocrds to handle. Do you have any advice that could help me please?

Thanks, Walloud

Within the oracle database 11g the table FLASHBACK_TRANSACTION_QUERY provides the UNDO_SQL column which S...More

Within the oracle database 11g the table FLASHBACK_TRANSACTION_QUERY provides the UNDO_SQL column which SQL reverses the modification done by a specific previously executed query.

However, I need the REDO_SQL (the original SQL which has been executed) instead. Is there a way (using SQLs) to extract REDO_SQL information from the oracle flashback area?

I'm trying to run the following query:

select a.*, 
    case when NVL (SELECT max(b.field1)
        FROM b
        where b.field2 = ...More

So, a few weeks ago, I asked about Oracle execution plan c...More

So, a few weeks ago, I asked about Oracle execution plan cost vs speed in relation to the FIRST_ROWS(n) hint. I've run into a similar issue, but this time around the ORDERED hint. When I use the hint, my execution time improves dramatically (upwards of 90%), but the EXPLAIN PLAN for the query reports an enormous cost increase. In this particular query, the cost goes from 1500 to 24000.

The query is paramterized for pagination, and joins 19 tables to get the data out. I'd post it here, but it is 585 lines long and is written for a vendor's messy, godawful schema. Unless you happened to be intimately familiar with the product this is used for, it wouldn't be much help to see it. However, I gathered the schema stats at 100% shortly before starting work on tuning the query, so the CBO is not working in the dark here.

I'll try to summarize what the query does. The query essentially returns objects and their children in the system, and is structured as a large subquery block joined directly to several tables. The first part returns object IDs and is paginated inside its query block, before the joins to other tables. Then, it is joined to several tables that contain child IDs.

I know that the CBO is not all knowing or infalible, but it really bothers me to see an execution plan this costly perform so well; it goes against a lot of what I've been taught. With the FIRST_ROWS hint, the solution was to provide a value n such that the optimizer could reliably generate the execution plan. Is there a similar kind of thing happening with the ORDERED hint for my query?

Am attempting to design an Ab Initio load process without any Ab Initio training or documentation. Yeah I know. A design decision is: for the incomi...More

Am attempting to design an Ab Initio load process without any Ab Initio training or documentation. Yeah I know. A design decision is: for the incoming data files there will be inserts and updates. Should I have the feed provider split them into to data files (1 - 10 GB in size nightly) and have Ab Initio do inserts and updates separately?

A problem I see with that, is data isnt always what you expect it to be... And an Insert row may be already present (perhaps purge failed or feed provider made a mistake) Or UPdate row isnt present.

So I'm wondering if I should just combine all inserts and updates... and use Oracle Merge statement (after parallel loading the data into a staging table with no index of course)

But I don't know if AbInitio supports Merge or not.

There is not much for ab initio tutorials or docs on web... can you direct me to anything good?

how can i populate a table of records wich has more than one field of %rowtype using bulk collect?

my code:

      dro...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 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 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

The ODP.NET OracleCommand class has a CommandTimeout property that can be used to enforce a timeout for the execution of a command. This property s...More

The ODP.NET OracleCommand class has a CommandTimeout property that can be used to enforce a timeout for the execution of a command. This property seems to work in situations where the CommandText is a SQL statement. The example code is used to illustrate this property in action. In the initial version of the code, the CommandTimeout is set to zero - telling ODP.NET not to enforce a timeout.

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;"))
            using (OracleCommand cmd = new OracleCommand())
            {
                con.Open();                
                cmd.Connection = con;

                Console.WriteLine("Executing Query...");

                try
                {
                    cmd.CommandTimeout = 0;

                    // Data set SQL:
                    cmd.CommandText = "<some long running SQL statement>";
                    cmd.CommandType = System.Data.CommandType.Text;

                    Stopwatch watch1 = Stopwatch.StartNew();
                    OracleDataReader reader = cmd.ExecuteReader();
                    watch1.Stop();
                    Console.WriteLine("Query complete.  Execution time: {0} ms", watch1.ElapsedMilliseconds);

                    int counter = 0;
                    Stopwatch watch2 = Stopwatch.StartNew();
                    if (reader.Read()) counter++;
                    watch2.Stop();
                    Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds);

                    Stopwatch watch3 = Stopwatch.StartNew();
                    while (reader.Read())
                    {
                        counter++;
                    }
                    watch3.Stop();
                    Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds);
                    Console.WriteLine("Records read: {0}", counter);
                }
                catch (OracleException ex)
                {
                    Console.WriteLine("Exception was thrown: {0}", ex.Message);
                }

                Console.WriteLine("Press any key to continue...");
                Console.Read();
            }
        }
    }
}

Example output for the above code is shown below:

Executing Query...
Query complete.  Execution time: 8372 ms
First record read: 3 ms
Records 2..n read: 1222 ms
Records read: 20564
Press any key to continue...

If I change the CommandTimeout to something like 3...

cmd.CommandTimeout = 3;

...then running the same code produces the following output:

Executing Query...
Exception was thrown: ORA-01013: user requested cancel of current operation
Press any key to continue...

Calling a stored procedure that returns a ref cursor is another matter though. Consider the test proc below (purely for test purposes):

PROCEDURE PROC_A(i_sql VARCHAR2, o_cur1 OUT SYS_REFCURSOR)
is
begin

    open o_cur1
    for
    i_sql;

END PROC_A;

The example code below can be used to call the stored proc. Note that it sets the CommandTimeout to a value of 3.

using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection con = new OracleConnection("User ID=xxxx; Password=xxxx; Data Source=xxxx;"))
            using (OracleCommand cmd = new OracleCommand())
            {
                con.Open();                
                cmd.Connection = con;

                Console.WriteLine("Executing Query...");

                try
                {
                    cmd.CommandTimeout = 3;

                    string sql = "<some long running sql>";
                    cmd.CommandText = "PROC_A";
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add(new OracleParameter("i_sql", OracleDbType.Varchar2) { Direction = ParameterDirection.Input, Value = sql });
                    cmd.Parameters.Add(new OracleParameter("o_cur1", OracleDbType.RefCursor) { Direction = ParameterDirection.Output });

                    Stopwatch watch1 = Stopwatch.StartNew();
                    OracleDataReader reader = cmd.ExecuteReader();
                    watch1.Stop();
                    Console.WriteLine("Query complete.  Execution time: {0} ms", watch1.ElapsedMilliseconds);

                    int counter = 0;
                    Stopwatch watch2 = Stopwatch.StartNew();
                    if (reader.Read()) counter++;
                    watch2.Stop();
                    Console.WriteLine("First record read: {0} ms", watch2.ElapsedMilliseconds);

                    Stopwatch watch3 = Stopwatch.StartNew();
                    while (reader.Read())
                    {
                        counter++;
                    }
                    watch3.Stop();
                    Console.WriteLine("Records 2..n read: {0} ms", watch3.ElapsedMilliseconds);
                    Console.WriteLine("Records read: {0}", counter);
                }
                catch (OracleException ex)
                {
                    Console.WriteLine("Exception was thrown: {0}", ex.Message);
                }

                Console.WriteLine("Press any key to continue...");
                Console.Read();
            }
        }
    }
}

Example output from the code above is shown below:

Executing Query...
Query complete.  Execution time: 34 ms
First record read: 8521 ms
Records 2..n read: 1014 ms
Records read: 20564
Press any key to continue...

Note that the execution time is very quick (34 ms) and that a timeout exception was not thrown. The performance that we see here is because the SQL statement for a ref cursor is not executed until the first call to the OracleDataReader.Read method. When the first Read() call is made to read the first record from the refcursor, then the performance hit from the long running query is incurred.

The behavior that I've illustrated means that the OracleCommand.CommandTimeout property can't be used to cancel a long running query associated with a ref cursor. I'm not aware of any property in ODP.NET that can be used to limit the execution time of a ref cursor SQL in this situation. Anyone have any suggestions on how the execution of a long running ref cursor SQL statement could be short circuited after a certain amount of time?

I have added a field to a Payroll Interface definition. I am using the delivered field TEXT254. The field where you define the length of the field ...More

I have added a field to a Payroll Interface definition. I am using the delivered field TEXT254. The field where you define the length of the field in bytes (field definition table) is three characters, so it would appear that you can define the length as 999 bytes. The PI process fails when I set the length to 999 bytes, until I lowered it to 150 bytes. I am experimenting, with it, slowly increasing the value I'm wondering if anyone knows what the limit really is? Our PI takes 3 hours to run, so experimenting takes a long time.

edit - I cut down the runtime by getting rid of all but one company. The largest byte size that I seem to be able to get to run is 240. I did some research, and it looks like when you build your tables, Oracle will set the field to VARCHAR2(n*3) where n is the size of the field specified in AppDesigner. Sure enough, the script generated by the Project...Build sets my field to VARCHAR2(762).

We need an exact search for arbitrary character sequences in a CLOB column. The search must be index based due to performance reasons.

At th...More

We need an exact search for arbitrary character sequences in a CLOB column. The search must be index based due to performance reasons.

At the moment we use n-grams stored in separate tables.

With Oracle Text we have learned that a tokenizer is needed. To match our "exact and character wise search" requirement this tokenizer must split our initial CLOB into single characters. No character can be ignored. Because inverted text indexes are used we doubt if this solution is appropriate.

Another option would be to leverage Lucene and Java with Oracle (supposing that Lucene supports exact character wise matching).

Are there any other options?

I have a pl/java class in a running Oracle database that I have misplaced the source cod...More

I have a pl/java class in a running Oracle database that I have misplaced the source code to.

Is there anyway to get the java bytecode back out the database so I could run it against a decompiler?

I've already checked ALL_SOURCE and Oracle claims it doesn't have the source code.

We have ASP.NET app that connects to oracle database with odp.net.

Lately we started to experienced some performance issues. It seems that O...More

We have ASP.NET app that connects to oracle database with odp.net.

Lately we started to experienced some performance issues. It seems that Oracle connections do not close and eventually pile up until it crash our website.

As a first step we did a code review and we made sure that we close all open connections after executing.

OracleConnection cn = Helpers.ConnectToDB();
    try
    {

        cn.Open();
        //do somtehing
    }
    catch (Exception ex)
    {
        //log error
    }
    finally
    {
        cn.Close();
        cn.Dispose();
    }

but that didn't help, every several hours the connections are piling up and crash our website.

Here is the connections log from yesterday:

TO_CHAR(DATE_TIME,'DD/MM/YYYY   MACHINE STATUS  CONNECTIONS 
19/01/2012 14:40:03 WORKGROUP\OTH-IIS-1 ACTIVE  1   
19/01/2012 14:38:00 WORKGROUP\OTH-IIS-1 ACTIVE  2   
19/01/2012 14:35:57 WORKGROUP\OTH-IIS-1 ACTIVE  2   
19/01/2012 14:34:55 WORKGROUP\OTH-IIS-1 ACTIVE  28  
19/01/2012 14:33:54 WORKGROUP\OTH-IIS-1 ACTIVE  26  
19/01/2012 14:31:51 WORKGROUP\OTH-IIS-1 ACTIVE  34  
19/01/2012 14:30:49 WORKGROUP\OTH-IIS-1 ACTIVE  96  
19/01/2012 14:29:47 WORKGROUP\OTH-IIS-1 ACTIVE  73  
19/01/2012 14:28:46 WORKGROUP\OTH-IIS-1 ACTIVE  119 
19/01/2012 14:27:44 WORKGROUP\OTH-IIS-1 ACTIVE  161 
19/01/2012 14:26:43 WORKGROUP\OTH-IIS-1 ACTIVE  152 
19/01/2012 14:25:41 WORKGROUP\OTH-IIS-1 ACTIVE  109 
19/01/2012 14:24:40 WORKGROUP\OTH-IIS-1 ACTIVE  74  
19/01/2012 14:23:38 WORKGROUP\OTH-IIS-1 ACTIVE  26  
19/01/2012 14:22:36 WORKGROUP\OTH-IIS-1 ACTIVE  2   
19/01/2012 14:21:35 WORKGROUP\OTH-IIS-1 ACTIVE  2

Crash point occurred at 14:27:44 and after restarting the application the connections started to drop down.

the connection string we using is:

<add name="OracleRead" connectionString="Data Source=xxx;User Id=yyy;Password=zzz;Max Pool Size=250;Connection Timeout=160;" providerName="Oracle.DataAccess"/>

So what is the problem here?

Do we need to define or change one of these properties:

Connection Lifetime, Decr Pool Size, Max Pool Size, Min Pool Size?

What is the recommended settings in this situation?

I am experiencing a very strange problem This is a very simple use of JDBC connecting to an Oracle database OS: Ubuntu Java Version: 1.5.0_16-b02 ...More

I am experiencing a very strange problem This is a very simple use of JDBC connecting to an Oracle database OS: Ubuntu Java Version: 1.5.0_16-b02 1.6.0_17-b04 Database: Oracle 11g Release 11.1.0.6.0 When I make use of the jar file JODBC14.jar it connects to the database everytime When I make use of the jar file JODBC5.jar it connects some times and other times it throws an error ( shown below) If I recompile with Java 6 and use JODBC6.jar I get the same results as JODBC5.jar

I need specific features in JODB5.jar that are not available in JODBC14.jar

Any ideas

Error

> Connecting to oracle
    java.sql.SQLException: Io exception: Connection reset
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:494)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:490)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:202)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474)
    at java.sql.DriverManager.getConnection(DriverManager.java:525)
    at java.sql.DriverManager.getConnection(DriverManager.java:171)
    at TestConnect.main(TestConnect.java:13)

Code

Below is the code I am using

import java.io.*;
import java.sql.*;
public class TestConnect {
    public static void main(String[] args) {
        try {
            System.out.println("Connecting to oracle"); 
            Connection con=null;
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con=DriverManager.getConnection(
               "jdbc:oracle:thin:@172.16.48.100:1535:sample",
               "JOHN",
               "90009000");
            System.out.println("Connected to oracle"); 
            con.close();
            System.out.println("Goodbye");
        } catch(Exception e) { e.printStackTrace(); }
    }
}