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].......
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
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
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 RelatedI 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)
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?
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
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].....
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.
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 RelatedI have a table emp_up, daily this table is uploaded by a SQL *LOADER(with REPLACE option) script run by a UNIX JOB.There is no particular timestamp column in this table. Is it possible to know when/AT what time the table is uploaded.
View 2 Replies View RelatedI have a staging table and a target table. How do I pull in last loaded data from staging table to target table?
View 4 Replies View Relatedshall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies View RelatedHow to create procedure for getting number of records of a table
View 24 Replies View Relatedi have 2 tables in two different sources.
I have loaded data from source to destination.
some rows are missed while loading.
i want to know the missing rows
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
I am trying to insert record from sql loader but this is inserting only single row.
View 3 Replies View RelatedI have got a procedure that successfully creates an oracle external table and populates it with the contents of a file. This works fine until I have a situation where one of the fields is a VARCHAR2(2) and I try to insert say, a 5 character value. When this happens the record in question does not get populated in the external table (and rightly so), but I could do with working out if there is a discrepancy in the number of records in the file and the number of records that actually make it into the table so I could inform the user that there is a problem.
I have attached the code that creates the external table and populates it.
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.
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.
I need to load 2 trillion data from an external table to Oracle Heap table. I am using Direct Path insert for that. how to commit after inserting n number of rows.
View 8 Replies View RelatedI'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?
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
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'.
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.
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.
I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.
ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h
How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.
I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[code].....
I have two sql queries. They run the one after another.
Query 1:
select * from capital
where member_status = 'MEMBER' AND rownum <= 25
order by price desc
Query 2:
select * from capital
where member_status = 'MEMBER' AND rownum > 26
order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
I have the following requirement.
Data from table 1 is printed first and after every X number of rows another set of data either from the same table or a different table needs to be printed.