PL/SQL :: When Oracle Invalidates Result_cache Results Without Any Changes In Objects

Jul 30, 2012

On our production servers we have simple function with result_cache, like this:

create or replace function f_rc(p_id number) return number result_cache
is
ret number;
begin
select t.val into ret from rc_table t where t.id=p_id;
return ret;
exception
when no_data_found then
return null;
end;

/And its results frequently invalidates without any changes in table or function. I found only 2 cases when oracle invalidates result_cache results without any changes in table:

1. "select for update" from this table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with "on delete cascade".

I test it on 11.2.0.1, 11.2.0.3, on solaris x64 and windows. Test cases:

[URL]......

But none of them can be the cause of our situation: we have no unindexed fk, and even if i lock all rows with "select for update", it still does not stop invalidating. In what other cases this happens? Am I right that the oracle does not track any changes, but the captures of the locks and "commits"?

View 2 Replies


ADVERTISEMENT

Performance Tuning :: Oracle 11g R2 Result_cache Is Not Working

Oct 25, 2010

I am querying a table having 6 million records,It takes arround 35 seconds in command line terminal using putty in a windows client.

Also the server is linux centos with oracle 11g installed. I saw the method Oracle result_cache which i am suspecting to be fast

my query is
"select /*+ result_cache */ * from relationshipobject;"
SQL> set autotrace traceonly stat
SQL> set timi on

[Code]....

i can i reduce my query execution time and consistent gets?

View 10 Replies View Related

SQL & PL/SQL :: Compiling Invalid Objects Results In ORA-03113 Error

Mar 19, 2011

I am getting the following error while compiling the invalid objects.

ERROR at line 2: ORA-03113: end-of-file on communication channel

I am trying to compile the invalid objects after importing the dump file.

View 1 Replies View Related

Index On Oracle 11g - Get Results Faster?

Nov 9, 2010

i have table (MEN) with 900,000 records.in this table i have field `IP` and `Tdate`.when i run query:

select * from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

it takes long time until i get Result.i try to make index like this:
create index
my_in
on
MEN (IP,Tdate );

but how to run the query to get fast Result?i try this:

select My_in from MEN where IP = '1.1.1.1' and Tdate = TO_DATE('07/04/2010', 'DD/MM/YYYY')

and get error: `ORA-00904`

View 1 Replies View Related

SQL & PL/SQL :: Connecting From Oracle To Excel To Copy Results In Cell Value

Aug 29, 2012

How can I connect excel from pl / sql to copy results from the query. I was trying with OLE2 but it is throwing error as "invlid Identifier". Then how to include OLE2 in oracle.

View 10 Replies View Related

Connect To Oracle DB And Write SQL Results Into Excel File

Sep 27, 2013

I would like to connect to oracle database and would like to execute series of SQLs and write those SQL results into Excel file (sheet1).

my requirement is simple :
------------------------
db_user_id scott
db_user_pwd tiger
db_sid orcl

select count(*) from emp;
select count(*) from dept;

print the same SQLs in A column and result should be in B column in excel file.

column A column B
select count(*) from emp; 14
select count(*) from dept; 4

thats it.

View 1 Replies View Related

Server Administration :: Difference In SQL Results From Oracle 9i To 11gR2?

Jan 2, 2011

I've migrated from Oracle 9i to Oracle 11gR2, when i'm checking my application on Oracle 11gR2, i found that some of the query result are differed from the previous version of Oracle 9i, which is very illogical to me. I've checked the data for all related tables which are involved in one of the query. I've checked all indexes also. Still i'm not convinced that Oracle is doing anything wrong. I cannot recreate the whole scenario here, because of many tables involved in that query.

One of the sample query code.

