XML DB :: Possible To Remove XMLSchema Restriction From XMLType Column?

Oct 24, 2012

I have an XMLType column that is validated via some XMLSchema. Now I want to remove this restriction and make it just a generic XMLType column. I tried bunch of alter table commands but I couldn't figure out the magic combination. Is this possible?

Here is the actual problem. Unfortunately, we ended up both local and global schemas using the same URL. We have customers out there with both schemas (early customers) or only global schema (new customers). Most of the tables were created before the global schema was added. So, they are referencing the local schema. Now we want to evolve our schema, and I am trying to write a sql script to clean-up this mess. if only global schema exists then (these are late customers)

- do nothingelse if both global and local schema exist then (these are early customers)
- If any table or table.column has dependency on local schema (I can find this out from user_dependencies) I am going to mark those tables/columns as no schema validated. i.e. remove all references to local schema
- delete and purge the local schema
- modify those tables/columns and make them point to the global schema end if
- evolve the schema (only global one is left)

Is there a way to modify a column definition and move its reference from local schema to global schema where both schemas have the same URL? Of course the main constraint is that we do not want to loose customer data. By the way, both local and global schemas are identical in terms of xsd.

is it possible to change XMLSchema/Element settings of an XMLType column from X to Y, X to null, or null to X?

View 2 Replies


ADVERTISEMENT

PL/SQL :: Remove Duplicates From A Column

Nov 8, 2012

I want to remove duplicates from a column MAIN_TABLE based on TLEVEL Column:

create table UNIQ_TEMP
( TLEVEL NUMBER(10,0),
TABLE_NAME VARCHAR2(30),
MAIN_TABLE VARCHAR2(30)
);
[code]....

My Requirement is:

MAIN_TABLE= MARKETING_OPTIN
EXISTS IN MAX(TLEVEL)
REMOVE DUPLICATE MARKETING_OPTIN FROM OTHER LEVELS

This should apply to all the values in column MAIN_TABLE

View 12 Replies View Related

SQL & PL/SQL :: Restriction On Commit

Feb 3, 2011

Tell me restriction on commit means where this keyword is not used....like i somewhere read in trigger we can't used commit...instead of that we use pragma autonomous_transaction..

but my confusion arise when i see commit used in trigger in our database table....

is commit used in trigger , if not then what will be use...

Another one is commit used while creating procedure or function?

View 5 Replies View Related

Associating XMLTYPE Object With A XSD?

Oct 11, 2012

How to create an AQ queue in with payload of type "sample_orc.xsd".

Right now I created an object of XMLTYPE. Below is the code

CREATE OR REPLACE TYPE sample_payload_type AS OBJECT (
test_data SYS.XMLTYPE
);

Now How will I associate an XSD say "sample_orc.xsd" to this object?

View 2 Replies View Related

Restriction On Scalar Subqueries

Feb 21, 2013

I would like to pass my 1Z0-047 certification, but I don't understand the limitation on the scalar subqueries, especially for the having clause.

Here is my scalar subquery because it returns only one value.

CODEselect avg(list_price)
from product_information

I use it in a having clause as a scalar subquery and it works

CODEselect status ,avg(list_price)
from product_information
group by status
having (select avg(list_price)
from product_information)
>= avg(list_price);

but it is documented that it can't works :

QUOTE There are also important restrictions on scalar subqueries. Scalar subqueries can�t be used for:
Default values for columns
RETURNING clauses
Hash expressions for clusters
Functional index expressions
CHECK constraints on columns
WHEN condition of triggers
GROUP BY and HAVING clauses
START WITH and CONNECT BY clauses

I probably don't understand the limitation .

View 10 Replies View Related

SQL & PL/SQL :: Restriction On Analytical Functions

Dec 6, 2012

Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .

For Ex: Departments wise count including all employees record :

SQL> select count(*) over(partition by deptno) dept_Count, ce.*
2 from scott.emp ce
3 order by deptno, job;

DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10
3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
3 7839 KING PRESIDENT 11/17/1981 5000.00 10
5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Restriction In Date Range?

May 7, 2012

I want my user to be restricted for entering duplicate time within two times.

create table asd(dt_frm date,dt_to date);

insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi'));
insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));

now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.

View 8 Replies View Related

Restriction On Database Access

Oct 17, 2012

I have one question regarding database access. I have one database server on which 3 databases are running. I want to restrict each database access for particular group only , so if anyone outside of this group try to access the database then they can not access the database.

Question is end user never login to Database server and access the database , they always connect to the database using different tool like Pl/sql dev,sqldev etc.

Is there any option through which I can make database access within group only ?

View 1 Replies View Related

SQL & PL/SQL :: Archive Logs Restriction

May 2, 2010

I have one requirement saying that " Can we restrict archive logs for some tables".

View 7 Replies View Related

XML DB :: Add Attribute Node To Xmltype

Aug 31, 2012

How can I add an attribute node to an existing xml in a xmltype?

Say I have

declare
  t_xml xmltype;
begin
  t_xml := xmltype( '<x a="1"/>' );
end;How can I manipulate this variable t_xml to become
<x a="1" b="2"/>Anton

View 14 Replies View Related

PL/SQL :: PLS-00436 - Implementation Restriction

Jul 10, 2012

Currently, I am using oracle 10g and getting foolowing error

"PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records"

When trying to access attribute in record-collection inside FORALL. I know its prohibited in 10g but allowed in 11g. My questions is how can we achieve this in 10g. I will rephrase my problem:

I have three record-collections say rc1, rc2 and rc3 and I need to put all these record-collection inside final record collection say FC using FORALL

