PL/SQL :: Extracting Data From XML And Inserting Into Table
Aug 8, 2013
I have an XML of the following format:
<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students>
Now i have to insert this xml into DB , the table consist of following columns ( row number , property name , value ) Expected out put is (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is
1. how to get the tag names and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?
View 8 Replies
ADVERTISEMENT
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies
View Related
May 26, 2013
I am trying to add a new column in a table and insert data from another column of same table.
alter table POSITION add INT_MK_DATA_ID number(10,0) null;
update POSITION set INT_MK_DATA_ID = INST_MARKET_DATA_ID;
commit
As there are huge number of records in the POSITION table ...its taking for ever to execute this query.
View 1 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
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
Sep 3, 2012
I am trying to insert a column into a variable from a trigger.
Here is the code that i have:
CREATE OR REPLACE TRIGGER BUYER_after_update AFTER UPDATE ON buyer
FOR EACH ROW
DECLARE
v_key varchar2(10);
BEGIN
select ID into v_key from buyer;
insert into message_log_table (table_name, message_comments)
values
('Buyer', 'Buyer '||v_key||' has been updated');
end;
/
When I run the above I get the following compiler error:
[Error] ORA-00904 (6: 12): PL/SQL: ORA-00904: "ID": invalid identifier
Since ID is defined in my BUYER table I do not understand what the error means.
Here is my create table statement:
CREATE TABLE BUYER
(
ID VARCHAR(50) NOT NULL PRIMARY KEY,
FNAME VARCHAR(50) NOT NULL,
LNAME VARCHAR(50) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(2) NOT NULL,
ZIP_CODE NUMBER(5) NOT NULL
);
View 1 Replies
View Related
Jul 22, 2012
I have a table created with the following code:
CREATE TABLE CLIENT
(
CLIENT_ID NUMERIC(2),
CLIENT_NAME VARCHAR2(25),
CONTACT_LAST_NAME VARCHAR2(15),
[Code]...
I have altered the table to have the following:
ALTER TABLE CLIENT
ADD CLIENT_CITY VARCHAR2(25);
I am trying to insert new data into said table that was updated:
INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1;
Then I get the following error:
Error starting at line 1 in command:
INSERT INTO CLIENT
(CLIENT_CITY)
VALUES
('Mount Pearl')
WHERE CLIENT_ID = 1
Error at Command Line:4 Column:15
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
View 3 Replies
View Related
Jul 22, 2013
I am exploring the differences between OBJECT & RECORD. As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL. Below i am trying to insert data into an table of type object but i am unsuccessful.
CREATE OR REPLACE type sam as OBJECT
(
v1 NUMBER,
v2 VARCHAR2(20 CHAR)
);
---Nested Table---
create or replace type t_sam as table of sam;
--Inserting data----
insert into table(t_sam) values(sam(10,'Dsouza')); Error Message:
[code]........
View 4 Replies
View Related
Jun 25, 2013
I am trying to insert huge data into another huge table which is almost taking around 2-3 hrs. See my below query
INSERT /*+ APPEND *//*+ NOLOGGING */ INTO DB1.Table1 SELECT * FROM DB2.Table2 ;
COMMIT;
Both Table1 and Table2 have same structure and table1 is master table having 100 Billion records and table2 having 30 Million records. This is a direct insert where each day this operation carried.
View 4 Replies
View Related
Feb 16, 2010
I have a form that has one text box in it, and I want this value to be inserted into a table called Staff Name, which has only one column, but im unsure as to how I would get my SQL statement to pick up this value.
I tried:
begin
insert into StaffName values ('addstaffmember');
end;
Add staff member is the name of the text box. this statement compiled but when I ran the form and tried to click save it would not work.
View 5 Replies
View Related
May 13, 2011
using FORALL for inserting data into table.
Below pl/sql works fine when we write all the cursor data together:
DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN
[Code]...
but I want to insert including some constant value while inserting the data like
DECLARE
TYPE t_rec IS TABLE OF T%ROWTYPE;
l_tab t_rec;
CURSOR cur IS SELECT a,b FROM t;
BEGIN
[Code]...
How to do this by using forall, or how to do without hiting performance ( more than 5000 rows i have to write.
View 5 Replies
View Related
Feb 16, 2004
how to read the excel data and insert into tables without using SQL loader. i tried using OLE2 package,but i am getting an non-oracle exception. even i tried using CSV format. but i couldn't make it.
View 3 Replies
View Related
Oct 18, 2010
Scenario:
Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.
What we are performing is:
Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;
Issues:
my_table_rpt get locked
We have found the workaround by disable locking in destination and subsequent to the insert enable locking
ALTER TABLE my_table_rpt DISABLE TABLE LOCK;
Insert the data to the reports database table
Then
ALTER TABLE my_table_rpt ENABLE TABLE LOCK
Question:
Why does the large destination table (my_table_rpt) get locked in the RAC environment?
View 2 Replies
View Related
Oct 7, 2011
I have come one requirement where i need to extract data from a LONG RAW data type column.
View 7 Replies
View Related
May 15, 2013
Currently, I am running 4 separate queries in order to retrieve specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g. :
Message Reference UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
456123 >(Cancelled)</UploadError>
456123 >4561</UserId>
456123 >1234</SecurityIdentifier
At the moment, I am extracting the required info as follows.
Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)
Once retrieved, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))
Example query 2: message_content, 2000, 581 > retrieves the starting point for a user id.
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)
View 13 Replies
View Related
Oct 4, 2013
This is my sample data:
WITH table_data
AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL
UNION ALL
SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL
UNION ALL
SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL
[code].........
I can extract the data from before the first *, but I need to also be able to extract the data from between the 1st and 2nd *s, e.g. Z23, A32345, TEST, THIS and THAT from the sample data above.
View 4 Replies
View Related
Mar 26, 2013
I need to extract tag values from REST web service response.
Webservice response
Quote:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <LicenseDetailsRes xmlns="com:*******services:LicenseDetailsRes">
<ResponseCode>LD001 - SUCCESS</ResponseCode>
<OrderNumber>51663852</OrderNumber>
<LineNumber>8</LineNumber>
<quantity>0</quantity>
</LicenseDetailsRes>
I am storing above response in a table column 'output_xm' of xmltype datatype.
My requirement is to extract 'ResponseCode' & 'quantity' values from above response. For which I am using below query but it's not working.
SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';
View 2 Replies
View Related
Jan 30, 2013
I exported and imported data from one oracle database to another, but not all the data got loaded in to the destination database. Basically it is filtering the data. Can it be because of the reason that the sql developer may be an express edition?
View 4 Replies
View Related
Oct 4, 2012
I need to extract the values from XML file.I tried with method described in below links -
[URLs]....
My XML file is -
<?xml version="1.0" ?>
<!DOCTYPE main [
<!ELEMENT main (DATA_RECORD*)>
<!ELEMENT DATA_RECORD (STATE_CODE?,JOB_NAME?,WC_CODE?,JOB_ID?,STATUS?,LOG_MESSAGE?)+>
<!ELEMENT STATE_CODE (#PCDATA)>
[code]...
The query is not returning any rows.
View 3 Replies
View Related
Jul 26, 2012
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
View 1 Replies
View Related
Jun 7, 2012
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.
View 3 Replies
View Related
Sep 30, 2008
I have requirement of extracting the relationships between all tables in the Database(Oracle 10.2.02) created by CA Unicenter Servicedesk.
Tell me about any tools/scripts which I can use to extract this information. I can do it manually, but it is time consuming.
View 4 Replies
View Related
Feb 15, 2009
I have two tables
table1
col1.....................................col2
primary key................ foreign key refer to col2 in tab2
table2
col1 ........................................... col2
foreign key refer to col1 in tab1 ............ primary key
now my question is how to insert data in to the two tables
View 2 Replies
View Related
Jul 17, 2009
I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.
The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.
What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.
View 6 Replies
View Related
Feb 21, 2013
I am inserting XMLTYPE data using DBLINK I am getting the following error.
INSERT INTO APSP.SALES_HISTORY@APSP_LINK
SELECT * FROM KMBS.SALES_HISTORY
ORA-22804: remote operations not permitted on object tables or user-defined type columns
Source table structure
Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE
Target table structure(another DB)
Name Null? Type
----------------------------------------- -------- ----------------------------
SC_NO NOT NULL NUMBER(25)
LT_DATE TIMESTAMP(6)
METHOD XMLTYPE
how to insert XMLTYPE data using DBLINK.
View 16 Replies
View Related
Mar 7, 2012
create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);
I want to insert data from stg1, stg2 into stg
Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2
I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;
it gives me table does not exist error:
so by stgi, i mean it should take stg1, stg2 etc
View 4 Replies
View Related
Mar 13, 2007
how can I insert data into tables on another user. They both are in the same table-space.
View 1 Replies
View Related
Nov 8, 2010
In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.
that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.
View 2 Replies
View Related
Feb 13, 2013
I am facing problem while inserting data into Core Table from staging (both Core Table and Staging are on same database but different schema)
I am using below command:
INSERT /*+ APPEND PARALLEL("CORE TABLE", DEFAULT, DEFAULT) */ INTO
SELECT DATA from Staging
CORE TABLE is quit big contains millions of record partition on date and having old stats of 2007. Data fetching from staging is very fast appx 1 million record in 2 mins. we are inserting one day data daily into CORE TABLE from staging and its taking 3 Hrs.
View 2 Replies
View Related
Apr 19, 2011
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
how to take care of this sequence no.
View 15 Replies
View Related
