I wanted to print 'null' when the column value is null. Actually, i am doing something like this
select empno||','||''''||ename||'''''||','||comm||','||sal from emp
It gives the following output for example
7369,'pointers',,200
If I use the above values to form a insert statement it throws
an error. As 'comm' value is not there.
I wish to get something like
7369,'pointers','',200
or
7369,'pointers',null,200
from the above select query
note I dint copy paste the query exactly from my sql*plus session as I am away from my oracle machine
how to insert data in oracle table without writing insert statement in oracle 9i or above. i am not going to write insert all, merge, sqlloder and import data.
The line highlighted in Bold is where I have the issue. I want it to pick up NULL or Blanks in the method type has well as types E,G,T. I've tried NULL is the statement, "", and '' put none of these work. What am I doing wrong? I'm using 11g by the way
select specialty, member_id, sp_id, service_date, sum(case when method_type = 'C' then (payment_total) end) "CONSULTATION", sum(case when method_type = 'M' then (payment_total) end) "MOT", sum(case when method_type = 'D' then (payment_total) end) "TESTS",
We all know Primary key doesnt treat NULL as a value. But the above statement is fine to be executed without problem. Is this something to be highlighted? or am i not right in understanding 'var1 varchar2(20) null '?
I have a table with no primary key constraints with some roles containing null value/duplicates. I then decided to alter the table to add composite primary key constraints on four columns (a, b, c, and d). I did this by using the same script that was used to create the original table but this time adding the not null constraints.
I then took and export of the original table. I now want to import the data to the newly created table but I am now getting the error: ORA-01400: cannot insert NULL into (string).
I will like to perform the import without NULL. Is there a parameter in impdp that I can use? I tried DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS but it didn't work.
Beside options using impdp is there a way to do an insert statement like this insert into table a (select * from table) excluding NULL;?
Basically, I need to load the data into the newly created table without NULL.
the above error while tryin to run my control file in sqlloader as i need to load the csv data into oracle...what sequence i need to write so that i do not face the above error.
I have one query i have create one table T1 in this table i have this three colm (EMPNO,ENAME,HIREDATE) and i have to insert raw from this two table T2 and T3 in T2 table colms are (EMPNO,SAL) and T3 table colm are (EMPNO,MGRID) so which query i have to run
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
i'm executing a load test using the below statement:
INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501; COMMIT; EXIT;
I've been using the same for a few days without any error while now wehn i try to use the same i get below error:
SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500; insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500 * ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected.
I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.
I have a table with unique index, and the application is doing inserts/updates into this table.Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.
All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.After this half minute everything went back to normal.What could be the reason and how can I investigate it ?
Given below a block of code, this code compiled successfully on one DB but returns error on other.DB version is same. I know sqlerrm can not be used directly but how it compiled successfully on one DB.
I am creating the following two tables...no issues here: CODECREATE TABLE COURSE_SECTION ( Csecid NUMBER(8) CONSTRAINT COURSE_SELECTION_NUMBER_pk PRIMARY Key, Cid NUMBER(6) NOT NULL CONSTRAINTS COURSE_SELECTION_Cid_fk REFERENCES COURSE, Termid NUMBER(5) NOT NULL CONSTRAINTS COURSE_SELECTION_Termid_fk REFERENCES TERM,
[code]...
The issue I am having is actually inserting data into the table: CODEINSERT INTO ENROLLMENT VALUES (100, 1000, 'A' ); INSERT INTO ENROLLMENT VALUES (100, 1003, 'A' );
[code]...
But I get an ORACLE error of
ORA-02291- integrity constraint (User1.ENROLLMENT_CSECID_FK) violated - parent key not foundHow can the parent key not be found when I have it declared/created in the above statement?
I would like to insert a value if that value is not existing in the table (example for a column which contains date only new dates should be inserted and if the date already exists in the column then it needs to get updated )
example of scenario...
date s1 s2 s3
in the above if the date is new..it should get inserted with the appropriate slot no.(s1,s2,s3) if the date already exists it needs to update the no.in slot no.
I want to grant a privilege through an insert statement into a sys table.Why do not grant the privilege through the classic way : grant select on t to l_user; ?
Because I want to do it remotely.I am connected to db1.I want to grant select on t2 to u2_b from u2_a.I assume that all DDL are DML. So a grant is equivalent "somewhere" to an insert.I tried to do my requirement locally, and here is the output.
SQL> conn scott/aa Connecté. SQL> -- step 1 : try to grant "normally" a select on dept to hr from scott SQL> grant select on dept to hr;
Autorisation de privilèges (GRANT) acceptée.
SQL> SQL> conn sys/a as sysdba Connecté. SQL> -- step 2 : Then, we connect to sys to see the row inserted in dba_tab_privs SQL> SQL> col GRANTEE format A10 SQL> col OWNER format A10
[code]...
Then if I can do it locally, I can do it remotely through a db link.
