SQL & PL/SQL :: How To Handle NULL While Inserting Records In A Table
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
ADVERTISEMENT
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
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
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
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
Jan 13, 2013
create table top22 (timed timestamp);
insert into top22(timed)
values (current timestamp),(null);
select timed from top22;
2013-01-14 10:50:22.17
NULL
select 'no is '||coalesce("num",'') from "bifrost"."top22"
getting error in DB2
if i remove this coalesce function
select 'no is '||timed from "bifrost"."top22"
no is 2013-01-14 10:50:22.17
NULL
but i need the second result is no is null
View 9 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 16, 2009
I am using sql loader to load data to tables from tab delimeted files.
Here the problem is that the sql loader is not handling null values. If there is any null value in the flat file it is moving the field values to left and loading to the table. I am using NVL function to handle the null values, but it is not working.
My control file is:
LOAD DATA
INFILE 'C: da_poc_filesSQL_scriptsSourcefilesTRADEGLOBNODE1.TXT'
BADFILE 'C: da_poc_filesSQL_scriptsBadfilesTRADEGLOBNODE1.bad'
DISCARDFILE 'C:C: da_poc_filesSQL_scriptsDiscardfilesTRADEGLOBNODE1.dsc'
[Code]....
Source file is attached to this link For the attached file in the first record, tradedate value is coming in to tradeprice field
View 16 Replies
View Related
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 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
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
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
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
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
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
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
Jul 23, 2009
I'm needing to return results per month counting records that meet a certain criteria. Some months there will be no results but I need to return a zero rather than having that month omitted from the result set.
This is the pre-existing query:
SELECT TO_CHAR(CRSCHED_DATE,'YYYY/MM'), Count(CPMA.RECORDNUMBER)
FROM CPMA.CPMA CPMA
WHERE (CPMA.CRSCHED_DATE Between TRUNC(ADD_MONTHS(SYSDATE,-12),'MM') And LAST_DAY(ADD_MONTHS(SYSDATE,-1))) AND (CPMA.CHGSTATUS='Duplicate')
GROUP BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
ORDER BY TO_CHAR(CRSCHED_DATE,'YYYY/MM')
The results returned are accurate, but any month(s) with no records meeting the specified criteria are skipped in the result set.
View 14 Replies
View Related
Jun 25, 2013
i want to count group above report records! want to count department group employees there in time is not null!
my report query
SELECT div.division,
DEP.DEPARTMENT,
DEP.STRENGTH,
E.EMPLOYEECODE,
E.NAME,
DES.DESIGNATION,
[code].....
im doing it with formula column!
function CF_PRESENTFormula return Number is
v_emps number;
begin
select count(1)
into v_emps
[code]......
View 18 Replies
View Related
Jul 1, 2013
previously i set null constraint to the column and creating some rows and need to change new entering values as not null constraint to the column in oracle without disturbing the old records. how can I do that.
View 5 Replies
View Related
Nov 22, 2012
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.
View 4 Replies
View Related
Jun 1, 2010
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].....
View 9 Replies
View Related
Mar 10, 2012
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
View 4 Replies
View Related
Aug 27, 2010
i am trying to fetch the cursor returned from a stored procedure and insert into a physical table. but i am unable to populate the table. below is how my code looks.
declare
p_out sysadm.CGF_PKG_REFCUR.rcDataCursor;
a table1.node%TYPE;
b table1.acct%TYPE;
c table1.descr%TYPE;
[code]......
In the above, stored_proc is the stored procedure from OLAP database. Above code i am executing is from EPM database.
p_out returns a result set of (select a,b,c from table2).
I am trying to get the result set from OLAP database and populate a table in the EPM database.
View 3 Replies
View Related
Oct 23, 2010
I have two tables with two columns of each table in my Oracle Version :Oracle 9.2.0.1.0
TEST22:
-----------
|sno |sname |
-----------
| | |
-----------
TEST22P:
---------------
| col1 | col2 |
|---------------|
| sno | 1 |
| sname | arun|
---------------
Required outcome is
TEST22:
----------
|no | name |
|----------|
|1 |arun |
----------
Also this should be applicable for more than one value in the column col2 of table TEST22P.
ex:-
TEST22P:
--------------------
|col1 | col2 |
|--------------------|
|sno | 1,2..n |
|sname | arun,ajay..n|
--------------------
I used decode & pivot insert for this,but the result is a failure.
SQL>INSERT INTO test22 (no,name) SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
SQL>
sno sname
--------
1 null
null arun
AND
SQL> INSERT ALL
2 INTO test22 VALUES(no)
3 INTO test22 VALUES(name)
4 SELECT DECODE(col1,'n',col2),DECODE(col1,'name',col2) FROM test22p;
INTO test22 VALUES(name)
*
ERROR at line 3:
ORA-00904: "NAME": invalid identifier
View 4 Replies
View Related
Oct 6, 2012
I need to insert rows in a table all the columns are have same value except one column i.e.
insert into table values( 'a','b','b','b');
insert into table values( 'b','b','b','b');
insert into table values( 'c','b','b','b');
is there any short cut to insert because there are thousands of records to be inserted.
View 3 Replies
View Related
