Forum Stats

  • 3,815,999 Users
  • 2,259,128 Discussions
  • 7,893,360 Comments

Discussions

Commit in Portal Stored Procedure

435064
435064 Member Posts: 2
edited Feb 18, 2010 6:55AM in SQL & PL/SQL
Hi,

I'm working with Oracle Portal 10.1.4.0.0 (Build: 594) and need to do the following,..

1/ Read an XML file from a server directory and store it in an table in a column of XMLType.
2/ Extract certain nodes and use the info to populate a relational table.

For testing, i used SQL PLus worksheet and ran the following commands. (xmldir already set up)

insert into test_xml_tbl values (XMLType(bfilename('XMLDIR', '18758084431.xml'), nls_charset_id('AL32UTF8')));

This works.

Then i do this,...

insert into drp.test_xml_data_tbl (Title, RESEARCHCENTRE, Category, docnumber, Language) VALUES
((select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/language') from test_xml_tbl),
(select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/researchCentre') from test_xml_tbl),
(select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/documentCategory') from test_xml_tbl),
(select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/DocumentNumber') from test_xml_tbl),
(select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/title') from test_xml_tbl));

This also works and i can now select values from the test_xml_data_tbl table.

This is exactly what i want to do but i need it done within a Portal stored procedure and this is where my problem occurs.

------

For testing, i put the 2 snippets of code above into 2 separate procedures within Portal.

When i do this, the first procedure will run and populate the XML table with no problems.

BUT the 2nd procedure WILL NOT run. (error - (WWV-11230))

Now if i flip back over to SQL Plus Worksheet and issue a "commit", then flip back to portal, procedure #2 will now run.

Even if i put in an actual 'commit' command in my first procedure, it doesn't seem like the work is being committed when it's done within Portal. And because it's not being committed, my 2nd procedure fails.

At least i think this is my problem. :-)

I'd appreciate any comments or ideas.
Tagged:

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,803 Red Diamond
    edited Feb 17, 2010 12:51AM
    Chris P wrote:

    I'm working with Oracle Portal 10.1.4.0.0 (Build: 594) and need to do the following,..
    No idea what that is.. there are (or should be) dedicated forums on OTN dealing with that product. This forum is for PL/SQL and SQL languages, so you should only expect assistance on these.
    Then i do this,...

    insert into drp.test_xml_data_tbl (Title, RESEARCHCENTRE, Category, docnumber, Language) VALUES
    ((select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/language') from test_xml_tbl),
    (select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/researchCentre') from test_xml_tbl),
    (select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/documentCategory') from test_xml_tbl),
    (select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/DocumentNumber') from test_xml_tbl),
    (select extractVALUE(OBJECT_VALUE, '/DocumentMetaData/title') from test_xml_tbl));

    This also works and i can now select values from the test_xml_data_tbl table.
    Very poor approach. Count the number of SELECT statements being executed. Identical statements, with only the SQL projection differing. Why use that many when a single SELECT statement is faster and will scale better?
    insert into drp.test_xml_data_tbl (
      Title, RESEARCHCENTRE, Category, docnumber, Language
    ) 
    select
      extractVALUE(OBJECT_VALUE, '/DocumentMetaData/language'),
      extractVALUE(OBJECT_VALUE, '/DocumentMetaData/researchCentre'),
      extractVALUE(OBJECT_VALUE, '/DocumentMetaData/documentCategory'),
      extractVALUE(OBJECT_VALUE, '/DocumentMetaData/DocumentNumber'),
      extractVALUE(OBJECT_VALUE, '/DocumentMetaData/title') 
    from test_xml_tbl;
    Keep it simple. Keep the number of moving parts (be that s/w layers, program design, language statements) to the minimum needed to achieve the objective.
    For testing, i put the 2 snippets of code above into 2 separate procedures within Portal.
    When i do this, the first procedure will run and populate the XML table with no problems.
    BUT the 2nd procedure WILL NOT run. (error - (WWV-11230))
    This is not an Oracle (SQL or PL/SQL) error - you need to look at the product's error manual/info for resolving that.
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,803 Red Diamond
    A comment in general. I notice that you are using the suffix "+_tbl+" for table names - why?

    From a developer's perspective, why would he/she need to know that it is a table? And not a synonym? Or a view? Or a pipelined table function? And why just a table? If you argue that knowing the object type is important, then surely by the same token you need to know exactly what the object type is? There are a number of different table structures. It can be a hash table. Or an index organised table. It can be a partitioned table. It can be a clustered table. Etc.

    Fact is that this is not important to you - why would you care whether the table is hash or index organised? So why then do you need to know whether it is a table and identify it by using a "+_tbl+" suffix? It does not make sense.

    Also, from a data modeling perspective, this is just plain silly. Objects in the database need to accurately reflect the entities in the data model. Standard method says that the data model will for example define entities CUSTOMER, PRODUCT and INVOICE and so on.

    These will then be implemented as data objects (e.g. tables) CUSTOMERS, PRODUCTS and INVOICES in the database - plural being used as the data object contains multiple of the same data model entities.

    There's absolutely no need at data modeling level, logical database implementation level, or development level, to know the physical implementation details of a data object - and then tack on these as so-called "+attributes+" to the name of the object.

    Hungarian notation, in most (if not all) its forms, is dead. And it especially has no place in data modeling, logical databases and the SQL language.
  • 435064
    435064 Member Posts: 2
    Thanks for the SELECT tips. Just new to the and i copied that code from the web.

    As far as table names go. There is no data modeling going on here,..it's simply some test tables to see how it all works. They will all be deleted when i figure out how it works. Until then, who cares how they're named.
This discussion has been closed.