SQL & PL/SQL :: Find Number Of Inserts Happened Every Day To A Particular Table?

Aug 9, 2013

Is there any way I could find the number of inserts happened every day to a particular table.

View 10 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create Counter For The Number Of Inserts Run In A Block

Feb 18, 2010

I have a plsql Proc, which accepts a few parameters and inevitably loops through a cursor and runs a bunch of insert statements. With quite a few IF conditions.

Each insert statement has a value which i want to increment by (+1) every time an insert statement is executed in the same loop.. This is for a student housing database and this is for their room preferences so 1 is the first, 2 is there second preference e.t.c.

Please take a look at the code below: in the Insert values() I have put a? Where I want the number to increment from.

There are a lot more inserts which I haven't put below. I hope I have made myself clear as this has been quite difficult to explain. So for example if the 2nd two inserts are run, then I was the first one to insert with a 1 and the second with a 2.

BEGIN
FOR rec IN c1
LOOP
IF c1%FOUND THEN
INSERT INTO table (PK_A, fk_rms_id, application_type, application_person_type) VALUES (NULL, rec.pk_rms_id, app_type, app_person_type) RETURNING PK_APPLICATION_NO INTO x;
[Code] ........

View 2 Replies View Related

SQL & PL/SQL :: Find Biggest Age Number In Table?

Mar 2, 2010

i want a simple query to find out for example the highest age in the table.

View 9 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Primary Keys Present In A Table?

May 17, 2011

is there any query to find number of primary keys present in a table.

View 13 Replies View Related

Parameters For Table Containing Only Inserts

Apr 1, 2013

I'm on 11.2 DB and need to create an audit table that will be populated by DB triggers of other tables (after Insert,Update and Delete). The triggers will only ever be inserting data into the audit table. I have read that for insert only tables, you should define the 'pctfree' as 0. Is this correct? Do I need to set any other params (like pct_used) for tables only ever being inserted into?

View 2 Replies View Related

SQL & PL/SQL :: Create A Procedure That Inserts Parameters Into A Table

Oct 20, 2011

I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;

HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;

STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....

I think im close just that my syntax is off.

View 8 Replies View Related

Server Administration :: How To See What Happened On Past 10 Days In DB

Jul 19, 2010

I am working in development environment which has only 150 gb db size but suddenly it is raised to 250gb within 10 days i want to see which user did what in my database.How we can see what task made what in past 10 days.

View 3 Replies View Related

Reports & Discoverer :: How To Find Page Number And Total Number Of Pages

Jan 26, 2010

i am using oracle developer 6i report builder i required this type of query

example

if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;

return (TRUE);
end;

but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.

View 34 Replies View Related

SQL & PL/SQL :: Count A List Of Number Value And Find Maximum Number?

May 21, 2010

how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1

View 5 Replies View Related

PL/SQL :: Track Table Data Changes (inserts / Updates / Deletes) Including Os_user

Mar 12, 2013

I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.

If I CREATE MATERIALIZED VIEW LOG on that table.

View 4 Replies View Related

SQL & PL/SQL :: How To Find Number Of Rows Of View

Oct 22, 2010

is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?

View 4 Replies View Related

SQL & PL/SQL :: How To Find Next Closest Number In Series

Jan 21, 2012

i have a data something like

010030
110495
210960
311425

[code]...

and user enters the figure like 13025now i would like to find the closest next value to the number entered by user, which is 713285 .how can i get this result, i have search a lot on Internet and i found some analytic functions can work out. but don't know how ..

View 14 Replies View Related

SQL & PL/SQL :: Query To Find The Number Of Alphabets?

Apr 20, 2013

I need to find out that assume i have a table having 2 column

Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani

I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets

View 6 Replies View Related

SQL & PL/SQL :: Find A Negative Number In String?

Apr 24, 2012

I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.

One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.

This is the code I am currently trying on and is not looking good.

SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'

View 13 Replies View Related

PL/SQL :: Find The Range Of Number Between Two Columns

Jul 12, 2012

I have a table with two columns Column1 and column2

Like such

create table testTable (column1 number(15), column2 number(15));

insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.

so my answer set should be
1,2,3,4,5

View 4 Replies View Related

How To Find Number Of Connections From Specific Client

Mar 13, 2013

We are interested to find the number of connections from specific client. Is tracing on sqlnet.ora in the client machine the answer? If yes, which trace has the information?

View 1 Replies View Related

How To Find Oracle Client Version Number In Nix

Mar 26, 2008

I want to find out the oracle client version on a particular server. How can I do that? I used to know some files to look at, and part of the file name indicated the version #, but it's been too long and I don't remember which files they are.

View 2 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Nulls For Each Record

Aug 18, 2013

We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.

provide a simple query using case in ORDER BY clause.

ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP

We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as

1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME

for the following three records number of NULL values are same. but positions are different.

0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP

Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17

Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as

ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL

View 15 Replies View Related

Reports & Discoverer :: Find Out Last Page Number?

Sep 29, 2011

I want to display a boilerplate item and their value only on the last page.

The item is under frame . So print object at last page doesn't work.

I could find the page number by the use of srw.get_page_num(n).

But not sure how to find the last page number.

View 2 Replies View Related

Forms :: How To Find Line Number Of Error In 6i

Jun 4, 2012

How to find the line number along with package name or procedure name (for ex. in Oracle 10g DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ) where exactly error is coming in forms.There is plsql code written and while executing the code, ORA-01722: invalid number error is coming. For easy tracing the error, any way is there ?