View 2 Replies View Related

XML DB :: Access Data Using XMLType

Oct 2, 2013

I have the following XML structure: 

<TZR Action="Insert" OrgaT="400"> <Division id="4">  <label language="de">ok</label>   <label language="en">Live engl.</label> <Productstructure id="300" Sparte="5">  <label language="en">Live engl.</label> </Productstructure> </Division></TZR>    

 I'd like to get  4, ok, Live engl, 300, 1, Live engl.   

using XMLTYPE but how to get

id="4" or Sparte="1"   SELECT *FROM XMLTable(       '/TZR'       passing XMLType(bfilename('XML_DATA_DIR', 'xxx.xml'), nls_charset_id('AL32UTF8'))       columns Division_id       varchar2(30)       path 'Division/id'     ); Oracle 11.2.02 on Windows 2008.

View 2 Replies View Related

Inserting Restriction By Existing Data

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

SQL & PL/SQL :: User Restriction On Database Level?

Dec 13, 2011

For the list of userid's, how to find the list of OS/Restricted Shell ID's at the database level ?

View 7 Replies View Related

SQL & PL/SQL :: Create A Function That Return XmlType

Apr 29, 2010

i want to create a function that build a return xml (XmlType):

create or replace
function plainLanguageSummary(nip varchar2,id number,code_language varchar2) return XmlType
as

[Code].....

but in the compilation i got the following error :

Error(10,62): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following: ( - + case mod new not null others <identificateur> <identificateur entre guillemets> <variable bind> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <un littéral de chaîne avec spécification de jeu de caractères> <un nombre> <une chaîne SQL entre apostrophes> pipe <constante de chaîne éventuellement entre guillemets avec indication du jeu de

he seems to not like the first "select" he encounter!.

View 9 Replies View Related

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 View Related

Client Tools :: Insert XML Into XMLType With Value

Mar 29, 2012

I'm trying to insert an XML into an XMLType with the following value:

insert into Table1(
code, xmlColumn) VALUES
(1, '<script> > </script>');

the question is I need to have > in the text, but Oracle insists in putting it like <script> > </script>.

What do I have to do to solve this?

View 15 Replies View Related

TNS-00510 / Internal Limit Restriction Exceeded

Aug 23, 2011

TNS-12540: TNS:internal limit restriction exceeded

TNS-12560: TNS:protocol adapter error

TNS-00510: Internal limit restriction exceeded

DEC OSF/1 AXP Error: 28: No space left on device

View 4 Replies View Related

JDeveloper, Java & XML :: Selecting The Attribute Value Of Given Xmltype

Dec 12, 2011

I need to get the value of an attribute for the given attribute name of xmltype.

DROP TABLE some_things ;

create table some_things (object_value CLOB);

insert into some_things values
('<?xml version="1.0" encoding="UTF-8"?>
<indexAttributes>
<attribute name="PENSION_APPLICATION.APPLICATION_ID" value="App123 "/>
<attribute name="PERSON.ALT_IDENTIFIER" value="9046095052"/>

[Code]...

Now i need to get the application id and the corresponding ssn number. I tried by using extractValue function. But it's not retuning any value

View 4 Replies View Related

SQL & PL/SQL :: Storing And Reading Xml Data From XMLType Table

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

SQL & PL/SQL :: Load Xml File Into Table Having Xmltype Datatype?

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

SQL & PL/SQL :: How To Find Certain Rows From A Table According To Position Of Node In XMLTYPE

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

JDeveloper, Java & XML :: Loading Xml_data Into Xmltype Field In Table

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

XML DB :: How To Select Parent / Child Related Data From XMLTYPE Table

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

Forms :: Restriction On New Record Addition In Datablock Through Form Menu Plus Icon?

Sep 23, 2011

i am populating 3 records in my data block.i do not want more than 3 rows.but when i click the plus icon in form menu one more row is getting generated which i do not want. i can restrict the new record addition through plus icon in menu bar for a particular block.

View 1 Replies View Related

Session Restriction - Limit Number Of Open Application Actions By Some Os User?

Sep 17, 2013

,in 11g,is there a way I could limit the number of open application actions by some os user ?We have an application where users are executing the same thing while the last is not yet finished,so we have several same things runninng at the same time executed by the same user.

Can we restrict that somehow through the database or that needs to be  done through application?

View 4 Replies View Related

How To Remove Fragmentation

Sep 24, 2012

I have table of 60 gb(indexes 60gb ) and it is subject to fragmentation around 10gb .

I m going to remove fragmentation . As i know i have three options

1.expdp/impdp .
2.CTAS (create table ........as.......) with parallel option .
3.Moving table into another tablespace with parallel option .

I have 4 physical and 4 logical cores(total 8 cores) on server .

View 1 Replies View Related

SQL & PL/SQL :: Remove Only One Leading 0?

Jun 3, 2010

I receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:

CREATE TABLE leading_0_test
(
col_1 VARCHAR2(10)
)

insert into leading_0_test values('00123')
select trim(leading'0' from COL_1),trim(leading'0' from substr(COL_1,1,1)),COL_1 from leading_0_test

The first trim removes all leading zeros. Therefore I tried to solve it with the substr, but without success.

View 7 Replies View Related

SQL & PL/SQL :: How To Remove Space

Oct 20, 2010

query string in such like that index.php?name=tejaspatel

i have table in record is available below

select * from emp where name = :name // it is query string parameter

emp
name
tejas patel

then how to match this record ?

View 8 Replies View Related

SQL Statement To Remove Extension

Oct 15, 2008

I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved