SQL & PL/SQL :: Inserting Values In Table - XMLTYPE
Oct 28, 2013
I am trying to insert the following values in a table called as inf_content
The table INF_CONTENT has following two cols
(REQUEST_ID NUMBER(10,0) ,
FLTER_SET_XML "SYS"."XMLTYPE" NOT NULL ENABLE);
===========================================================================================
INSERT INTO inf_content
[code]...
I am getting the error:
Error:
SQL Error: ORA-06553: PLS-307: too many declarations of 'XMLTYPE' match this call
View 6 Replies
ADVERTISEMENT
Oct 6, 2012
I need to insert rows in a table all the columns are have same value except one column i.e.
insert into table values( 'a','b','b','b');
insert into table values( 'b','b','b','b');
insert into table values( 'c','b','b','b');
is there any short cut to insert because there are thousands of records to be inserted.
View 3 Replies
View Related
Apr 20, 2012
LPAD not behaving as expected. the main thing I'm trying to accomplish here is reading values from one table, and inserting them into another... but in the "other" table, they need to be inserted as 11 characters long, with leading zeros. in it's most basic form, this is the cursor I'm using:
CODEDECLARE
CURSOR update_mpi_cur IS
select distinct A.epn_nbr, A.mrn_nbr, B.mpi_nbr
from table1 A, table2 B
where B.external_id = A.epn_nbr and B.identifier_type = 'EPN';
[code]....
should have mentioned that this is Oracle 10.2.0.3, on HPUX. not sure if that matters for this issue or not, but wanted to throw that out there.
View 2 Replies
View Related
Aug 3, 2012
I have a table AUD$ (containts some data) and aud_new(exactly structure same as AUD$ table with no data)
i want to schedule a job which will insert all data from AUD$ into table aud_new$ from aud$ table on next day again data from aud$ should be append to aud_new table and aud$ table should be truncated again.
The job should run on every day at midnight at 2 am
(note : If insertion in table aud_new get fail due to any reason , the aud$ table must not get truncated )
View 3 Replies
View Related
Jul 26, 2011
shall we stop inserting data into a table before it inserting using Trigger?
View 3 Replies
View Related
Aug 21, 2010
Since I need to do something along the lines of inserting an xml document into the database for later to use.
Anyway, the problem I get with the below is
Quote:
Error at line 95
ORA-00984: column not allowed here
Script Terminated on line 95.
It's on the INSERT line
declare
xmlInput clob;
begin
xmlInput :=('<?xml version="1.0" encoding="windows-1256"?>
<XLAAPRPT>
[Code].....
View 2 Replies
View Related
Jul 4, 2013
I'm trying to load xml file into table having xmltype datatype, but it is throwing below given error.I even tried to load data by changing '&' into '&' but still getting same error.
Error at line 6
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.
Version : ORACLE 11g, Windows 7
CREATE TABLE xml_test
(
id NUMBER(5),
NAME VARCHAR2(50),
xmldata xmltype
);
INSERT INTO xml_test(id, name, xmldata) VALUES(1,'file1', XMLTYPE(bfilename('SCOTTDIR', 'TEST_XML.XML'), nls_charset_id('AL32UTF8')));
TEST_XML.XML file content:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<!-- excelFolder:"c:CSD'TEST.XSL" -->
<File1>
<Version>1.6.1</Version>
<FileSource>Java&Conversion 1.2</FileSource>
<FileDateTime>2013-06-13T15:55:54</FileDateTime>
<Filecreatedby>Lo&ki</Filecreatedby>
<FileLocation>Bangalore</FileLocation>
</File1>
insert xmlfile having '&' data into xmltype column.
View 4 Replies
View Related
May 8, 2013
I am having a table with content_id and content_data , content_data is of XMLTYPE
Content_data field in table is like below
<Content id="123234354" version="3">
<Definition>
<Code>ABC</Code>
[Code].....
i want to find in how many rows <Title> tag is after <XYZ> tag in the content_data field of the table?
View 2 Replies
View Related
Nov 1, 2012
Version of DB: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
I have issue while loading xml_data into xmltype field in the table.Issue is whenever there is a special character like this 'revisions to §6' in xml text then it is 'revisions to §6' when its in the xmltype field of the table.
There is this new character appended before every special character.I have checked the database characterset.Characterset: NLS_ CHARACTERSETValue: AL32UTF8
create table xx_testxml(lx xmltype);/
DECLARE
x_item_doc sys.XMLTYPE := NULL;
BEGIN
SELECT XMLELEMENT("SyncItemPrimaryAttribute", 'revisions to §6' )
INTO x_item_doc
FROM dual;
INSERT
[code]....
View 23 Replies
View Related
Jan 9, 2013
select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.
Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.
how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.
My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE
=============================
Smith NinthGrade PHYS ED 101
Smith TenthGrade CALCULUS 201
Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and is queryable).
select * from V$VERSION
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
[code]....
View 3 Replies
View Related
Jun 10, 2011
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'
View 1 Replies
View Related
Aug 10, 2010
I am working on convertion of triggers from SQL SERVER to Oracle. I got them converted using a tool. But not sure how to correct the below trigger which uses '*' in sql server.
Code in SQL SERVER :-
CREATE TRIGGER INSERT_AUDIT ON T_AUDIT
FOR INSERT AS
INSERT INTO T_AUDIT_LOG
SELECT 'INSERT','AFTER', CURRENT_TIMESTAMP, NULL,*
FROM INSERTED
Converted into Oracle:-
CREATE OR REPLACE TRIGGER INSERT_AUDIT
BEFORE INSERT
ON T_AUDIT
FOR EACH ROW
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER', SYSTIMESTAMP, NULL, * );
END;
how can I remove the *. I tried replacing the * with the rest column names prefixing with :NEW.
the last 3 columns when are c_data1,c_data2,c_data3 as :-
INSERT INTO T_AUDIT_LOG
VALUES ( 'INSERT', 'AFTER',SYSTIMESTAMP NULL, :NEW.c_data1,:NEW.c_data2,:NEW.c_data3);
But this gives error:-
PLS-00049: bad bind variable 'NEW.C_DATA1'
PLS-00049: bad bind variable 'NEW.C_DATA2'
PLS-00049: bad bind variable 'NEW.C_DATA3'
how can I convert this code.
View 7 Replies
View Related
Sep 30, 2010
I am facing a problem while inserting primary keys using a sequence. Following is my case
I have a Master Table, say M_A, and 2 detail tables D_1 and D_2. I am trying to generate primary keys for the master and detail table as well as the reference keys for the detail tables using sequence.
I created a pre-insert trigger, say preInsertTRIG
proc_ABC(pri_key_master OUT VARCHAR2,
pri_key_detail1 OUT VARCHAR2,
fk_detail1 OUT VARCHAR2,
pri_key_detail2 OUT VARCHAR2,
[Code] ........
I am able to insert the P.K of the master table as well as P.K of one of the detail table. However, it fails to insert the P.K of 2nd detail table and reference keys for both the detail tables. I know there are other simple methods available in Forms, but I have to do it by this procedure only
View 5 Replies
View Related
Jul 19, 2010
I have a report, where there are opening balance and closing balance, so i have to store the closing balance values in a separate table during runtime and should show this closing value as opening balance for next month.
View 24 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
Mar 10, 2012
My homework requires me to create a booking table for a hotel and I have created the table but I'm having trouble inserting the dates.
This is my table:
DROP TABLE BookingDM CASCADE CONSTRAINTS PURGE;
CREATE TABLE BookingDM (
hNo NUMBER(3),
gNo NUMBER(5),
dFrom DATE NOT NULL,
[code]......
This is the first set I'm attempting to insert
hNo = 148
gNo = 11169
dFrom = 09/03/2009
dTo = 09/10/2009
rNo = 202
This is my attempt to insert the set:
SQL> INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202');
INSERT INTO BookingDM VALUES('148', '11169', '09/03/2009', '09/10/2009', '202')
*
ERROR at line 1:
ORA-01843: not a valid month
I need my dates to be in the format "MM/DD/YYYY".
View 4 Replies
View Related
Aug 27, 2010
i am trying to fetch the cursor returned from a stored procedure and insert into a physical table. but i am unable to populate the table. below is how my code looks.
declare
p_out sysadm.CGF_PKG_REFCUR.rcDataCursor;
a table1.node%TYPE;
b table1.acct%TYPE;
c table1.descr%TYPE;
[code]......
In the above, stored_proc is the stored procedure from OLAP database. Above code i am executing is from EPM database.
p_out returns a result set of (select a,b,c from table2).
I am trying to get the result set from OLAP database and populate a table in the EPM database.
View 3 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
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
Jun 14, 2013
I want to insert bulk records to the table. I want to insert date rows for next 50 years in table ( from year 2001 to year 2050). I have following columns in my table :
YYYYMMDD MM/DD/YYYY Day of the week ( Monday, Tuesday etc) JulianDate
View 5 Replies
View Related
Jul 12, 2011
I have an insert statement like below.
insert into emp (select empno,ename,sal);
Here I need to exclude the rows having sal<0 from the SELECT query and insert those into some other table simultaneously.
View 4 Replies
View Related
Jul 24, 2012
CREATE TABLE tbl_emp
(
name VARCHAR2(20),
price NUMBER,
[Code]...
NAME PRICE DATAENTRD ROWRANK
aaa 9999 24.07.2012 05:56:00 1
aaa 10000 24.07.2012 05:55:58 2
I want to insert this result into another table, how can I do it??
Quote:TABLE
CREATE TABLE tbl_emp_result_set
(
name VARCHAR2(20),
price NUMBER,
dataEntrd date,
rowrank number
)
View 8 Replies
View Related
Nov 16, 2012
How to insert more than 30000 records in a table using oracle procedure where I am having a table with number,varchar,data fields and columns like mpno,ename,sal,date of joining,data of leaving.
Data should populate using procedure.is there any way of doing it by procedure
View 6 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
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
View Related
Oct 9, 2013
I have a table of 10 records ,out of 10 records 2 records are having null values by using anonymous block i need to move the successful record(excluding null) into 'abc' tableand null records into 'err'table.
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
Dec 23, 2010
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
View 9 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 20, 2013
I have a data from the source table like this :
IdDate1 No1 Date2 No2
12/15/19959292 8/30/1997 11767
25/21/19932812 4/29/2002 2812
39/22/1978
410/11/19781157381 2/8/1979 929137
58/2/19891698430 11/30/1989 1365528
6
711/20/199541 1/23/1997 677
811/20/1995 1/23/1997 680
I want to insert into two separate tables using the following logic :
If date1 is not null or no1 is not null then insert into target_table1(id,date1,no1)
If date2 is not null or no2 is not null then insert into target_table2(id,date2,no2)
View 4 Replies
View Related
