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] ........
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')
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;
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 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.
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)
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.
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
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.
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.
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]........
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
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
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.
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.
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.
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]..........
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]........
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.
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.
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.
