I have string like 'PRASAD,ALLEN,STEWART,SMITH'.
LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH
I want to store the data into columns using SELECT statement only
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
I have a table which has a column that stored concatenated data.
Sample test case is as below:
SQL> create table tst (
2 col1 varchar2(20));
SQL> insert into tst values ('one,two,three');
1 row created.
SQL> commit;
Commit complete.
Is there any way i could write a sql to split the text of this column into rows? Sample output im expecting is as below
col1
-------
one
two
three
i want to store all rows of columns into single variable and then use in inside of SP
declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....
it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g
We have been recommended to store data in CLOD data type.
Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.
We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?
My requirement is to get primary key column name and there values of a table and store in a stage table
create table easy(id integer, event_rowid integer,txn_rowid integer,txn_name varchar2(200));
begin
for i in 1..5 loop
insert into easy values(i,'777'||i,i||89,'Ris'||i||i);
[code]...
I was able to fetch primary keys and there values for a table and store them in stage table but only 1 record , I am stuck when there are more than 1 record Here is what i tried
SQL> SET serveroutput ON
SQL> DECLARE
2 V_prim_key VARCHAR2(2000);
3 l_tab DBMS_UTILITY.uncl_array;
[code]...
If you look at the above code in the execute immediate where condition i have used id=1, which is 1 records only but if i change id <3 then there will be 2 records and i will get below error ORA-01422: exact fetch returns more than requested number of rows
Other problem if there are 3 primary keys as in above case i am fetching there values 1 by 1 , is there any way if i can fetch those values once in for all data to be stored in stage table in below format
TAB_NAME V_PEIM_KEY V_PRIM_DATA DT
------- --------- ------- ------
EASY TXN_NAME,EVENT_ROWID,ID Ris11,7771,1 10-Feb-13
EASY TXN_NAME,EVENT_ROWID,ID Ris22,7772,2 10-Feb-13
I Have to write a procedure which takes XML data and inserts into some tables.
If the XML format is fixed then i can use extract function for parsing and can insert into the tables.
But the problem is there is not fixed format for the xml.
Are there any built in packages which takes the xml data for parsing..
I have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.
So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
Since XML-files only contain character data, we could/should store it in a CLOB, rather than a BLOB.
But, One of my friend having a table where a column is defined as bloband came to know that XML data are being stored. I searched for some article with keyword 'How to insert large XML data in BLOB' But did not work.How to store the large xml content in a Blob and How to extract it?
how to tune qurey for coulumn wise data saved.because we have to join same table n number of times.for reference go through the following scnarios.
Suppose one table T1 is there and it has two column KEY and VALUE.if we are writing qurey for retriving desire result in row manner we have to join samae table no of times.
KEY Value
agreesWith true
id 1
assessment False
basisForDateOfProgression 1
bestOverallResponse 2
bestOverallUnknownComments data is ok
Qurey:
select * from (
select t.agreesWith from t1 t
)a
[Code]...
In this manner we can join upto bestOverallUnknownComments .so which method we follow to reduce the execution time and performance should be good.
I am very new to oracle and SQL.I am trying to create a store proc that will copy 14 day of data into a table and then truncate the original table. When i compile following code....
CREATE OR REPLACE PROCEDURE STOPROC_TRUNCATE
( dateNum IN NUMBER )
IS
BEGIN
create table AUDIT_14Days as select * from AUDIT where TIMESTAMP >= (SYSDATE - dateNum);
truncate table AUDIT drop storage;
[code]....
I am getting the file using CLIENT_GET_FILE_NAME. I need to read the data from the .xsl file and convert it into blob. The file should not be stored in DB.
View 8 Replies View Relatedwhat could be effective data type to store large integer values like, 50,000; 10,000,000 etc.?
View 3 Replies View RelatedI have a PL/SQL procedure which gathers data from multiple places as well as calculates some data. I want to store all this in a materialized view.So, I created an object type (I've shortened the definitions):
CREATE OR REPLACE TYPE mf_record_type AS OBJECT
(identifier VARCHAR2(6),
name VARCHAR2(100));
Then created the table type of the object:
CREATE OR REPLACE TYPE mf_table_type IS TABLE OF mf_record_type;
Then in the stored procedure defined a variable of the table type:
v_mf_record mf_table_type := mf_table_type();
Then I loop and populate the record type:
v_mf_record.EXTEND(1);
v_mf_record(x) := mf_record_type(v_rec.identifier, v_mf_detail.name);
When all that is done I try and create the materialized view:
EXECUTE IMMEDIATE ('CREATE MATERIALIZED VIEW mf_snapshot_mv AS
SELECT * FROM TABLE (CAST (v_mf_record AS mf_table_type))');
ORA-00904: "V_MF_RECORD": invalid identifier
Am I doing something wrong here? Can't I create the materialized view based on something other than a physical table?
I'm using few cursors to update my data and store it back to the same table. But for some reason the cursor seems to be picking obsolete data.
cursor c1
is
select distinct roles
from table1
where flag = '1';
cursor c2
is
select distinct roles
from table1
where flag = '1';
begin
for i in c1
loop
here im updating the roles im picking to to a suffix and role.
update table1
set role = suffix_role
where 'some condition';
end loop;
commit; -- committing so changes are visible for my next cursor.
for j in c2
loop
here im deleting all the roles that are not part of my comparing table.
delete from table1
where role = 'something';
But in my debugging table i see that its deleting roles present as input for first cursor, whereas it should actually pick data with suffix_roles.
I have a Data entry form which is a multirecord block;
Question: for example that form has 10 to 25 fields or columns more than that all the data has been entered, but before committing or saving that form i need to cross check the the data with a select query, whether the data entered is correct or not but before committing, that data it should be posted into that table if i find that one data is entered wrongly then i will modify that and again cross check and save the transaction permanently into the database table?
I have a table Product as;
desc product
Name Null Type
--------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER
INGREDIENT VARCHAR2(20)
The data in Ingredient is separated by ','.
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A,B,C
2 A,D
3 E,F
I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A
1 B
1 B
2 A
2 D
3 E
3 F
write this sql ?
How block splitting will happen in oracle block.
Suppose, I am having Oracle BLOCK_SIZE of 16k and my Linux OS level BLOCK SIZE is 4k. then How 16k oracle block will store in OS level? and
What will be the internal block splitting process?
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..
I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com
I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.
I need to split the given string into muliple sub strings based on one special character
Ex : Speace is Special character
with data as (
select 'ab cd ef gh ' from dual )
select * from data
Required Output :
ab
cd
ef
gh
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....
Aim: Architecture change in existing application
Domain: Health Care
Background: There are 2 application ( Front end: one in oracle forms - deals with accounts module and another in some legacy application - deals with patient, clinical and diagnose module) using and sharing the same Oracle 9i database.
Patient related modules are moved into another database ( java as a front end, oracle 10g as backend ) which is normalized - eliminating duplicate tables and column, also its tables and columns names are not matching with existing (patient)system.
Now the requirement is making the existing application related only to Accounts module ( having complicated business logic written in packages ) to work as it is without changing the code, design drastically.
Questions:
1. Now how best this task can be completed without affecting existing Accounts system drastically ( with minimal changes )?
2. what are the possible best approach to achieve this ?
3. what are the best way for communicating the 2 DB in this scenario ( may be creating synonym, views etc ) ?
4. What are challenges that needs to be addressed ?
Table Name : Trans
chitta_enn number(10,0)
varavu_patti varchar2(100)
pattru_patti varchar2(100)
Thogai number(10,2)
where in the data's are as follows
chitta_enn varavu_patti pattru_patti Thogai
101 panam null 101.00
101 null sambalam 51.00
101 null kamishan 50.00
I need to create the view as follows
View Name : Pattiyal
vivaram varchar2(2000)
varavu number(10,2)
pattru number(10,2)
The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null
Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn
Is it possible to create a view as above
11.2.0.3...just trying to learn the syntax. I have not worked with IOTs and I am exploring a feature I have not really used to try to learn something new. I know about intervals.This exact split syntax below, works on a heap table without errors. When I run the following split against a regular heap table it works.
CREATE TABLE MYTABLE (
INSERT_DATE date,
myfield1 varchar2(50),
myfield2 varchar2(50),
myfield3 varchar2(50),
[code]....
alter table MYTABLE split partition "FUTURE" at ( to_date('09_MAY_2013_13','DD_MON_YYYY_HH24' ) ) into ( partition "B4_09_MAY_2013_13", partition "FUTURE" ) update global indexes
*
ERROR at line 1: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).
However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.
splitting a table partition without making its primary key index ar any other indexes unusable.
I think it is possible to do so 10g onwards.
DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP
I do have a query which gives me two rows of data.(This might be more than two in other cases)
The data I get is formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
European Patent EP 101 Mokilosu
What I want to get should be formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
Mokilosu
As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.
The code I do use to get this data:
SELECT
TABLECOUNTRY.COUNTRYDESCRIPTION "COUNTRY_DESCRIPTION"
,TABLECOUNTRY.COUNTRYCODE "COUNTRY_CODE"
,OWNER.NAME "APPLICANT"
,CASEEVENT.EVENTNUMBER "EVENT_NUMBER"
,TO_CHAR(CASEEVENT.EVENTDATE,'DD.MM.YYYY') "EVENT_DATE"
[code]....
So what to modify in the query to get these columns of the second row blank?
I have a table like below-
create table test (cust_id number(3), m_no number(1), other_date varchar2(20));
Where there is a "cust_id" that can have maximum 3 "m_no" (Don't bother for that condition, we have it in actual table).
In this table records are as follows.
insert into test values (1,1,'ABC');
insert into test values (1,2,'DEF');
insert into test values (2,1,'DEG');
insert into test values (3,2,'DEG');
[Code]..
My objective is to get date like below--
cust_idf_m_nof_oth_datas_m_nos_oth_datat_m_not_oth_data
1 1 ABC 2 DEF
2 1 DEG
3 2 DEG 1 BCD 3 MNO
4 9 KLM
5 2 XYZ 5 XAZ
I want to say, that cust_id's that doesn't have second and third m_no should not have any data in those columns.
Can this be performed through single query, or I need to make temporary tables or functions for this.