Re: plsxml setup yields errors with checkdeps.sql

From: Bill Lucas <Homebrew42_at_hotmail.com>
Date: Fri, 14 Feb 2003 22:36:12 GMT
Message-ID: <wbe3a.7935$jR3.4222310@news1.news.adelphia.net>

"Frank" <fvanbortel_at_netscape.net> wrote in message news:3E4D681F.4050005_at_netscape.net...
> Bill Lucas wrote:
> > "Frank" <fvanbortel_at_netscape.net> wrote in message
> > news:3E4C0263.1050508_at_netscape.net...
> >
> >>Bill Lucas wrote:
> >>
> >>>Hello all,
> >>>
> >>>Here is a screen shot of the info I get back when running checkdeps.sql
> >>
> > on
> >
> >>>our Oracle Server Version information included in screen shot
> >>>
> >>>Connected to:
> >>>Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> >>>With the Partitioning option
> >>>JServer Release 8.1.7.0.0 - Production
> >>>
> >>>SQL> @checkdeps.sql
> >>>DECLARE
> >>>*
> >>>ERROR at line 1:
> >>>ORA-06502: PL/SQL: numeric or value error
> >>>ORA-06512: at "SYS.OWA_UTIL", line 315
> >>>ORA-06512: at "SYS.HTP", line 859
> >>>ORA-06512: at "SYS.HTP", line 974
> >>>ORA-06512: at "SYS.HTP", line 992
> >>>ORA-06512: at line 10
> >>>
> >>>Can anyone help with these errors? Am I missing something? Oracle is
> >>
> > still
> >
> >>>a foreign entity to me, and I will freely admit my ignorance in this
> >>
> > case.
> >
> >>>Can anyone point me in a direction to try?
> >>>
> >>>For the interested we are starting to rewrite our application for an
> >>
> > oracle
> >
> >>>backend. We are currently investigating the various methods of getting
> >>
> > xml
> >
> >>>out of an oracle database. We are still ruinning against 8i and are
> >>>planning on developing to that platform. We can not use 9i as our
> >>
> > customer
> >
> >>>surveys indicated most are still running 8.x.x and are reluctant to
> >>
> > upgrade
> >
> >>>to 9i. So if there are better ways to get XML out of the database
> >>
> > please
> >
> >>>let me know... One of the things the dbxml package looked like it
> >>
> > allowed
> >
> >>>was custome tag names which we find very desirable. Any information on
> >>
> > this
> >
> >>>would be welcome.
> >>>
> >>>Regards,
> >>>Bill Lucas
> >>>
> >>>
> >>
> >>Apart from all the (good) advise to get the latest patch, I doubt
> >>if this has anything to do with it.
> >>This script is designed to be called from an HTTP-enabled environment.
> >>Calls to OWA_UTIL (Oracle Web Application) and HTP (HyperText Package)
> >>will yield no effect on a sql prompt.
> >>
> >>Are you sure, this is meant to run like this?
> >>And there seems to be an error on line 10 of the script. Maybe it was
> >>written for a previous version of the OWA packages (they came with
> >>Designer/2000 before declared standard in the database).
> >>
> >>--
> >>Regards, Frank van Bortel
> >>
> >
> >
> > Frank,
> >
> > Here is the link to the full page.
> > http://otn.oracle.com/tech/xml/htdocs/plsxml/install.htm
> >
> > The pertaining excerpt says...
> >
> > Make sure that your database schema has access to the following packages
on
> > which PLSXML's utilities depend:
> > HTP
> > HTF
> > UTL_FILE
> > OWA
> > OWA_UTIL
> > UTL_HTTP
> > To easily determine if you have the appropriate access to these
packages,
> > try running the checkdeps.sql script from the install subdirectory:
> > SQL> @checkdeps
> >
> > I followed these instructions and got the errors listed in the OP. I
ran
> > the @checkdeps script from both my account and later from the sys
account
> > with the same results. I should also note that even trivial example
scripts
> > calling the htp package fail as well. I am currently installing a clean
> > machine to see if I can trouble shoot this problem. If you have any
further
> > thoughts I would be interested in hearing them.
> >
> > Thanks for the input.
> >
> > Regards,
> > Bill
> >
> >

>

> Ah! Now I see where you're going to... There are two errors in the
> script... try:

>

> DECLARE
> /* If this routine executes successfully, you have
> ** all the right access to OWA and UTL* packages that
> ** you need to run DBDOM, DBXML, and DBXSL
> */
> f UTL_FILE.FILE_TYPE;
> p UTL_HTTP.HTML_PIECES;
> v OWA.VC_ARR;
> BEGIN
> htp.p(htf.strong('Hello'));
> owa_util.mime_header('text xml', true);
> END;
> /

>

> BTW - XML gets installed standard... 8i/9i as well as 9iAS
> Just check if you have a XDK directory.

>

> To run XSQL, you need several things checked:

>

> locate the file xml.conf - it should be in xdk\admin.
> See if there is an entry regarding XSQLConfig.xml. Make a note of it.

>

> I have seen it in %OH%\oracore\admin (standard 8i RDBMS),
> %iAS_Home%\XDK\Admin, and other locations.
> If there is no mention of this file in xml.conf, it should
> be under %iAS_Home%\XDK\Admin if you have 9iAS installed.

>

> Open %iAS_Home%\XDK\Admin\XSQLConfig.xml.
> Check your demo connector description, standard is:
> <connection name="demo">
> <username>scott</username>
> <password>tiger</password>
> <dburl>jdbc:oracle:thin:@localhost:1521:ORCL</dburl>
> <driver>oracle.jdbc.driver.OracleDriver</driver>
> <autocommit>true</autocommit>
> </connection>

>

> It's quite at the bottom of the file. If your test SID is not ORCL,
> you need to change it here. If you ever want other users to logon
> you will need to add them here. This is the file where the link
> between login information and the XML connector is laid.

>

> Check xml.conf for a XML entry. Something like:
> Alias /xsql/ "D:\oracle\9iAS/xdk/demo/java/xsql/"

>

> Create a file, e.g. test.xqsl, with:
> <?xml version="1.0"?>
> <xsql:query connection="demo" xmlns:xsql="urn:oracle-xsql">

>

> select 'Hello World' as test from dual

>

> </xsql:query>

>

> Invoke it: http://you_server:port/xsql/test.xsql.
> --
> Regards, Frank van Bortel

>

Frank,

Thanks for the great reply, I will check into it. Funny the @Checkdeps.sql script distributed with the packages has errors in it. There was too much for me to digest right now. I am already late for a date with SWMBO, so I will check this out over the weekend, and hopefully it will get me on to the next step.

I should mention this though. The included support for XML (from the XDK) using DBMS_XMLQUERY.GetXML() Seems to UCASE all the tags. We have an existing front end that the XML Tag Specification written for it requires Tags in Camel Back notation. So we are trying to hard to get the XML out of the DB in the same format as we are already using. To that effect it looks like the plsxml package allows you to define your own tags and it its syntax seems rather friendly so we are going to look at that. Of course all this would be a non issue if we could develop against 9i, which we can't because over 80% of out customers who are interested in the Oracle version are still running 8i with no real timeframe to validating and qualifying 9i for systems that fall under cGxP control.

Appreciate the help,

Regards,
Bill Lucas Received on Fri Feb 14 2003 - 16:36:12 CST