PL/SQL :: Assign Count Of Committed Rows To Variable

Sep 4, 2012

from within plsql i am doing

insert into t_target (...) select (fiels,fields,fiels) from sourcetable;
commit;

i want to capture number of rows committed and assign it to a variable or insert it into a table, i can not rely on count of source table or t_target because both those tables are changing through out of the day/ data are being inserted and removed from those tables. is this possible

View 2 Replies


ADVERTISEMENT

PL/SQL :: Dynamically Assign Values Of Any Column Of Row Type Variable

Nov 12, 2012

need to dynamically assign value of each column of a row type variable:The example is like that:

Table "Student_list" is as follows:
---------
COL1 VARCHAR2(50),
COL2 VARCHAR2(50),
COL3 VARCHAR2(50)
[code]....

Is there any work around where i can dynamically built my variable or table.column name and assign a value to it?

View 7 Replies View Related

SQL & PL/SQL :: Declare Date Variable And Assign It To Current System Date

Apr 19, 2011

Write a program to declare a date variable and assign it to the current system date. Depending on the day of the month the program should print the following:

If day is 1-10 then print "It is day<day number> of <month name>. It is early in the month".
If day is 11-20 then print "It is day<day number> of <month name>. It is the middle of the month".
If day is 21-31 then print "It is day<day number> of <month name>. It is nearly the end of the month".

For example, if the day is November 30, then print "It is day 30 of November. It is nearly the end of the month".

View 9 Replies View Related

SQL & PL/SQL :: Setting Select Count Value Into Output Variable

Apr 12, 2011

I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.

create or replace
PROCEDURE BOOK_PASSENGER(
EXCURSION_ID IN excursion_booking.excursion_id%TYPE,
PASSENGER_ID IN excursion_booking.passenger_id%TYPE,
NUMBER_BOOKED OUT NUMBER)
AS
BEGIN
INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL);
SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID;
END BOOK_PASSENGER;

View 6 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

SQL & PL/SQL :: Way To Know Last Committed DML Operations Over A Table?

Feb 11, 2011

Is there any way to know Last Commited DML Operations Over a table?

View 1 Replies View Related

Forms :: POST Command - Getting Committed With Error Especially In New Database?

Jul 29, 2010

I have a form which will call another and do some transactions and come back to original form and do other processes. save if successful. or roll back with error message.i m doing POST when i exit from the called form. and continue process in calling form. if any error, i m raising an error message with rollback.

But sometimes, it is getting committed with that error trn. especially in new database, first time try the scenario, it any error comes then getting commited.I m not able to find where the commit is happening? if any other sessions commit will affect this trn?

View 1 Replies View Related

SQL & PL/SQL :: Count Distinct Rows In Table

Feb 21, 2013

I think this is easier than I think but all I want to do is count the distinct rows in a table.

WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL

[Code]..

seems to get what I want

ID1VAL1CNT1
111114
212224
321114
422224

but can the count be done in a stand alone query such as??

SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1

NOT what I want

ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253

View 5 Replies View Related

SQL & PL/SQL :: How To Count Rows Of Partition Table

Apr 9, 2013

I have tried to execute this query

select partition_name, num_rows from dba_tab_partitions where table_name='Partisi_Book_Issue';

it display 0 num_rows. But i have made non partitioned table to partitioned table with the same number of rows. how to display number of record in each partition in any table?

View 3 Replies View Related

SQL & PL/SQL :: Count Number Of Rows With Certain Condition

Jun 21, 2010

To count the number of rows to the below table entries with certain conditions

my table

col1(varchar2(4000)) col2(varchar2(10))
3-1808116101 3-Standard
3-1808312141 3-Standard
3-1808312061 3-Standard
3-1807714441 2-Significant
3-1808284201 2-Significant
3-1808278361 2-Significant
3-1808284111 3-Standard
3-1807714261 3-Standard
3-1807128371 3-Standard
3-1807128351 3-Standard
3-1808301721 2-Significant
3-1808301701 3-Standard
3-1808322501 2-Significant

where conditions:

1)need to take the count of rows(col1).where col2 value is 3 or 2 .
2)value 3 or 2 is extracted and conditioned in the query

View 13 Replies View Related

SQL & PL/SQL :: Count Of Rows For Specific Period?

Aug 12, 2010

I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:

Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645

View 12 Replies View Related