select 'ProductMaster' producttype,e.schemecd,e.SCHEMESHORTDESC as SCHEMEDESC,to_char(e.schemefrdate,'dd/MM/yyyy')as schemefrdate, to_char(e.schemetodate,'dd/MM/yyyy') as schemetodate,e.mkid ,e.countrycd,e.statecd,e.districtcd,f.schemetype, a.productcd,a.packsizecd,a.packtypecd,a.sortorder1, a.countrycd,a.statecd,a.districtcd,a.p_uniqueid,a.mrp, (a.productcd ||'-'||b.countryname||'-'||c.statename||'-'||d.districtname|| ' -pkt.prc:'||a.pktprice ||' -mrp:'||a.mrp) as pcode
from wb.wbproductdetails a

[code]......

View 17 Replies View Related

Migrate MySQL 5.0 Objects To Oracle 11g

Jan 23, 2013

I`m migrating a mysql 5.0 database to Oracle 11g. I used Oracle's Sql developer and with a workaround i could convert tables, constraints, indexes, some triggers and created sequences.

My question is, what happen with stored procedures and functions, they didn't pass. I've been searching and i found here hey have to be rewritten. Is there any way to convert those objects ? I can't afford to do it manually because the mysql database has almost 400 of them and they aren't small.

View 2 Replies View Related

PL/SQL :: Oracle Objects - Insert Particular Attributes

Apr 4, 2013

I'm using the below data base version

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

I have created a pl sql type as object with five attributes as below.Now im trying to insert only 3 attributes with the attribute names ( As like insert into statement with column names).

Create type address_test as object (
              Streetno varchar2(20),
              Locality varchar2(20),
              City varchar2(20),
              Pin varchar2(7),
              Country varchar2(20))
[code]....

Is that possible to use attribute names in the insert statement of column object type ?

View 9 Replies View Related

Loose Coupling Among Objects Within Oracle Schema?

Sep 30, 2010

I am building an information service that manages Suppliers. The suppliers are used by our billing system, tender system and sales system. Though 60% of the attributes of supplier are unique to each system, there are still 40% attributes of Supplier that are shared across the systems.

My objective is to build a flexible system, so that change to one individual system's data, should not impact other systems. For example, if i need to make certain tables offline for upgrading them, it should not impact rest of the systems that need supplier information. What is the best way of achieving this? Should all the different context specific attributes live in one schema, but deployed on different table spaces? Also, the read and update may happen more for one set of attributes than the other. How should i logically represent them via one model, but deploy them in such a fashion that they can evolve independently?

View 3 Replies View Related

SQL & PL/SQL :: Handle Customer Specific Objects In Oracle?

Nov 25, 2012

the more customers we have for our software solution the more individual oracle objects (Tables, Packages, Functions, etc.) we have in our scheme. Right now we separate these by giving them identifying names like "X_CUSTOMER1_TABLENAME" e.g. (I know ..... )

This is not very practical when keeping our reference clean and when deploying/syncing our reference with a customer db: One customer would receive the objects of other customers on a deploy.

Is there a common solution to this problem? We were thinking about having a separate scheme for each customer. That way we would have our standard, untouched scheme with the basic functionality and the customer schemes with the individual content.

To make it a bit more concrete: We have around 100 basic tables that make the most of the content/functionality of the software. Each customer might have between 1 -5 additional tables with "custom" data that is used in conjunction with the standard objects in individual packages, functions etc. The installations will be made on our customers systems. So I have in mind to have schemes 0001, 0002, etc for each customer IN OUR REFERENCE. But we would then deploy only the scheme for that certain user when installing on their system. So for example for customer 0001 I would deploy the STANDARD and 0001 scheme

View 6 Replies View Related

Oracle 11g - Record Query And Time To Fetch Results For A Specific User ID

Jul 22, 2013

I have an application connected to Oracle 11g that sends its own querys to the db based on what the user is clickng on. The applicaiton is connected via one user id and I was wondering, is there a way that I can capture the tiem each query starts, the sql itself, and the amount of time it took to fetch the data?

View 7 Replies View Related

SQL & PL/SQL :: Create Oracle Objects (Views) Using Conditional Compilation?

Jun 6, 2011

it is possible to create Oracle objects(Views) using Conditional compilation?

View 5 Replies View Related

Convert SQL Server DB To Oracle 10g - Optimizing View And Its Objects?

Oct 8, 2010

I'm converting a SQL Server db to Oracle 10g. So far it's going pretty well. However, I've hit a significant performance snag trying to run queries against the converted view whose SQL is below. In SQL Server it runs pretty quickly, 10-15 secs. This query presently returns about 1.7 million records. code below, and assuming I've done nothing to optimize the database or the objects involved,

BS
------------------
CREATE VIEW DST_TMP_VIEW_ACCT_XACTN_CRN AS
SELECT CUSTACCNUM,
FUNDID,

[code]...

View 2 Replies View Related

JVM :: How To Extract Java Class Objects From Oracle Database

Jun 26, 2013

How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.

All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup. 

View 1 Replies View Related

SQL & PL/SQL :: Access Attributes Of Objects Of Nested Table In Member Function Of Oracle Database

Nov 17, 2011

I have created two types and a list of the first type:

create type type1;
/
create type type1_list as table of ref type1;
/
create type type2;
/

I have now just created the two types as follows:

create type type1 as object(
id# number
);
/
create type type2 as object(
attribute1 type1_list,
MEMBER FUNCTION function1 RETURN NUMBER
);
/

Ok, I've created the tables (I don't know if it's necessary to point out my problem)

CREATE TABLE type1_table OF type1;
/
CREATE TABLE type2_table OF type2
NESTED TABLE attribute1 STORE AS nested_type1_list_table;
/

And what I wanted to do now is to implement the member function1 and check something of the attributes of type1 in the list of attribute1... And that's where my question occurs, how does it work, I can't figure it out. I tried something like this:

Quote:
create or replace
TYPE BODY type2 AS
MEMBER FUNCTION function1 RETURN NUMBER AS

[Code]....

But I don't get the right way, it doesn't work

View 4 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

SQL & PL/SQL :: Getting Duplicate Results

Feb 7, 2012

I am getting duplicate results..I have been at it for hours now.This is what I have:

DECLARE
v_course_id classes.course_id%TYPE := :course_id;
v_instr_id classes.instr_id%TYPE := :instructor_id;
[code]...

The output I get is this (There is only 1 of each in the database)

Class ID: 1 Status: Enrolled Name: John O'Reilly
Class ID: 1 Status: Enrolled Name: Natacha Hansen
Class ID: 1 Status: Enrolled Name: Reed Jetto
Class ID: 1 Status: Enrolled Name: Janis Greenberg
Class ID: 1 Status: Enrolled Name: Vishal Singh
Class ID: 1 Status: Enrolled Name: Francis Hamilton
[code] ...

View 2 Replies View Related

Calling SP Results In ORA-00911

Apr 20, 2007

I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.

call get_all_customer_orgs;

The get_all_customer_orgs procedure is defined as.

create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor)
is
begin
open p_recordset for
select * from customerorgs;
end get_all_customer_orgs;

I don't understand what the invalid character could be.

View 5 Replies View Related

Any Way To Pivot Results Of A Query?

Apr 23, 2007

Is there anyway to pivot the results of a query?

so if i have:

SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP

And it give the following output:

A 10
B 50
c 24

Is there anyway to put into this format?

A B C
10 50 24

I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!

View 1 Replies View Related

Select Field Where Results Are The Same

Feb 26, 2008

I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.

lets see .. so a count of records where widget = widget or something along those lines.

View 9 Replies View Related

Getting Total Results Count

Aug 27, 2010

I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".

Currently I am doing this by submitting a second query:

select count(*) from (<previous query criteria>)

Is there a better performing approach I could be using?

View 4 Replies View Related

SQL & PL/SQL :: Display Results Using CASE WHEN?

May 24, 2011

I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).

What do I need to do to display 'data' if 'data2' contains rows?

WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,

[Code]....

View 11 Replies View Related

SQL & PL/SQL :: Results Different After Using Order By Clause

Mar 18, 2011

Result are different after adding the Order By clause in below query.

Query

select sum(wpsv1.primary_qty) detail_qty,
wpsv1.from_subinventory subinventory,
wdd.revision revision,

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Grouping Splits Results?

Jun 6, 2013

SELECT
pas_code,
pas_profile,
count(sutp_id),
sum(sutp_price),

[code]...

And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.

pas_codepas_profilesutp_idsutp_pricex
2664good stuff310069< because pbk_price is like 67 from that period
2664good stuff310071< because pbk_price is like 50 from other period

how to get all results in a single line like:

pas_codepas_profilesutp_idsutp_pricex
2664good stuff6200140

View 3 Replies View Related

SQL & PL/SQL :: To Get The Results In The Form Of A Tree

Nov 25, 2012

My table structure is like this

CREATE TABLE Tab1(LVL1_NM VARCHAR2(20), LVL2_NM VARCHAR2(20), LVL3_NM VARCHAR2(20), LVL4_NM VARCHAR2(20))

and the values will be for one value of LVL1_NM there will be different LVL2_NM values like wise for each and every value of LVL2_NM column there will different values in LVL3_NM column like wise for LVL4_NM is there any way to get the results as per their relation ships.

View 7 Replies View Related

SQL & PL/SQL :: Grouping Results By Ranges?

Oct 21, 2010

I have this table,

Create table TBL_OK_HIST
(
DATE_KEY NUMBER,
A_N VARCHAR2(22 BYTE),
R_DUR VARCHAR2(8 BYTE),
CH_DUR VARCHAR2(8 BYTE),
REV VARCHAR2(20 BYTE)
)

insert into TBL_OK_HIST
values (20101010,123456768,5,20,2);
insert into TBL_OK_HIST
values (20101010,123496568,15,20,2);
insert into TBL_OK_HIST
values (20101012,122235768,25,25,3);
[Code] ......

Thus, applying the following would yeld:

Select * from TBL_OK_HIST

DATE_KEYA_N R_DUR CH_DUR REV
201010101234567685202
2010101012349656815202
2010101212223576825253
201010112345676819202
2010101234567681252527
2010101323456768136365
2010101056768123411202
2010101134681256717202
2010101068123456755559

generate the following results:

range_start_rdur range_end_rdur no_of_an sum_of_rdur sum_of_chdur sum_of_rev
1 5 1 5 20 2
6 10 1 9 20 2
11 15 2 26 40 4
16 20 1 17 20 2
21 25 1 25 25 3
26 30 0 0 0 0
31 35 0 0 0 0
36 40 1 36 36 5
41 45 0 0 0 0
46 50 0 0 0 0
51 55 2 107 107 16

I thought I would make use of the following query, but I am not getting the proper results when applying it to a real table with more than 20 mln records:

SELECT trunc(R_DUR/6)*5+1 as range_start_rdur,
trunc(R_DUR/6)*5+5 range_end_rdur,
sum(noofan) as no_of_an,
sum(sumofrdur) as sum_of_rdur,
sum(sumofchdur) as sum_of_chdur,
[Code] ...........

View 1 Replies View Related

SQL & PL/SQL :: Search B If Not In A - But Only Use Results From A If In Both Tables?

Nov 11, 2010

I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".

Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.

The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.

Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.

I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...

View 9 Replies View Related

PL/SQL :: Get Expected Results From Pivot?

Jan 31, 2013

with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
union all
select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
select param_name,param_type,param_value from t1;

desired output:

need output in a row in three different columns

param_value
01-01-1970    31-12-9999 30

I tried below query

SELECT *   
FROM   (
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual

[code]...

and am getting output as

param_type      eff_date_param_value   disc_date_param_value   initial_ignoring_param_value
mbn256_type    <null>                                 <null>                           <null>

View 2 Replies View Related

PL/SQL :: How To Get Added Hours From Results

Jun 14, 2012

I am not getting added hours from results.

SELECT audittimestamp + interval (SELECT      EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved