SQL & PL/SQL :: Calling A View That Calls Other?

Mar 16, 2011

Now, I am trying to come with an alternative. My first thought was to use materialized views with Fast Refresh - refresh only changed rows. This might not even work because there are many contraints when using Fast refresh. I am in the process of learning their data model so I do not know much.

Have you ever encountered a problem like this?

View 7 Replies


ADVERTISEMENT

User Calls / Executes

Nov 8, 2012

Please explain :

What is the difference between : User calls and executes in

AWR Load Profile :

User calls:     1,373.51     0.80
Executes:      8,558.06     4.98

As i understand , than User calls is : the number of DML (insert , update, Delete and Select ) cursor : parse,execute , fetch , and execute is ?

Why executes > User calls ?

Or may be one pl/sql package procedure may execute many SQL ? How to explain this ?

View 2 Replies View Related

Active Calls From Remote Machines

Mar 24, 2011

My Oracle DB 10g R2 running on AIX 5.3 OS. When I am shutting down the oracle I see that it is waiting for complete the active calls and I determined that these active calls/processes are from remote machines.

When I try to run this ps -ef as shown below, it shown me number of oracle process with LOCAL=NO

/home/oracle> ps -ef | grep 91334
oracle 85210 87320 0 12:47:56 pts/35 0:00 grep 91334
oracle 91334 1 0 12:45:11 - 0:00 oracleIDB (LOCAL=NO)

What are the possible scenarios for this LOCAL=NO? How do I find that this process is from which machine or IP address?

View 2 Replies View Related

SQL & PL/SQL :: How To Avoid Multiple Function Calls

Jul 23, 2013

I have a function that is being called three time using UNION and wanted to know if this can be improved to just one call while incorporating all the table joins.

