I have table with Blob column. In the blog column I have XML data (see below). I wanted to parse xml and get the value of note '<GetTest>'. How do I achieve that? Sample Table:
create table SAMPLE_XML_TABLE
(
ID NUMBER not null,
XMLSTRING BLOB,
);
<?xml version="1.0" standalone="no"?>
<FromResponse xmlns="http://www.somewebsite.com/FromResponse">
<Code>0000</Code>
[code].........
I'd like to parse an XML file and get some data extracted as columns. Input file country.xml:
<?xml version="1.0" encoding="UTF-8"?>
<MAS Action="Insert">
<Country ObjectId="100000000000000009" VersionId="8"><Id>1</Id><NlTexts><Name Language="de">Land1</Name><Name Language="en">Country1</Name></NlTexts></Country>
[Code]....
I have a table that has rows like below in one column. I want to parse the rows in different columns.
Column12
abc|yed| qas ert | hub cap | jak
agh|
bef|rfd
som|gfr|lup
I need to parse the above in different columns by splitting at the pipe.
so it will show something like this
col1 col2 col3 col4 col5
abc yed qas ert hub cap jak
agh
bef rfd
som gfr lup
is it possible to do it in PL/sql.
I have a column which holds the data in the below format.
Source Data :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192>SNO_54-90-16489-008G
>SNO_SDPMDB-008G-11>SNO_54-90-18008-008G>SNO_54-62-08791-008G>SNO_20-81-00327
>SNO_SDPMDB-008G-12>SNO_54-90-17830-008G>SNO_54-62-08598-008G>SNO_20-81-00327
[code]..
Problem Statement :
Split the above data into individual components and create columns / aliases dynamically.If the column is present then place the data under the created column.
Example 1) :
>SNO_SDSDQ-8192-BN>SNO_54-99-24120-8192
Result
Column Name : SKUCol_54-99
Data :SNO_SDSDQ-8192-BNSNO_54-99-24120-8192
Example 2)
>SNO_SDSDAA-002G-101-J>SNO_54-90-16002-002G>SNO_54-62-05781-002G>SNO_20-81-00135-5
Column Name : SKU54-90Col_54_62Col_20_81
Data :SNO_SDSDAA-002G-101-JSNO_54-90-16002-002GSNO_54-62-05781-002GSNO_20-81-00135-5
Column Name can be derieved from the components like SNO_54-62-05781-002G
i.e. SNO_54-62-05781-002G ==> SNO_-05781-002G
"54-62" will give the Col_Name
The First Column will always have data starting with
SNO_SD%
This column is constant and will be named as SKU.
I need this data to be placed in the same record / row but under different columns as per the data set.Basically, Can the data be split into multiple parts based on delimeters and the columns are created based on the unique data in the parts that form the data in the column.
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.19 In-memory Sort %: 100.00
Library Hit %: 83.82 Soft Parse %: 11.21
Execute to Parse %: 92.47 Latch Hit %: 99.77
Parse CPU to Parse Elapsd %: 82.07 % Non-Parse CPU: 71.64
I see my soft parse in instance efficiency is 11.21%. Is this something to worry about?
I have a field that contains many part numbers. There can be 2 or more part numbers in the field separated by a space. How can this be dynamically read so you can compare the contents in the where clause to what you are search for? Example Part Numbers in the field: 1X 2X 3X 4Y 5Y 7Y
I would like to read the field and make rows out of the data.
1X
2X
3X
4Y
5Y
7Y
I got the table where are logged every single events from web site. Sometimes (depends on events)is written xml code into the xml_params column of table H ow to make an automatically procedure (sql query, function, or something like that) which every value of xml code should be written separate into the column the table. The point is I dont know what parameters will be included in the xml code in future.
For instance xml looks like:
<parameters xmlns="http://datalan.sk/webreporting/params/v1_0">
<parameter name="loginReason">
<value>invalidUserNameOrPassword</value>
[code]....
how to parse paramlist type variable to get names of the fields and their values in the paramlist?
View 1 Replies View RelatedI'm working on trying to write a shell script to parse a parameter file, but at the same time I want to be able to overwrite the parameter file settings with other command line settings. For instance if my par file had export/import settings for the username, password, schema, etc;
I wanted to run the same export/import with those settings for a different schema. I want to be able to put the schema=<different_than_par_file> after the parfile=<parfile.par and have the parfile be read and applied for everything except the different schema.
Right now I'm storing the cmd line and parsing it again looking for other parameters besides the parfile.
I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file.
View 1 Replies View RelatedActuall we requried to send and email from Oracle 10g database having attachements big in size aprox 10 to 30 MB.Is there any Custom Procedure or standard procedure for this.
We are using UTL_SMTP and UTL_MAIL (Only support RAW attachement upto 32 k in size).
I would like to extract some data value from oracle to a text file...and i m not sure how to set the delimiter between the columns data value
SET echo off
SET space 0
SET pagesize 0
SPOOL a.txt
SELECT emp_id, name, add
FROM table1
/
SPOOL OFF
Where do i set the delimiter?
Can i do something like in SQL*Loader?
fields terminated by ',' enclosed by '"'
I would like the text file to be display as
"123","ABCD","123 abc road"
"234","XYZ","234 xyz road"
Below is the function code used to extract data from blob column. The function works fine when the blob data length < 2000 bytes. When it is more than, it is throwing an error as below.
Table name: mr_test
Columns: id number
seo blob
CREATE OR REPLACE FUNCTION fn_mr_blob(in_id IN number) return varchar2
IS
len NUMBER;
[Code]....
ORA-01489: result of string concatenation is too long
when I replcae the
"SELECT myvar||trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;"
with
SELECT trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;
myvar1 := myvar1||myvar;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
11:53:51 SQL>select * from v$version
11:53:55 2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
All, I have an infuriating problem and I'm hoping for some advice. Well actually it is a number of problems but I'll use one example as a microcosm.I'm going to use as the example:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 912K(100)| |
| 1 | HASH JOIN | | 3412K| 940M| 826M| 912K (1)| 03:02:28 |
| 2 | HASH JOIN | | 3412K| 787M| 699M| 748K (1)| 02:29:39 |
| 3 | HASH JOIN | | 3610K| 657M| 557M| 560K (1)| 01:52:07 |
[code]...
Now then, this query runs perfectly and as expected (direct reads/DB scattered reads) until it hit step 13 in the plan (FTS of T6). At step 13 it basically enters into a (rather fatal) process of reading almost nothing but undo exclusively.
Now then, I wonder why this might be. Of course the natural reaction is to say "they data is changing, its read consistency (optional rookie insult)". Now, I found this hard to believe that the volume of data could be so extensively changed, keeping in mind its a good sized table and it's doing a full access.
I checked the DBA_HIST_SEG_STAT(DB_BLOCK_CHANGES_DELTA) view for the period and in the run up, i.e. the explain plan steps 2-12 took approx 20 minutes. In this window, less than 1% of the blocks in the table T6 were changed (approx 3-4k out of a 500k block table).Nearly every access of this table is reading undo, yet since this query started, 99% of the table is unchanged. But the query is behaving as if everything it reads has altered since it began.
To be clear, I understand the read consistency etc and expect it, what I DONT expect is the virtually the ENTIRE table scan operation routing via undo when I'm accessing the full table, yet hardly any blocks are altered after I start running.
Note this query/application seems to fetch the rows in blocks of 1k, its almost as if its re-executing for each fetch, but I wouldn't have expected that.
In summary:
>Query starts
>hits final table
>almost exclusive undo reads
>under 1% of block changes in read table since query began
Is there any other way anyone knows of that might cause a session to read into undo beyond a session changing data after the query executed?
How to Pick / Extract the java class files from the database.? We have not maintained the latest codes in the oracle application server where java class code is residing.
All the Java Classes are available only in database. So we need to pick the latest java class code from production environment. In TOAD we tried but all class objects are listing at the left side but we are unable to take the code. So how can we take the latest codes(java classes) from the Production Database as a backup.
Is it possible for Access to extract data from an Oracle database and upload it directly?
Currently we have a business process where data is being extracted in scheduled queries (30+) to Excel spreadsheets, then manually edited to remove heading lines and imported to an Access database. I see an opportunity to automate a time consuming manual activity by having the Access db extract the data and directly upload it.
how to write a procedure in oracle to Save pdf file content to BLOB field in Oracle....
View 11 Replies View Relatedcode to extract email id (full email id) from a string/text using Oracle Regular Expressions.
Example -
"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com
In our application, we are allowing user to upload data using excel sheet in UI. We are using PHP script in UI and using SQL Loader to load data from excel sheet to temp_table.
The temp_table has a primary key.
Here my question is , Is there any way to put some batch id for every upload in that table in automatic way ? so that we can easily extract the data by using batch id . we are using Oracle 11g.
I have a table in schema with a BLOB field. I store employee's picture in this field. Fields in this table are emp_id (number) emp_name (varchar2) and emp_photo (BLOB). I want to ask if there is a way in pl / sql that i could empty this BLOB field to null or reset this field so that user can change the saved photo graph and save another one.
what i am looking for is something like
alter table employee set emp_photo = empty_blob() or alter table employee set emp_photo = null
Upgrading from 10.1.0.2 to 10.1.0.5. Enterprise Manager requires 'newest' version of Oracle JDBC drive.Downloaded what I believe to the correct file (classes12.jar). I'm unclear what to do with this, my readings have pointed me in the following direction:
1) copy to c:oracleproduct10.1.0db_1jre1.4.1in
2) extract
here is the problem...tried:
1) just clicking on it (nothing)
2) c:program filesjavajre1.6.0_03injavaw -jar classes12.jar
Error:
Failed to load Main-Class manfest atrribute from
c:oracleproduct10.1.0db_1jre1.4.1inclasses12.jar
Is my location correct, I've been hunting everywhere..making no progress.
I needed to create a page on my existing APEX application that would allow the user to upload a file, I followed an online tutorial where the user had created a dummy table and inserted CSV File inserted through APEX into the table. Following that simple example I am able to load the simple CSV file (from tutorial) into a dummy table (from tutorial) but when I attempt to insert actual/dummy data into my actual database (which has a lot more fields of different types), using the exact same process, I am unable to do so.
Ironically, I am unable to insert even dummy values despite the fact that I have been able to insert the same dummy values using SQL Developer. Icing on the cake is that APEX does not produce any error, this lack of debugging feature (especially line by line debugging) is such a pain. Just to add, I can load the values into an Array and can successfully print the delimited values off the array but am still unable to insert the same values into my table. Here is the table that I am attempting to insert into (actual names replaced by Dummyxx):
CREATE TABLE "TABLE"."STG" (
"DUMMY01" NUMBER,
"DUMMY02" NUMBER,
"DUMMY03" NUMBER,
"DUMMY04" DATE,
"DUMMY05" CHAR(3 BYTE),
[code].........
And here's the insertion process:
DECLARE
v_blob_data BLOB;
v_blob_len NUMBER;
v_position NUMBER;
v_raw_chunk RAW(10000);
v_char CHAR(1);
[code]..........
If I run a process to produce the output of the data array onto the screen, the values displayed are in the pattern desired.
Inserting Dummy values instead of values from the Array (v_data_array) to test
EXECUTE IMMEDIATE 'insert into TABLE.STG (DUMMY01, DUMMY02 ..... DUMMY65)
VALUES (
60040,
5,
TO_DATE("14-Dec-12","DD-MON-RRRR HH:MI:SS"),
"ABC",
"ABC",
60,
"11DEC1201",
"331",
"SCOTT TIGER",
"MARKETING",
[code].......
Note that all the the CSV does not contain all the fields, the CSV files that are expected to be entered into the system contain 65 Fields whereas the Table has 73 Fields. Also note that the process runs fine through SQL loader that is invoked through a different server which I need to release and hence the attempt to load the table this way. Also, the procedure on the SQL Loader server is quite complex and involved JAVA+Unix Shell Scripts etc. which I would prefer to avoid.
My user would like to have a way to attach files to database records. For simplicity, we'll say that each employee in the database could have 0-many file attachments associated with them. I know how to make this part work using Oracle Forms.
So that's the easy part for me. The tough part is that they want to be able to run an Oracle Report on our application server (displays as a PDF, also downloadable as a PDF) and have links on this report to the attachments that they uploaded using the forms process above.
For the idea of storing the files on a folder somewhere, I simply created a text object on the report that had it's hyperlink property pointing to the file location. Done.. opens fine.
However, I want to be able to have the option of storing these files in the database instead, just in case we can't go with the shared folder idea. I'm not sure how to make this work. I can store a blob in the database.. but how do I link to that blob on the report for them to be able to download it? Is this even possible?
how to create a download link on the Oracle Report that let's them download a file out of the database.
I have a requirement where the data between [] or ][ has to be extracted from a string.
Here is my situation :
INPUT:
[abc] [def]-[ghi][jlk]
OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl
Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?
View 5 Replies View RelatedHow to extract DDL of DBMS_JOB in sqlplus ?
View 30 Replies View RelatedI have to extract a csv file running a sql file.
SQL>@d: estEndItem_Vio_Item_Material_Violations.sql;
This works on the sql prompt. I have to do the same using schedular now for which I want to do the same embedded in a procedure.
create or replace procedure test_csv
as
begin
@d: estEndItem_Vio_Item_Material_Violations.sql;
end;
How can I run the sql file in a procedure.
I'm stuck on 1 scenario
I have the following table:
Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion
INSERT INTO age_rateSELECT 45, 50, 60, 90
FROM dual UNION ALLSELECT 45, 50, 60, 88
FROM dual UNION ALLSELECT 40, 50, 60, 90 FROM dual UNION ALLSELECT 5, 50, 60, 88
FROM dual ;
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4 age_5_20 age_21_34 age_35_44 45 50 60 9045 50 60 8840 50 60 905 50 60 88 Required outputRate Min_age Max_age
----The below rate is for age band 0_445 0 445 0 440 0 45 0 4
--The below rate is for age band 5_2050 5 2050 5 2050 5 2050 5 20
--The below rate is for age band 21_3460 21 3460 21 3460 21 3460 21 34
--The below rate is for age band 35_4490 35 4488 35 4490 35 4488 35 44
Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not
I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.
The test script is below:
create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....
The expected output would be:
Fields - ID, old_level, old_level_start_date, new_level, new_level_start date
e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'
Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;
but this is not right
i want to extract numbers without DOT also.