I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.
I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?
I have input like below
ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA
and I want to get concatenated values separated
ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA
I would like get rows into comma separated values expected output
rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:
create table test1 (name1 varchar2(10));
insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;
SQL> select * from test1;
NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO
SELECT 'TEST','F1,F2,F3,F4' from dual
I want to split the rows by comma separated as below
TEST F1
TEST F2
TEST F3
TEST F4
I have one table select * from abcd;
No err-----------------------------1 rishi,rahul2 rishi,ak I want output like:
No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this:
select no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but this query is giving me output:
1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);
I have a string like this .
'ABC-XYZ-MNO'
and i want the data in the below format
'ABC','XYZ','MNO'
DB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
Enter user-name: sys@testdb as sysdba
Enter password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select deptno,wm_concat(ename) from scott.emp group by deptno
2 /
DEPTNO
----------
WM_CONCAT(ENAME)
--------------------------------------------------------------------------------
10
CLARK,MILLER,KING
20
SMITH,FORD,ADAMS,SCOTT,JONES
30
ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
SQL>
i want to display this data with semicolon seperated not comma seperated.
I have a scenario where I have to extract data in a string which is enclosed in [].
Here is what I want to do..
input:
[name_first] [name_mi] [name_last]
required output: this should return all the data that is enclosed in the brackets.
name_first
name_mi
name_last
I have tried the "REGEXP_SUBSTR" but my database version is 9i, and it is available only from 10g.
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies View Relatedhow 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.
View 2 Replies View Relatedwhen i tried to insert the details from oracle froms..the data inserts twice to the DB..
my table structure:
create table app_sri
(a_id integer primary key,
p_first_name varchar2(30),
p_last_name varchar2(20),
p_age number(3)
);
here a_id can be genarated through simple sequence(pid_seq)...
trigger on app_sri
create or replace trigger pid_trg
[Code]....
form insertion code..
Begin
insert into app_sri(null,'robo','Big',100);
commit
End;
the data inserted...but twice
what is the reason behind the double insertion?
I have to insert many computed rows (not coming from a select...where...) into a target table.
I wrote a code like the following example; it works but it seems to me not efficient:
WHILE l_data < l_dt_end
LOOP
l_value := MY_FUNCTION( l_data );
l_color := MY_COLOR( l_data );
INSERT INTO MY_TARGET
( value, color )
VALUES( l_value, l_color );
l_data := l_data + 1;
END LOOP;
COMMIT;
EXCEPTION
...
ROLLBACK;Which is the best way to write this?
In oracle 11i, unable to insert rows using insert statement. unable to retrieve data using select statement.
View 5 Replies View RelatedI must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error
First insert the user from application
INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC, CD_TIPO_ACAO, CD_ACAO_IMEDIATA, DS_ACAO, CD_RESPONSAVEL, DT_ORIGINAL, DT_ABERTURA, NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');I create triggers
CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
BEFORE INSERT
[Code]....
END ARCTR_ACAO_IMEDIATA_IUreturn me error
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
ORA-06512: at line 7using 9.2.08
I am unable to insert the rows into table after creation of trigger because Mutating error was getting.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NAME VARCHAR2(10)
EMP_NO VARCHAR2(10)
SQL> select * from test;
EMP_NAME EMP_NO
---------- ----------
ORACLE PC729
JAVA PC047
C PC0729
SQL> create or replace trigger trig_test
2 after insert on test
3 for each row
4 declare
5 ename varchar2(10);
6 eno varchar2(10);
7 begin
8 select emp_name,emp_no into ename,eno from test
9 where emp_no ='1';
10 insert into test2(emp_name,emp_no) values(
11 ename,eno);
12 end;
13 /
Trigger created.
SQL> insert into test values ('PRO','1');
insert into test values ('PRO','1')
*
ERROR at line 1:
ORA-04091: table APPS.TEST is mutating, trigger/function may not see it
ORA-06512: at "APPS.TRIG_TEST", line 5
ORA-04088: error during execution of trigger 'APPS.TRIG_TEST'
SQL> spool off;
I have a table named SOURCE with about 1.000.000 rows looking like this
date varchar2 varchar2 varchar2
DATE ID1 ID2 AMOUNT
---------- ----------- --------- --------
2010-07-03 1403 1403 1500
2010-07-13 2015438 etc 188608 6074
2010-08-28 1151927 410,4222 1750
2010-08-28 13622012 41026 178.99
2010-08-28 1600246 John 65
I want to insert the rows into a table TARGET
date integer integer number
DATE ID1 ID2 AMOUNT
There are about 10.000 rows where ID1, ID2 and/or AMOUNT contains characters. These rows I don't want to insert as the columns in the target table are INTEGER. I simply want to discard these.
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.
CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....
create table x (
field1 varchar2(10) );
insert into x values ('001-002');
insert into x value ('0150-0152');
insert into x value ('0100-0101');
create table y (
field varchar2(6));
recortd in table y
001
002
0150
0151
0152
0100
0101
I am trying to insert rows in two tables using sql loader.
I have two tables in database as
SQL> desc name
Name Null? Type
---------------------- -------- ------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE
SQL> desc name3
Name Null? Type
--------------------- ----------- -------------
ID NUMBER
NAME VARCHAR2(20)
BD DATE
I created controlfiles as
[oracle@DBTEST sqldri]$ cat datafile.ctl
options (direct=true)
load data
INFILE *
into table name truncate
when id='1'
[code]....
when i run sql loader as
[oracle@DBTEST sqldri]$ sqlldr hr/hr control=/u01/sqldri/datafile.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Load completed - logical record count 2.
no rows is inserted..the log file contain entries as
[oracle@DBTEST sqldri]$ cat datafile.log
SQL*Loader: Release 10.2.0.1.0 - Production on Tue Aug 7 23:30:07 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /u01/sqldri/datafile.ctl
Data File: /u01/sqldri/datafile.ctl
Bad File: /u01/sqldri/datafile.bad
[code]....
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3
(SELECT t1.c1,t2.c2
FROM TAB1 t1, TAB2 t2
WHERE t1.c1 = t2.c1
AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.
View 9 Replies View RelatedCan i set individual tab page bar color?
Like the attached screenshot..
I am trying to insert multiple rows using array bind feature.
The following is working fine & inserting rows as expected.
char values[3][2] = { "1", "2", "3" };
ub2 lenp[3];
lenp[0] = lenp[1] = lenp[2] = 2;
[Code]...
OCIStmtExecute() is failing with the following error
Error - ORA-01480: trailing null missing from STR bind value
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
seq date
------------------------------
101.01.10
205.01.10
306.01.10
410.01.10
512.01.10
613.01.10
714.01.10
815.01.10
916.01.10
1018.01.10
1119.01.10
[Code]...
The result should be (Don't use Pl/Sql)
seq date
------------------------------
101.01.10
205.01.10
310.01.10
413.01.10
516.01.10
619.01.10
723.01.10
826.01.10
929.01.10
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
My Table structure
column1 varchar(10)
column2 Date
Column3 varcahr(2)
Column4 varcahr(2)
By Data file
asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds
I still want to insert row 2 and row 4 into table by defaulting the date. how can I handle this in control file?
Script for test:
CREATE TABLE TEST(empno VARCHAR2(4), empname VARCHAR2(50), empstd NUMBER(2))
insert into test values(0001,'A',2);
insert into test values(0002,'B',5);
insert into test values(0003,'C',2);
insert into test values(0004,'D',7);
insert into test values(0005,'E',9);
Now I want to get empno for the particular employees based upon subscript and I have written below
DECLARE
CURSOR cur_rec
IS
SELECT *
FROM TEST;
TYPE cur_type IS TABLE OF cur_rec%ROWTYPE;
v_cur_rec cur_type;
BEGIN
OPEN cur_rec;
FETCH cur_rec
BULK COLLECT INTO v_cur_rec;
[code]....
how to access individial item froma table of rowtype data.
CM: Added [code] tags, please do so yourself next time, see the orafaq forum.
I have following data
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX
6 rows selected.
I want it in the following manner
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1
6 rows selected.
Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record
Present query is as following and it is not much efficient
SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT
[code]....
I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results
select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';
In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.