I searched in Google before posting in this forum and found that ON-ERROR trigger if we write DBMS_ERROR_TEXT it will display the ling number along with package or procedure name. But it is not working and this is only giving me error name no line number.

View 3 Replies View Related

PL/SQL :: Query To Find Number Of Days Across Each Month

Oct 1, 2012

We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.

We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.

We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below

Number of Working days in March is 31 - 3 + 1 = 29

Allowance A1 = (300 * 29 )/31

Number of Working days in April is 5 ( this also needs to find logical I am guess )
Allowance A2 = (300 * 5 )/30

Then A1 + A2.

The A(n) would be the total allowance where provided the number of month across.

View 10 Replies View Related

PL/SQL :: Find Database Server IP Address And Port Number

Aug 21, 2012

Is it possible to find the Ip address and Port number of a database server in Oracle 10g version?

actually i wanta to connect it through Putty.

View 7 Replies View Related

Sort Through Schema Tables To Find If Particular Vendor Number Exist

Nov 14, 2006

I would like to be able to sort through a schema's tables to find if particular vendor numbers exist. The decode cross tab works fine, except that it needs static data for each table. How can I code a PL/SQL to populate the table_name and give a count for each row? See example below:

col table_name format a15
set numwidth 10
set lines 1000
set pages 50

select b1.table_name,
count(case when a.vendor='86444' then 1 else null end) "86444",
count(case when a.vendor='86445' then 1 else null end) "86445",
count(case when a.vendor='86469' then 1 else null end) "86469",
count(case when a.vendor='86470' then 1 else null end) "86470",
[code]........

View 1 Replies View Related

Server Administration :: How To Find Out Number Of CPUs Allocated To Specific Database

Aug 1, 2011

when we ran SELECT statement against CUBE, we got below wait event: resmgr: cpu quantum.Further, we checked below 2 parameters :

NAME VALUE
------------------------- --------------------------------------------------
resource_limit TRUE
resource_manager_plan SCHEDULER[0x12B943C]:DEFAULT_MAINTENANCE_PLAN
[code]....

It has been found that these sessions did not get enough CPU to process the request. how to find out how many CPU has been allocated to this database ?

> uname -a
Linux dukedmts03db02.corp.cox.com 2.6.18-128.1.16.0.1.el5 #1 SMP Tue Jun 30 16:48:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

how to analyze how much % of CPU being utilized for a specific session ?

View 4 Replies View Related

PL/SQL :: Query To Find First And Last Call Made By Selected Number For Date Range

Apr 27, 2013

create table call(id number(10) primary key,mobile_no number(10), other_no number(10), call_type varchar2(10),call_date_time date, duration number(10));

insert into call values(1,9818764535,9899875643,'IN','24-APR-13 02:10:43',10);
insert into call values(1,9818764535,9898324234,'IN','24-APR-13 05:06:78',10);
insert into call values(1,9818764535,9215468734,'IN','24-APR-13 15:06:78',10);
insert into call values(1,9818764535,9899875643,'OUT','25-APR-13 01:06:78',10);
insert into call values(1,9899875643,9899875643,,'OUT','25-APR-13 22:06:78',10);

Query : need to find first and last call of '9818764535' mobile number and of call_date between '24-apr-13' and '25-apr-13';

Result :

date ,mobile_no , other_no, call_type, duration
24/apr/13 , 9818764535,9899875643,'IN',10
24/apr/13 ,9818764535,9215468734,'IN',10

[Code]....

View 5 Replies View Related

Server Administration :: Find Maximum Number Of Concurrent Logins Over A Period Of 1 Month

Nov 18, 2011

Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.

View 4 Replies View Related

Data Guard :: Find Out Number Of Standby Databases Configured For Primary Database From OS Level?

Nov 14, 2013

How can find the number of standby databases configured for primary database from os level

View 9 Replies View Related

How To Make Multiple Inserts In DB

Sep 4, 2009

i have multiple inserts to make in a table that is in an Oracle database...i already try several ways to do it but it always giving erros... how to make multiple inserts at same time.

View 2 Replies View Related

SQL & PL/SQL :: Automate Inserts From One Schema To Another

Dec 18, 2012

We have two databases one localdb with user rakdb and another one remotely remotedb with user rakdb .We need to be in sync with data in one table called om_item, where the users are inserting data on daily basis and the user sends us the insert script everday to run it on local databse to insert the new records in local database.I managed to create a file which records all the inserts into one text file in one directory.Can we have a scheduler to pick this text file from the specified folder and send mail using utl_mail.

CREATE TABLE ITEM (IT_CODE VARCHAR2(12),IT_NAME VARCHAR2(20));
INSERT INTO ITEM VALUES ('A','AAA');
CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:TEMP';
CREATE OR REPLACE PROCEDURE it_status

[Code]..

Procedure created.

EXEC it_status
HOST TYPE c: empaaaa.txt
INSERT INTO ITEM (IT_CODE, ITEM_NAME) VALUES ('A','AAA');
COMMIT;

View 4 Replies View Related

SQL & PL/SQL :: Performance Tuning In Large Inserts

Jan 19, 2011

I want to load 10 millions records from staging table to master table.One logic must be take during the load, the logic is rows already present in master table means, we need to update corresponding rows in master table otherwise rows insert in target table.

I have been using bulk collect and forall method to load data. it shows better performance compare then cursor row by row process. As per oracle doucmentation, we cannot use SELECT statements inside FORALL condition so we could not use logic inside the forall condition.

View 2 Replies View Related







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