SQL & PL/SQL :: Fetch All Rows Into Single Variable

Feb 21, 2012

i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.

SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;

[Code]....

how to resolve this. above query,variable holds last value which fetched from cursor.

View 9 Replies View Related

SQL & PL/SQL :: SELECT With Count Of Rows Depending On Sum Of A Field?

Aug 10, 2011

I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.

At first a code sample of the complete selection:

SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%')
AND saext='M'
ORDER BY qty DESC,ktxt;

This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.

So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.

1. Can I do this in a query?

2. If yes, what would this query look like?

View 19 Replies View Related

SQL & PL/SQL :: How To Display The Status And Record Count In Separate Rows

Mar 6, 2013

My problem is i need to display the status and the record count in separate rows.How can i do it

Example

SELECT 'A' STAT, COUNT(A) CNT,'B' STAT, COUNT(B) CNT,'C' STAT, COUNT(C) CNT
FROM TAB1;

OUTPUT SHUD BE LIKE

STAT COUNT
A A_cnt
B B_CNT
C C_CNT

View 13 Replies View Related

List Count Of Rows Where DATE Field Is Not Null?

Apr 1, 2010

I need to list a count of rows where a DATE field is not null and group those counts by day.

Here's my sql so far...

SELECT
COUNT(DQ_DISTRBTN_DATE) as DQR_DIST,
DQ_DISTRBTN_DATE as DIST_DATE
from
ETL_PROCESS.BATCH
group by
DQ_DISTRBTN_DATE;

Because DQ_DISTRBTN_DATE contains time, how do I modify to achieve the desired result?

View 2 Replies View Related

How To Store Total No Of Updated Rows (number) In A Variable

Jun 21, 2011

how to store total no of updated rows (number) in a variable after executing an updation query using script

View 2 Replies View Related

Application Express :: Interactive Report  Count Selected Rows?

Jul 6, 2012

I have a I/R report with a checkbox is column 1 of each row using:

HTMLDB_ITEM.CHECKBOX
(1,R.RESEARCH_ID,
decode(:Pxx_SELECT_ALL_ROWS,'Y','CHECKED',
decode(instr(nvl(:Pxx_SELECTED_IDS,0),R.RESEARCH_ID),0,NULL,'CHECKED'))
)
AS select_flag

I want to use Javscript or a Dynamic Action to "count" how many selected rows there are, when a UPDATE button is pressed. Right now, the UPDATE button "submits" the page, then a page item computation runs the following code to get a count:

DECLARE
v_cnt number(9);
BEGIN
v_cnt := 0;
FOR i in 1..htmldb_application.g_f01.count
LOOP
v_cnt := v_cnt + 1;
END LOOP;
return v_cnt;

This works... but I dont want to submit (and rerun the report) just to get this count.

I have copied this loop into a Dynamic Action using the Click action of the UPDATE button, but it doesnt seem to recognize htmldb_application.g_f01.count .. so my value returns as 0.

View 7 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

SQL & PL/SQL :: Rows With Same Date - Count Date Only Once

Jan 18, 2011

I'm trying to run a script that will sum column values from a table using only a certain period of time in example below only January submissions. However, some of those rows have the same date and I want to have the latest time row of that day in the count.

Oracle 10g

sample 'submit' table

pk loan submit_time
1 105000 1/17/2011 08:05:23 AM
2 204954 1/17/2011 07:59:53 AM
3 159000 1/14/2011 05:25:35 PM
4 215000 1/05/2011 01:06:12 PM
5 340415 12/18/2010 4:34:12 PM

View 2 Replies View Related

SQL & PL/SQL :: Assign The End Date

Dec 19, 2011

I had as source a set of actions where I have to assign the end date, the general rule is that each action has the same end date to start date of next action, if there is no next action it will be assigned the default('35001230').

I can do the assignment with the date of order:

NVL (LAG (DAT_INI) OVER (PARTITION BY ID_CLIENT DAT_INI ORDER BY DESC, DESCCOD_SEVERITY), TO_DATE ('35001230 ',' YYYYMMDD ')),

However, I have an exception to the rule, if the action have COD_SEVERITY = 3 or 4 and the next action have the COD_SEVERITY = 3 or 4, the end date will be for the previous day. If in the same day I have actions with COD_SEVERITY 3 and 4, the end date of actions with COD_SEVERITY = 3 will be DAT_INI -1 and the action with COD_SEVERITY = 4 will have the same date of the next action or DAT_INI -1 if the next action had COD_SEVERITY = 3 or 4

Input
ID_CLIENTDAT_INICOD_SEVERITYVALUE
1201112011100
1201112013100
1201112044101
1201112073100
[code].......

Output except
ID_CLIENTDAT_INICOD_SEVERITYVALUEDAT_END
120111201110020111201
120111201310020111203
120111204410120111206
120111207310020111213
[code]........

The source
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 1 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111201 as DAT_INI, 3 as COD_SEVERITY, 100 as VALUE FROM DUAL
UNION ALL
SELECT 1 as ID_CLIENT, 20111204 as DAT_INI, 4 as COD_SEVERITY, 101 as VALUE FROM DUAL
UNION ALL
[code].......

View 1 Replies View Related

PL/SQL :: How To Assign Value To Parameter

Mar 20, 2013

I have written this code, its throwing error.

begin
declare tmdate DATE;
select TO_DATE(TO_CHAR(MAX(DATERANGE),'DD-MON-YYYY')) into tmdate from table_check_date_range;
dbms_output.put_line(tmdate);
end;

View 10 Replies View Related

Forms :: Assign Employees To Jobs

Oct 12, 2011

Assign employees to their jobs in consideration the maximum number of employees to each jobs is 5 employee plus each job has own the maximum number of employees

we need the maximum number of employees for each job 5 to be variable when need to change this maximum for certain job , change this number from database (form the from of job ) not form code )

tables
emp
emp_no
name
manager
hiredate
salary

job
job_no
job

you can add tables or attributes to tables to complete you business.

View 10 Replies View Related

SQL & PL/SQL :: How To Assign NULL To Table Type

Jan 5, 2011

Let me know in PL/SQL how can we set a Table Type record to NULL?

Also, if .delete makes it NULL?

View 1 Replies View Related

SQL & PL/SQL :: Assign Sequence Name During Table Creation?

Aug 22, 2011

is it Possible to Assign the Sequence Name During the Table Creation.

View 1 Replies View Related

PL/SQL :: Assign Partitioned Index To Primary Key

Jan 25, 2013

create table mypart(a number, b number, c number, p_key number) PARTITION BY RANGE (p_key)
( PARTITION p0 VALUES LESS THAN (18),
PARTITION p1 VALUES LESS THAN (29),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
)  ENABLE ROW MOVEMENT;

create index idx_mypart on mypart(p_key,a,b)

I want to create primary key on this table that will use the local partitioned index idx_mypart

can I do that ?

alter table mypart add constraint pk_mypart primary key using index (idx_mypart)

above syntax gives error

basically the primary key should make use of the local partitioned index.

View 4 Replies View Related

Forms :: Assign Multiple Values To Variables?

Mar 31, 2012

assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause

declare
usergrade varchar(4) := 'pass';
user_unitcode number(6) := ;--needs three unit codes to equal pass

View 1 Replies View Related

Server Administration :: Assign User Roles To Uses?

Aug 19, 2011

Is there any default way to assign user roles to uses ?

Suppose I want assign to user different roles to different users on views, tables etc.

I wan to implement Access control list & Access request objects (ACL/ARO).

View 7 Replies View Related

Forms :: How To Assign Value To Parent From Child Form

Feb 29, 2012

I have two forms, FormPARN, FormCHLD.

One Text item is there in FormPARN, near to that Text item, Button is there.While clicking Button FormCHLD will be opened. Inside the FormCHLD, i will do some calculation and i need to store the result in FormPARN's Text Item.

How can i do it?

View 10 Replies View Related

Forms :: How To Assign More Than One Company ID To Temp Field On B1

May 19, 2013

I have two blocks in Form (Forms 10g) .On the Block B2 I have populated the Company Id by using the PLSQL Table.Now I selected more than two company ID from that Block ...I just want to keep the Company ID in the Temp Field on the Block B1.which I have selected on the Block B2. how can I assign more then one Company ID to That Temp Field on B1.

View 8 Replies View Related

Assign Different Profile For Oracle Database Users According To Their Job Functions

Apr 10, 2011

it is a good practise to assign different profile for oracle database users according to their job functions.what could be the resons for that?

View 3 Replies View Related







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