Home » Archived » BIRT » Oracle - Using PL/SQL Packages.
| Oracle - Using PL/SQL Packages. [message #368637] |
Tue, 16 June 2009 20:37  |
Eclipse User |
|
|
|
Originally posted by: edk.simsol.com
It seems that when you use an Oracle package you have to specify the
schema name when using it in a stored procedure query. My
package.procedure has a few IN parameters and an OUT parameter that is a
REF CURSOR. When I specify a schema
(schema.package.my_procedure(?,?,?)) everything works fine. With no
schema I get PLS-00306: wrong number or types of arguments in call to
'MY_PROCEDURE'
If I create a standalone procedure then I do not have to specify the
schema name in the query.
It looks that the parser gets confused when I remove the schema name
from that package and tries to use the package name as the owner of the
package when Birt or maybe JDBC is looking up info for the package in
the data dictionary.
Is their any way to use a package as stored procedure query without
specifying the schema name? I started to create standalone wrapper
procedures that call the package but that is just silly.
Ed
|
|
|
| Re: Oracle - Using PL/SQL Packages. [message #368669 is a reply to message #368637] |
Thu, 18 June 2009 14:27  |
| Eclipse User |
|
|
|
Originally posted by: edk.simsol.com
So any Oracle people out there using a stored procedure query with
packages? I would like to use packages but I do not want to specify a
schema name.
Thanks,
Ed
Ed wrote:
>
> It seems that when you use an Oracle package you have to specify the
> schema name when using it in a stored procedure query. My
> package.procedure has a few IN parameters and an OUT parameter that is a
> REF CURSOR. When I specify a schema
> (schema.package.my_procedure(?,?,?)) everything works fine. With no
> schema I get PLS-00306: wrong number or types of arguments in call to
> 'MY_PROCEDURE'
>
> If I create a standalone procedure then I do not have to specify the
> schema name in the query.
>
> It looks that the parser gets confused when I remove the schema name
> from that package and tries to use the package name as the owner of the
> package when Birt or maybe JDBC is looking up info for the package in
> the data dictionary.
>
> Is their any way to use a package as stored procedure query without
> specifying the schema name? I started to create standalone wrapper
> procedures that call the package but that is just silly.
>
> Ed
>
|
|
| |
| Re: Oracle - Using PL/SQL Packages. [message #368677 is a reply to message #368671] |
Thu, 18 June 2009 15:56 |
| Eclipse User |
|
|
|
Originally posted by: edk.simsol.com
Packages work fine but you have to use schema.package.procedure() to
reference them. If you use a procedure you don't need to prefix it with
the schema name.
I want to use packages but do not want to prefix them with a schema
name. It looks whatever is parsing the stored procedure query
definition is counting '.' When it sees no '.' then it assumes
procedure. When it sees somethingA.somethingB it assumes
schema.procedure. It does not have the logic to figure out if
somethingA is a schema name or a package name so it is assuming schema
name. When it sees somethingA.somethingB.somethingC then it finds
schema.package.procedure.
Just trying to find a way to use packages without a prefixing them with
a schema name.
Ed
Dean Hiller wrote:
> interesting, so that is how you can use packages.....I could not figure
> that out but then I don't know oracle and PL/SQL well at all.
> thanks!!! We had just been putting the procedures at the global level
> not in a package at all since we could not get it to work.
>
|
|
|
| Re: Oracle - Using PL/SQL Packages. [message #368971 is a reply to message #368677] |
Fri, 10 July 2009 19:28 |
 Galen Boyer
Messages: 54
Registered: July 2009 |
Member |
|
|
The problem you are seeing is that the metadata querys being sent to the
Oracle engine needs the owner of the package (Which is the issue I
describe, because why should BIRT be asking anything about the package?
You already told him everything he needs to know when you were designing
the report!). It does not start with the fact that you might have logged
in as the owner. I did not see anyway around that. You remove the owner
from the {call XXX.PACKAGENAME.METHOD(?,?,?,?,?)} and, bang, error.
I can tell you from direct experience that using Oracle packages within
BIRT is a huge performance issue, HUGE! You, as a developer, spend time
on wiring BIRT to the package, telling BIRT exactly what the inputs are
and what the ref-cursor looks like, and, then, at runtime, BIRT goes off
and sends many, many, many metadata queries trying to figure out what the
package looks like, its inputs and outputs. I guess it keeps getting
answers it does not like and asking more questions. It seemingly asks
multiple questions per output parameter of the ref_cursor. Download
wireshark, start it up, kick off a BIRT report that accesses a package and
watch what gets sent across the wire. Or, better yet, make sure your
connection will get traced, kick off the report, and then tkprof the
tracefile. Or, turn logging up to FINEST and watch how much time BIRT
spends interrogating metadata.
For the client I'm working for, I rearchitected an entire suite of BIRT
reports to use global temporary tables instead of the packages it was
originally architected to execute. The execution of the worst offender
went from 15 minutes down to < 10 seconds. All of the time savings came
because the questions BIRT was asking to the database about the package it
was executing were removed. None of the time drain came from database
query performance.
On the other hand, when BIRT queries tables, views directly, it seems to
have no issue, except when the number of pages of a report start growing.
Haven't looked into this though, just seems to be that way.
|
|
|
| Re: Oracle - Using PL/SQL Packages. [message #444311 is a reply to message #368971] |
Fri, 31 July 2009 16:41 |
|
Galen,
Have you logged a bug for this? This is good information that needs to
get back into the project.
Jason
Galen Boyer wrote:
> The problem you are seeing is that the metadata querys being sent to the
> Oracle engine needs the owner of the package (Which is the issue I
> describe, because why should BIRT be asking anything about the package?
> You already told him everything he needs to know when you were designing
> the report!). It does not start with the fact that you might have
> logged in as the owner. I did not see anyway around that. You remove
> the owner from the {call XXX.PACKAGENAME.METHOD(?,?,?,?,?)} and, bang,
> error.
>
> I can tell you from direct experience that using Oracle packages within
> BIRT is a huge performance issue, HUGE! You, as a developer, spend time
> on wiring BIRT to the package, telling BIRT exactly what the inputs are
> and what the ref-cursor looks like, and, then, at runtime, BIRT goes off
> and sends many, many, many metadata queries trying to figure out what
> the package looks like, its inputs and outputs. I guess it keeps
> getting answers it does not like and asking more questions. It
> seemingly asks multiple questions per output parameter of the
> ref_cursor. Download wireshark, start it up, kick off a BIRT report
> that accesses a package and watch what gets sent across the wire. Or,
> better yet, make sure your connection will get traced, kick off the
> report, and then tkprof the tracefile. Or, turn logging up to FINEST
> and watch how much time BIRT spends interrogating metadata.
>
> For the client I'm working for, I rearchitected an entire suite of BIRT
> reports to use global temporary tables instead of the packages it was
> originally architected to execute. The execution of the worst offender
> went from 15 minutes down to < 10 seconds. All of the time savings came
> because the questions BIRT was asking to the database about the package
> it was executing were removed. None of the time drain came from
> database query performance.
>
> On the other hand, when BIRT queries tables, views directly, it seems to
> have no issue, except when the number of pages of a report start
> growing. Haven't looked into this though, just seems to be that way.
>
|
|
| | |
Goto Forum:
Current Time: Thu Apr 25 17:20:28 GMT 2024
Powered by FUDForum. Page generated in 0.03334 seconds |
] 
Search
Help
Register
Login
Home