select field1,fdate,fname,username,stepnum from (
SELECT M.FIELD1,
TO_CHAR (M.FIELD_DATE, 'MM/DD/YYYY HH24MISS') AS FDATE,
M.FIELDNAME AS FNAME,
M.USERNAME,

View 10 Replies View Related

PL/SQL :: Find Common And Internal Calls?

Jul 19, 2013

create table call(id number(15) primary key, calling_no varchar2(15), called_no varchar2(15), calldate date, calltime timestamp, duration number(10) ,calltype varchar2(10));  1. CALL DETAILS OF 9891826547insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (1, 9891826547, 9891210785, to_date('01-Jun-13','dd-Mon-yy'), to_date('19:12:00','hh24:mi:ss'),10,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (2,9891826547, 9899985476, to_date('01-Jun-13','dd-Mon-yy'), to_date('22:10:12','hh24:mi:ss'),50,'OUT'); insert into call (id,calling_no,called_no,calldate,calltime,duration,calltype) values (3, 9891826547, 9818415767, to_date('02-Jun-13','dd-

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Using Analytic Function To Determine Maximum Concurrent Calls?

Apr 27, 2010

I have a requirement to calculate the maximum number of concurrent calls from the following data:

Create_date connect_date_time disconnect_date_time duration ...
12/01/10 13:20:26 1263253551 1263254153 602
...

I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.

e.g.

SELECT
count(*) calls,
avg(duration)/60 average_duration_mins,
max(duration)/60 max_duration_mins,
sum(duration)/60 total_mins,
(SUM(DURATION)/60)*0.04 total_cost_4c_per_min

[code]....

View 7 Replies View Related

SQL & PL/SQL :: Replacing Multiple Calls To Function By Table Joins

Dec 15, 2010

The following query calls the function get_meter_desc 8 times.

SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...

How can I replace the function call by the join in the main query?

View 19 Replies View Related

How To Provide The Description And Parameters Of Job Which Calls Package On Database

Nov 3, 2011

how to provide the description and parameters of job which calls package on database

package name is some 'xxxxx'

we need to find the job which call the package 'xxxxxx'.

View 2 Replies View Related

ODP.NET :: Can Multiple Calls Execute Within Single Oracle Session

May 8, 2013

Can multiple ODP.NET calls potentially execute within a single Oracle session due to connection pooling?

How can I test/observe this behavior?

We have an IIS application that uses one connection string for all logged in users and call aspx pages.

View 1 Replies View Related

SQL & PL/SQL :: Query To Fetch Steps Of Test Having Recursive Calls To Other Tests

Jul 17, 2012

I am working on the quality center oracle database to write a query that fetches all steps of a test case including the ones having calls to tests. Structure of table is explained below. I've made up an example and attached it as an image to this post. This image also has the expected result of the query I want to write.

Table Name: STEPS (This table contains steps belonging to tests. Some steps are simply calls/links to other tests)

Columns:
STEP_ID (primary key - integer)
STEP_NAME (char)
STEP_DESC (char)
ORDER (integer)
TEST_ID (reference to table TEST.test_id)
[code].......

Referring to the example (see attached image), I'm looking to write a query that gives me all steps (including steps from called tests) of the test - "Empty trash from mailbox" in the correct order.

To start with I can write the following query to get steps of the test - "Empty trash from mailbox".
SELECT * FROM steps WHERE test_id = (SELECT test_id FROM test WHERE test_name = 'Empty trash from mailbox')

View 7 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

Apr 25, 2012

We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.

This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?

I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....

View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.

View 6 Replies View Related

SQL & PL/SQL :: Oracle View Have Dynamic View Function?

Jul 28, 2010

Are oracle view have Dynamic view function?

View 8 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

Aug 12, 2013

what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?

View 7 Replies View Related

Calling Tables From PL / SQL

Jan 22, 2013

I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table. But its not working.

Example:
CREATE PROCEDURE TESTPROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('testing');
select count(*) from LOCATION;
END;

Compile error:
Error(5,22): PL/SQL: ORA-00942: table or view does not exist

View 2 Replies View Related

SQL & PL/SQL :: Calling From Ksh Script?

Aug 20, 2013

I have to write a .ksh script. In which i have to first pick the details using a sql query (select job from sys.dba_jobs where broken='Y';), Now for each job number i have to execute a procedure (EXEC DBMS_JOB.BROKEN('job number',FALSE);)

View 2 Replies View Related

Calling Procedure Using Trigger

Aug 26, 2013

I am in the process of creating a trigger for a procedure I created. The procedure is working fine but I keep getting an error on the trigger. I am getting Error(7,5): PL/SQL: Statement ignored AND Error PLS-00306: worng number of types of arguments in call to 'INSERTINTOPHYSLOG'

Here is my procedure:

create or replace PROCEDURE INSERTINTOPHYSLOG
AS
prec_num long;
srec_num long;

[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

Calling PL/SQL Procedure From JavaScript?

Oct 8, 2007

The application I am working on uses Oracle HTP procedures to generate HTML pages for our application. I am trying to perform some cleanup when a user closes a web application by clicking the 'X' button in there browser. I am thinking of using the onUnload trigger to call a javascript function, which in turn would call a procedure in my PL/SQL pacakge for cleaning up logical locks on records.

The body tag delaration looks like this code fragment:

htp.print('<BODY onUnload="clear_locks(' || p_user_id || ')"

Here is some psuedo code for the function i would like to use:

<SCRIPT>function clear_locks(p_user_id){
call clear_locks(p_user_id);
return true;
}</SCRIPT>

Note that clear_locks is a pl/sql stored procedure.So far I have gotten it to pass in the user ID correctly.Is there a way I can call my clear_locks procedure?

View 1 Replies View Related

SQL & PL/SQL :: Calling A Function With Parameters?

Feb 27, 2011

When I replace the params manualy with dates in format of:

'27-feb-2011 08:00:00'

I get the real output.

But when I call the function with the TEST button in the PLSQL I don't get any output (Empty table).

Here is the original function (Bad output) and attaching a file with manuly added dates (Good output):

FUNCTION WhatRoomsTaken(dStartTime date, dEndTime date, dEventDate date)
RETURN Genrefcursor IS
Retval Genrefcursor;
BEGIN
open Retval for

[code].....

Why do I need to change in order to get the good output by calling the function with params?

View 5 Replies View Related

SQL & PL/SQL :: Calling Script From KSH File?

Oct 4, 2013

scripts.sql file content
set verify off ;
set echo on ;
set termout off ;
set feedback off ;

[code]....

seems like the scripts.sql is not executed when called from the .ksh file.

View 8 Replies View Related

SQL & PL/SQL :: Procedure Calling Methods

Oct 14, 2013

I have created this procedure for printing ename,sal,job as output using empno as input:-

CREATE OR REPLACE PROCEDURE p_get (
p_empno NUMBER,
p_name OUT VARCHAR2,
p_sal OUT NUMBER,
p_job OUT VARCHAR2
)
IS
BEGIN
SELECT ename, sal, job
INTO p_name, p_sal, p_job
FROM emp
WHERE empno = p_empno;
END;

Now My Requirement I want to call this procedure using Positional,Named and Mix Methods...I am calling this procedure using Positional Method:-

declare
p_name varchar2(20);
p_sal number;
p_job varchar2(20);
begin
p_get(7369,p_name,p_sal,p_job);
dbms_output.put_line('Name='||p_name||' Salary='||p_sal||' Job='||p_job);
end;

how to call the same procedure using NAMED and MIX methods.how to call same procedure using NAMED and MIX methods.

View 11 Replies View Related

SQL & PL/SQL :: Calling A Remote Table In SP?

Feb 21, 2011

I have created a remote link to a sql server db in oracle 11.0.7 works fine in sql plus. I can select tables by select * from table@linkname...the link is a public link But when I put it in the procedure it says invalid table name. then I created a public synonym for the table - i can select from the synonym in sql plus - but in the procedure it says synonym translation invalid...Also I cant grant any privileges to the synonym as it says ddl operations are not allowed on remote objects

View 1 Replies View Related

SQL & PL/SQL :: Calling Procedure Into Function?

Aug 1, 2011

for my r&d purpose i create function which call procedure,

create table test_tab
(a NUMBER(10),
B NUMBER(10),
C NUMBER(10)

[Code]...

but it will gv me error ORA-06575:FUNCTION IS IN INVALID STATE that means function created with compilation error.

but when i complied fucntion it doesnt gv me any error.

View 15 Replies View Related

PL/SQL :: Calling Procedure In Loop

Oct 2, 2013

I am calling a procedure with the following parameters 

DECLARE     PROFIT_CENTER NUMBER;   BEGIN   PROFIT_CENTER:= 1109 --( Similarly I am running the proc for 5 more profit_centers 1123,1132, 1122,3211, 1111

one by one by passing values manually, it is taking almost a minute for each profit center

)Prc_test_calc (  PROFIT_CENTER);  COMMIT;   END;
For each profit_center proc is taking 1 mins (Approx) time.  

But when I am trying to loop it to call the procedure for each profit_center , I don’t know for what reason it is taking too much time for each profit_center. Anything wrong with the below loop  

DECLARE     PROFIT_CENTER NUMBER;  cursor c_profit is select distinct PROFIT_CENTER from plng_pc where profit_center in (1109, 1123,1132,1122,3211,1111); --

Plng_pc is a table from where profit_center information is getting fetched  

BEGIN For pc_rec in c_profit loop   Prc_test_calc (  pc_rec .PROFIT_CENTER );  COMMIT; End loop;   END;

View 9 Replies View Related

SQL & PL/SQL :: Calling A Function In Package

Mar 9, 2010

I have this following pakg

CREATE OR REPLACE PACKAGE pkg_test AS

-- Create a table type of the table you want
TYPE tbl_test IS varray(100) of VARCHAR2(30);

-- Function that will return the table type
FUNCTION fnc_test RETURN tbl_test;

-- End package
END;

CREATE OR REPLACE PACKAGE BODY pkg_test AS
FUNCTION fnc_test RETURN tbl_test IS
-- type table_name_va is varray(100) of VARCHAR2(30);
-- Variable of the type tbl_test

[code]...

But i am having problem calling this to test it.

declare
TYPE tbl_test IS varray(100) of VARCHAR2(30);
var_tbl_test tbl_test;
begin
var_tbl_test:= pkg_test.fnc_test;

[code]...

View 2 Replies View Related







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