SQL & PL/SQL :: Inserting New Record In Between List Of Records
May 2, 2012
I'd like to insert a record between the records which are already in the table. There are over 40000 records, and I would like to place this new record at 19th place. How may I do so?
Let's consider an example with 5 records:
Table data:
CREATE TABLE enum
(identifier VARCHAR2(64),
code VARCHAR2(512),
data VARCHAR2(4000))
/
[Code]...
So when we check the table, we get:
SELECT * FROM enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
What I would like to have when I run the select query:
SELECT * from enum
IDENTIFIER CODE DATA
---------- --------- ---------
HR_B_A Halli, L6 Halli, L6
HR_B_A Halli, L7 Halli, L7
HR_B_A Halli, L8 Halli, L8
HR_B_A Halli, L9 Halli, L9
HR_B_A Halli, L10 Halli, L10
HR_B_A Halli, P6 Halli, P6
HR_B_A Halli, P7 Halli, P7
The only way I can think of is transfer the data till L9 line in a table (e.g enum_temp), insert L10 line and then transfer the remaining data in that table. But I can't seem to figure out the syntax of the query.
View 6 Replies
ADVERTISEMENT
Sep 6, 2010
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
View 18 Replies
View Related
Jun 21, 2012
I have written a trigger which insert and update on same table and the select statement for update and insert is same. My trigger update the record but doesn't insert data and doesn't throw error as well.
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex.
create or replace
TRIGGER SRVCCLLS_RVW
AFTER INSERT OR UPDATE OR DELETE ON SD_SERVICECALLS
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
[Code]....
if i substitue all the where condidion value of insert statement then it return row and insert data but doesn't insert when trigger fire so there is no issue with the syntex
View 2 Replies
View Related
Mar 7, 2013
I am trying to insert record from sql loader but this is inserting only single row.
View 3 Replies
View Related
Jul 5, 2011
How to raise a exception when inserting a record in a table using plsql?
View 11 Replies
View Related
Jun 29, 2012
I have a view SV (say) which holds approximately 33,000 records. But, when I try to insert these many records into a table SV_T (say) it is taking huge amount of time i.e. 2-3 hrs (approx.)
how reduce the time taken for the insert.
View 3 Replies
View Related
Jun 14, 2013
I want to insert bulk records to the table. I want to insert date rows for next 50 years in table ( from year 2001 to year 2050). I have following columns in my table :
YYYYMMDD MM/DD/YYYY Day of the week ( Monday, Tuesday etc) JulianDate
View 5 Replies
View Related
Nov 16, 2012
How to insert more than 30000 records in a table using oracle procedure where I am having a table with number,varchar,data fields and columns like mpno,ename,sal,date of joining,data of leaving.
Data should populate using procedure.is there any way of doing it by procedure
View 6 Replies
View Related
Oct 9, 2013
I have a table of 10 records ,out of 10 records 2 records are having null values by using anonymous block i need to move the successful record(excluding null) into 'abc' tableand null records into 'err'table.
View 3 Replies
View Related
Mar 16, 2011
displaying an alter message after inserting a record into the database...i have designed form through forms builder.i want 2 display a alert message for showing a message 'RECORD INSERTED SUCCESFULLY ' along with the value entered in the textitem.this is the code i used
declare
d varchar2(30);
begin
go_block('block3');
insert into patient(ptno,ptname,dob,age,ptel,blood,address)values(:block3.item4,:block3.item
5,:block3.item6,:block3.item7,:block3.item8,:block3.item9,:block3.item10);
commit_form;
set_alert_property('alert2',alert_message_text,'RECORD INSERTED');
d:=show_alert('alert2');
end;
BUT I NEED TO SHOW THE VALUE FROM THE TEXT ITEM INTO MY ALERT MESSAGE..
View 2 Replies
View Related
Feb 20, 2013
I have a data from the source table like this :
IdDate1 No1 Date2 No2
12/15/19959292 8/30/1997 11767
25/21/19932812 4/29/2002 2812
39/22/1978
410/11/19781157381 2/8/1979 929137
58/2/19891698430 11/30/1989 1365528
6
711/20/199541 1/23/1997 677
811/20/1995 1/23/1997 680
I want to insert into two separate tables using the following logic :
If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1)
If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)
View 4 Replies
View Related
Dec 17, 2011
I have one indexed column in a table.for performance improval purpose i created index to that column .
my doubt is..it may decrease the performance while inserting data into that column?
View 5 Replies
View Related
Oct 11, 2013
I'm updating a large piece of legacy code that does the following type of insert:
INSERT INTO foo_temp
(id, varchar2_column)
SELECT id, varchar2_column
FROM foo;
We're changing varchar2_column to clob_column to accommodate text entries > 4000 characters. So I want to change the insert statement to something like:
INSERT INTO foo_temp
(id, clob_column)
SELECT id, clob_column
FROM foo;
This doesn't work, since clob_column stores the location of each text entry, rather than the actual content. But is there some way that I can achieve the insert with one call to a select statement, or do I need to select each individual record in foo, open the clob_column value, read it into a local variable and then write the content to the matching record in foo_temp?
View 2 Replies
View Related
Mar 8, 2013
I got Scenario that, i need to insert the number of records loaded in target table into the log table.
But the resultset is handled in the in the cursor. how to get the number of records the cursor handles.
/* Formatted on 08/03/2013 15:00:44 (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE DASHBOARD75.SP_STG_MLY_GL_HKP_V1_00
AS
CURSOR GL_HKP
IS
SELECT CAL.MTH_NM,
CAL.YEAR,
GLM.BU_CD,
[code].......
View 7 Replies
View Related
Jun 17, 2012
I have Multi record Block and for that block i have created one button, if we press that buttion it will open new block and it will post the records, Unfourtunately that block table dont have Primary key or any constraints .. so when we press that buttoon multiple times .. its posting multiple times..
Now i need to restrict to that which is should not post the records multiple timies i have tried by controling the paraemter..I have created one Non data base item initially value i assigned to 'N"
if the value is "N" then am doing process and showing the records and after processing am assigning the value to 'Y', if there are multiple records , at block level in pre-record trigger am assigning as 'N'.
View 2 Replies
View Related
Dec 14, 2012
I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.
SQL> create or replace
2 PACKAGE
casadm.sis_load_cpl_sis_reb_pgm_hist
3 IS
4 /**********************************************************************
******************
[code]....
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
View 16 Replies
View Related
May 8, 2012
which is the fast way of inserting 60 millions of records from a view to a table.
method 1:
create table t_temp_table as select * from v_dump_data;
method 2:
through bulk collect
---Bulk_Collect With FORALL----------
DECLARE
TYPE srvc_tab IS TABLE OF t_temp_table%ROWTYPE;
l_srvc_tab srvc_tab := srvc_tab();
l_start_time NUMBER;
l_end_time NUMBER;
l_error_count NUMBER;
[code].....
View 4 Replies
View Related
Apr 9, 2010
I'm calling sql loader recursively to load data from CSV files which has thousands of records in each file. If there are any duplicate records, the sql loader terminates with ORA00001. My query is how to ignore inserting duplicate records and continue with the load.
Most of the posts in forums suggests to use skip command. But i do not think that is a wise option in my case as we cannot predict the maximum error count. more over I have set up ERROR=0 in my code so that the code terminates in case thers is a data error.
any other way to ignore inserting duplicate records into the tables.
View 7 Replies
View Related
Apr 1, 2012
i have a master-detail form.
detail block is tabular.
when-button-pressed trigger iam inserting records in another table.i write
insert into ONHAND_QTY_LOCATION(sno,matid,matcode,description,partno,onhand_qty,location)
values (:ship_dtl.slno,
:ship_dtl.mat_id,
:ship_dtl.mat_code,
:ship_dtl.description,
:ship_dtl.part_no,
:ship_dtl.rec_qty,
:ship_mstr.place_from)
it is inserting only one record in onhand_qty_location table that too the last record.
i want all the records which iam entering in detail block should get entered in onhand_qty_location table.
View 8 Replies
View Related
Dec 19, 2011
I would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies
View Related
Jul 19, 2010
I got a form with few columns and a check_box. If the user selects n number of check boxes and click the button Save. The corresponding records should be inserted to XX_AP_CUSTOM table.
I have written the following below code in when-button-pressed trigger. With this i am able to insert only one record i.e where the current record indicator is there, even though multiple check-boxes(records) are selected.
declare
begin
IF :XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX = 'Yes' THEN
--IF CHECKBOX_CHECKED(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) = TRUE Then
--app_insert.insert_record('WHEN-BUTTON-PRESSED');
insert into xxfbi.XXFBI_INV_QUOTE_ANAL(Item,
[code]......
I know that i have to use last_record and first_record and for loop to insert multiple selected records, but dont know how to do it.
View 5 Replies
View Related
Aug 6, 2010
i want to popultate record in list item.... and write the follwing code...
PROCEDURE POPULATE_REC_IN_COMBO_BOX IS
RG_NAME VARCHAR2(500) :='RGGROUP';
RG_ID RECORDGROUP;
ERRCODE NUMBER;
BEGIN
[code].....
when i call this code then it returns the 2 errors:
1. FRM-41072: Cannot create Group RGGROUP.
2. FRM-41076: Error populating Group.
View 16 Replies
View Related
Jul 1, 2013
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob
7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
View 3 Replies
View Related
Apr 17, 2012
One of the tables is having
SQL> select count(1) from item_list;
COUNT(1)
----------
14356529
To fetch the count using the above query , it takes 6 mins. There are no other processes running in the DB. The fragmentation % on this table is showing 16.3%
I tried finding the fragmentation using the below query.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
((round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)) / (round((blocks*8),2)) ) *100 "percent fragmented"
from dba_tables
where table_name like ITEM_LIST%'
and owner = 'TEST_USR'
and blocks != 0
order by 4 desc
TABLE_NAME table size kb actual data in table kb
------------------------------ ------------- -----------------------
wasted space kb percent fragmented
--------------- ------------------
ITEM_LIST 5163360 4318302.27
845057.73 16.3664306
View 15 Replies
View Related
Jan 10, 2011
I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
View 12 Replies
View Related
Apr 23, 2010
i want to populate value into list item from below record group
DECLARE
channel recordgroup;
banks recordgroup;
errcode NUMBER;
errcode1 NUMBER;
m_stmt VARCHAR2 (100);
BEGIN
m_stmt :='SELECT DISTINCT RI_TYPE FROM COMPETETITION_DATA_UPLOAD';
[code]....
but i'm getting following error in forms FRM-41334 Invalid Record Group for list population
View 1 Replies
View Related
Jul 5, 2010
I created 3 radio buttons and one list item. Different options will appear according to the radio button the user has clicked on. i've created 3 recordgroups and 3 LOV. the first two for seven_eleven and mannings work fine, but not for GNC. is it because there is only one option for list item in GNC? Must it be more than one? I've tried to another option as space but it still doesnt work.
DECLARE
it_id1 item;
V_SEVEN_ELEVEN RECORDGROUP:=NULL;
V_MANNINGS RECORDGROUP:=NULL;
V_GNC RECORDGROUP:=NULL;
[code]....
View 13 Replies
View Related
Mar 15, 2012
I am using PL/SQL Developer.I have two tables: A and BTable A contains serial_from and serial_to values.This is used to define the serial numbers issued to customers (i.e. the start and end range of serial numbers issued).Shown here for a client:
Table B contains the individual numbers, B.serial (within the serial_from and serial_to range) along with other data, and is only created when the serial is used by the client.
I would like to create a list of records for individual clients containing serial numbers issued but not used. i.e. they are in between the serial_from and serial_to values in Table A, but not in Table B.
How can I create a list of numbers issued, but not yet used? Because Table A contains only two values (to and from) no record exists for them, so how do I generate a list and check against it?
View 3 Replies
View Related
Dec 31, 2010
I have a list box from which the user can select a client's name, I want them to be able to press a button next to the box which will then open another form and display all the client's details. How do I attach a button to a list box? and how can I call another form to open on a specific record?
View 3 Replies
View Related
Dec 28, 2010
Actually m working on oracle 10g forms. I have a list item in which no. of records displayed is 10. I am populating this list item through a record group. I want when user select a value from first record then he should not be able to select the same value in further record of that list item.
View 12 Replies
View Related
