Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

xmlschemas

Posted on 2012-03-13
12
Medium Priority
?
270 Views
Last Modified: 2012-03-14
Hi,

  In the definition of a table i see this definition for a column.  

  The URL part of this definition means that for any insert/select/update/delete on this column it will need to go online to the internet ?

  So any DML operation on this table will take more time that usual because it has to go online  to this internet URL ?

  What does this part of URL means in the table definition ?

 

  ) XMLTYPE COLUMN "ADT_ADF_INFO" STORE AS CLOB  (
  TABLESPACE "my_tablespace" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
 XMLSCHEMA "http://url_web.com" ELEMENT "AuditTrail" ID 3288
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "my_tablespace"
  PARALLEL 4 ;
0
Comment
Question by:joe_echavarria
  • 7
  • 4
12 Comments
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 37715455
No

XMLSCHEMA clause forces your XMLTYPE column to be validated against the url_web.com schema you have registered using the DBMS_XMLSCHEMA package

There is a lot to this topic, far more than the scope of a single question.
I suggest reading up on it, starting in the following resources:


PL/SQL packages and types reference
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xmlsch.htm#i1008494

xml db developers guide
http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm#g1070409
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37715478
So it does not connect to the internet at any time  during any database operation ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37715493
there will be no internet connection based on what you have posted above.

there could be triggers or other procedural steps that could create connections though.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Author Comment

by:joe_echavarria
ID: 37715582
The URL i posted was " http://url_web.com ", which is not a valid one.

The original table definition has a valid url.  Having a valid URL does it means will need to connect to the internet for any DML operation ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37715616
no
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37715622
Thanks, i just wanted to confirm that.

Because the server on which the database will be installed won't have internet access.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37715631
>> the server on which the database will be installed won't have internet access.

that makes it really easy to confirm.  Just try it.
if it needed internet access it would fail.
0
 
LVL 1

Author Comment

by:joe_echavarria
ID: 37715653
What happens is that any dml operation on that table always takes too much time, and i was telling it was because it has to go to the internet and connect to that url.

I wanted to be sure before.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37715699
>>and i was telling it was because it has to go to the internet and connect to that url.

I suppose it is possible.  I've never tried this but there is a version or REGISTERSHCHEMA that accepts a URIType for the schema doc.

Look in DBA_XML_SCHEMAS for the registered schema to see if it is local to the database.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 37715946
>>and i was telling it was because it has to go to the internet and connect to that url.

I suppose it is possible.


No, it's not....

http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb05sto.htm#i1031977

Registering an XML Schema with Oracle XML DB

The main parameters to procedure DBMS_XMLSCHEMA.registerSchema are these:

    SCHEMAURL – the XML schema URL. This is a unique identifier for the XML schema within Oracle XML DB. It is conventionally in the form of a URL, but this is not a requirement. The XML schema URL is used with Oracle XML DB to identify instance documents, by making the schema location hint identical to the XML schema URL. Oracle XML DB never tries to access a Web server identified by the specified URL.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37715970
I will repeat my caveat from above though.

If you have something other than dml against the table, (triggers or other procedural logic) then those processes "might" try to connect.  But those processes wouldn't have any technical correlation to the xdb schema registration
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37716289
Just to clarify on comments above.  I had originally misunderstood what slightwv was trying to say.

I thought he was referring to uri columns on the table,  not the uri version of the registersschema procedure.  (Yes, I know he was pretty explicit about it, but I still somehow misread it.)

If you want to dothat, you should actually call registeruri, and let it invoke the corresponding registerschema procedure for you.

If you do this, then your are telling Oracle to go retrieve a remote (i.e. across the internet) schema document and store it locally in your xdb repository.

Once the schema doc is stored, then, just like any other registered schema, there is no internet access required.  If you already have a registered schema, then none of this applies.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question