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
ADVERTISEMENT
Jul 24, 2011
I am trying to create view that selects a number of rows from a table and has a calculated field which is a counter form 1 to n next to each row.
In the below example, s_name is repeated many times each with a differnet serial, I want to cteate a view that shows a counter for the occurences of s_name next to the serial.
CREATE TABLE XYZ
(
SERIAL NUMBER PRIMARY KEY,
S_NAME VARCHAR2(30)
);
INSERT ALL
INTO XYZ VALUES (1, 'ABC')
INTO XYZ VALUES (11, 'ABC')
[code].....
View 3 Replies
View Related
Aug 9, 2013
Is there any way I could find the number of inserts happened every day to a particular table.
View 10 Replies
View Related
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
Nov 30, 2010
is there any way other than this way to retrieve the number of record displayed in the block ?
last record ;
l := :system.cursor_record
View 1 Replies
View Related
Mar 31, 2012
①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID
---------
57082
②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK BLOCKS
------------- --------
683 8
③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER
----------------
684
④SQL> SHUTDOWN IMMEDIATE;
⑤SQL> STARTUP;
⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found
⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1
⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK# CLASS#
------- ----------
686 1
684 1
687 1
685 1
688 1
683 4
⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3
QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?
QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).
View 2 Replies
View Related
Jun 23, 2010
if i execute
begin
insert into t values('hgahaha');
.......
....
<17000 times insert statement>
end;
I get following error
ERROR at line 1:
ORA-06550: line 16385, column 13:
PLS-00123: program too large
But instead of pl/sql block if i write the insert statements as sql file and execute it produces no error
Does it mean pl/sql has limit on number of lines of code to 16385?
View 10 Replies
View Related
May 21, 2011
I am working on form which consist of two block, now i need to know total record in detail block, but in form structure i have multiple details entry aginst 1 master entry and after going for next master entry the details of privious master entry are going for posting that's why i am unable to use the currnt_record function. function to retrive the total number of records in details tab.
View 2 Replies
View Related
Sep 5, 2011
I have form with master detail relation ship (invoicing form) the detail block is tabular, displaying upto 7 records...
Now my clients wants to show the serial number along with each record while feeding the data (this includes when insert, editing, deleting / clearing records) he wants to see the serial number as in MS access / MS techniques.
I tried to use the system variable to use :system.cursor_record; but this dose not works.(in insert/edit/delete/clear record)
View 5 Replies
View Related
Jun 14, 2011
I need to read a huge number of rows, say in lakhs and then need to populate it in data block. Since it is having huge data am never able to run the form. it hangs after some time. when i test with few rows it is working. so no problem in coding.
View 4 Replies
View Related
Aug 20, 2011
how to count how many items present in a particular data block in oracle forms 10g.whether it is a text_item or display_item or list_item etc is there any method to do this.
i have written this when-button pressed but the problem is how to get the next item name in the block
DECLARE
V_COUNT NUMBER:=0;
V_FIRSTITEM VARCHAR2(100):='';
BEGIN
GO_BLOCK('BLOCK_ITEMCOUNT');
V_FIRSTITEM := GET_BLOCK_PROPERTY('BLOCK_ITEMCOUNT', FIRST_ITEM);
LOOP
[code]....
View 3 Replies
View Related
Jun 2, 2010
I have created simple data block by wizard and taken tabular layout 10 rows and 6 columns
i want to generate serial number on first column .....
what need to use..?
View 11 Replies
View Related
Jul 25, 2011
I need to enhance the design of one of the tables by adding a primary key to it. i need to know how to fill this new column with sequenced serial before I change it to Primary key.
CREATE TABLE AFESD.REQUEST_TO_FINANCE
(
REFERENCE_NUMBER CHAR(10 BYTE) NOT NULL,
ISSUE_DATE DATE NOT NULL,
RECEIPT_DATE DATE NOT NULL,
DESCRIPTION VARCHAR2(250 BYTE),
FK_COUNTRYCODE NUMBER(3) NOT NULL
);
ALTER TABLE N_REQUEST_TO_FINANCE ADD REQUEST_SERIAL NUMBER(6);
Now I need to add values to REQUEST_SERIAL but can figure how to do that in SQL. I even tried to use your previous tip and use row_number() but it can only return a full set of values not a single one.
View 6 Replies
View Related
Jul 6, 2010
I m using Forms 11g. when I m trying to create Data Block & Select table Name then I received the following error.
View 5 Replies
View Related
Mar 30, 2012
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
declare
l_sql_stmt VARCHAR2(1000);
l_year NUMBER(4);
l_event_id NUMBER(2);
[Code]....
Sql gives result like
YEAR EVENT_ID
1995 111
1996 111
1997 111
1998 111
1996 111
1995 111
1995 111
SO I am trying to do like
l_my_arr(1995):= 1 ;
l_my_arr(1996):= 1 ;
l_my_arr(1997):= 1 ;
l_my_arr(1998):= 1 ;
l_my_arr(1996):= 2 ;
l_my_arr(1995):= 2 ;
l_my_arr(1995):= 3 ;
But if I put l_my_arr(l_year) := 0, immediately after FETCH, then the counter is always 1, and I cannot put it before because l_year has to be FETCHED.
View 6 Replies
View Related
Oct 22, 2013
I made this script but I still don't quite understand if the syntax is correct. I just wanted to create a function and call it in an anonymous block. I then wanted it to use a variable as a parameter in an iteration and output the variable every iteration. It's done basically but I know it's not 100% right. The fibonacci function looks like its going to loop an infinite number of times if the parameter is greater than 2.
CREATE OR REPLACE PACKAGE myPACKAGE AS
CREATE OR REPLACE FUNCTION fibonacci
(n BINARY_DOUBLE) RETURN BINARY_DOUBLE IS
BEGIN
IF n <= 2 THEN
RETURN 1;
[code]........
View 5 Replies
View Related
Aug 24, 2011
Is it possible to create autonomous transaction in anonymous block.
find the below code, when I am trying to execute I am getting error
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
[code].....
View 5 Replies
View Related
Feb 1, 2012
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
(Select distinct c.contact_code
Following is the SQL query
Select
tbl_contact.contact_code,
contact_title
||'.'||contact_name contact_name,
contact_address,
[Code] ......
View 1 Replies
View Related
Oct 23, 2012
I'm using database 11g & restore backup from another location.
RMAN> RESTORE CONTROLFILE FROM '/u01/RMAN/forstandby_0acekubq_1_1';
RMAN> catalog start with '/u01/RMAN/';
RMAN> restore database;
channel ORA_DISK_1: reading from backup piece /u01/RMAN/forstandby_09ceku8r_1_1
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /u01/RMAN/forstandby_09ceku8r_1_1
ORA-19599: block number 782 is corrupt in backup piece /u01/RMAN/forstandby_09ceku8r_1_1
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/23/2012 19:24:11
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
View 1 Replies
View Related
Mar 24, 2013
I couldn't take backup of the RAC database archivelog.
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Starting backup at 24-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DG1/kvxcprd/datafile/system.260.777756857
input datafile fno=00003 name=+DG1/kvxcprd/datafile/sysaux.268.777756857
input datafile fno=00002 name=+DG1/kvxcprd/datafile/undotbs1.263.777756857
input datafile fno=00005 name=+DG1/kvxcprd/datafile/undotbs2.264.777756983
input datafile fno=00004 name=+DG1/kvxcprd/datafile/users.267.777756857
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=2 sequence=17 recid=26 stamp=810915691
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/24/2013 14:37:11
ORA-19599: block number 1985 is corrupt in archived log +FG/kvxcprd/archivelog/2013_03_24/thread_2_seq_17.269.810915689
RMAN> backup archivelog all;
Starting backup at 24-MAR-13
current log archived
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/24/2013 14:39:52
ORA-19563: header validation failed for file
View 4 Replies
View Related
Apr 17, 2002
I'm wondering how I can use a counter to number records in a table I'm inserting into. I need the counter to reset based on changing data in my table. For example, I have the following
seq_number name_type
1 Short Name
1 Short Name
1 Short Name
2 Short Name
2 Short Name
I'd like my results to be the following:
seq_number name_type
1 Short Name - 1
1 Short Name - 2
1 Short Name - 3
2 Short Name - 1
2 Short Name - 2
I'd like my counter to increment so that I can add a sequence number to the end of my name type, but when my seq_number field changes I'd like to reset and restart my counter.
View 3 Replies
View Related
Jan 25, 2010
I been looking high and low for a way to get the default record counter, at the bottom of the window, to provide how many records have been returned by any query.You know the record 1/? or record 1/10 at the bottom.
I don't want to do an:
execute_query;
last_record;
first_record;
Nor do I want to set the "query All Records" property of the data block to "Yes". Both result in the form opening up way too slow. I've a table with a total of 29500+ rows.
View 28 Replies
View Related
Apr 30, 2010
How to create Data Block Using Stored Procedure in Forms10G ?
View 1 Replies
View Related
Jan 24, 2013
I want to increment a counter in a loop based on a condition.
Here is how my xml looks like
<result>
<resultset id=1>
<value>
[Code]....
I need to look at each and every resultset and check if the value is >400 and if it is then display some text. Something like this
<outputvalue>
Yes, there is a value greater than 400
</outputvalue>
In my XSL, I declared a variable called count with value 0. I created a for-loop which goes through these results. Then inside the loop there is a condition to cehck if the value is > 400. If the value is >400, then the counter is incremented.
````````````````````
<outputvalue>
<xsl:variable name="Count" select="0"></xsl:variable>
<xsl:for-each select="/results/resultset">
[Code]....
There is a problem with the above code is that result is like this
<outputvalue>
*1*
Yes there is a resultset with greater than 400
</outputvalue>
Is there a way I can just remove the character 1 from the output? Or is there a better way to increment?
View 3 Replies
View Related
Jun 22, 2011
I am trying to track an employee's department changes by adding a Department change counter which is set to 1 whenever the department changes otherwise it is set to a 0. How can I accomplish this update of the counter? Below is the table creation sql and sample data for insert.
CREATE TABLE EMPL_DEPT
(EMPLID VARCHAR2(11) NOT NULL,
EFFDT DATE,
EFFSEQ SMALLINT NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
DEPT_ID VARCHAR2(12) NOT NULL,
[code]..........
View 5 Replies
View Related
Jun 14, 2011
I want to create a block of code that would search in all tables in a schema for a column_name where its data_length is like 4000 let's say. This data_length is actually dedicated for a comment column. If found, all not null column with like 4000 data_length will be changed by string "Comment has been removed". I have the following script below but it seemed lacking.
begin
for rec in (select table_name, column_name from user_tab_columns where column_name like ?data_length? = 4000 order by 1,2) loop
begin
execute immediate 'update '||rec.table_name||' set '||rec.column_name||' = ''Comment has been removed'' where '||rec.column_name||' is not null';
commit;
[code]........
View 6 Replies
View Related
Oct 1, 2011
I found nothing in SQL (all in PL/SQL).I have a table:
create table Parent (pk_id number primary key); --which is filled using sequence seq_Parent.
And I have a child table:
create table Child (rRef number, fk_parent number primary key (rRef, fk_parent);
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
View 7 Replies
View Related
Apr 10, 2013
i want to insert unique number with out gaps in a col of a table every time my query runs . it shouel generate numbers from 1 to n. I can do it using sequence , but sequence need reseting and other user can reset it also and it creats gaps too.
Any function/technique which can give me session or transaction specific unique number on each call to sp.
i want to inserting these numbers in a table and insertion is going on in a loop.
View 6 Replies
View Related
Jul 6, 2011
How to create procedure for getting number of records of a table
View 24 Replies
View Related
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