Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Archived » BIRT » Oracle - Using different schemas
Oracle - Using different schemas [message #254548] Wed, 05 September 2007 21:05 Go to next message
Eclipse UserFriend
Originally posted by: edk.simsol.com

Our application schema is in lets say SCHEMA_A. My reporting will be
done from SCHEMA_B. I want to avoid at all cost to have any references
to SCHEMA_A in any of the SQL's or calls to stored procedures in any of
the data sets.

My options are create a private synonym in SCHEMA_B that references the
tables and packages in SCHEMA_A. That works for tables but I'm having
no luck with packages. Even though I create a synonym to PACKAGE_A I
get an error in the data set wizard if I do not have the full syntax
{call SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)} If I have {call
PACKAGE_A.PROCEDURE_A(?,?)} I get an error "SQL statement does not
return a result set object..." If I qualify it with the schema it works
fine.

But synonyms are not my ideal solution and I would really like to use
something that gets called when a connection is made to alter the
session to change the current schema. "alter session set current_schema
= SCHEMA_A;"

I found a post that mentions using the 'beforeOpen' DataSource script to
include something like this...
this.queryText = "alter session set current_schema = SCHEMA_A;";

I can't seem to get that to work as it never makes that call to the
database.

So my questions are.
1) How do I make a call to the DB in the data source when a connection
is opened to run a statement like "alter session set current_schema =
SCHEMA_A;"?
2) Why don't synonyms work with packages?

Thanks,

Ed
Re: Oracle - Using different schemas [message #254550 is a reply to message #254548] Wed, 05 September 2007 21:12 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: edk.simsol.com

Sorry but forgot to mention what versions I'm running...

Eclipse 3.3.0
Birt 2.2.0


Ed wrote:
> Our application schema is in lets say SCHEMA_A. My reporting will be
> done from SCHEMA_B. I want to avoid at all cost to have any references
> to SCHEMA_A in any of the SQL's or calls to stored procedures in any of
> the data sets.
>
> My options are create a private synonym in SCHEMA_B that references the
> tables and packages in SCHEMA_A. That works for tables but I'm having
> no luck with packages. Even though I create a synonym to PACKAGE_A I
> get an error in the data set wizard if I do not have the full syntax
> {call SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)} If I have {call
> PACKAGE_A.PROCEDURE_A(?,?)} I get an error "SQL statement does not
> return a result set object..." If I qualify it with the schema it works
> fine.
>
> But synonyms are not my ideal solution and I would really like to use
> something that gets called when a connection is made to alter the
> session to change the current schema. "alter session set current_schema
> = SCHEMA_A;"
>
> I found a post that mentions using the 'beforeOpen' DataSource script to
> include something like this...
> this.queryText = "alter session set current_schema = SCHEMA_A;";
>
> I can't seem to get that to work as it never makes that call to the
> database.
>
> So my questions are.
> 1) How do I make a call to the DB in the data source when a connection
> is opened to run a statement like "alter session set current_schema =
> SCHEMA_A;"?
> 2) Why don't synonyms work with packages?
>
> Thanks,
>
> Ed
Re: Oracle - Using different schemas [message #255098 is a reply to message #254548] Tue, 11 September 2007 19:00 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: edk.simsol.com

Anyone have any insight on this??

Eclipse 3.3.0
Birt 2.2.0

Ed wrote:
> Our application schema is in lets say SCHEMA_A. My reporting will be
> done from SCHEMA_B. I want to avoid at all cost to have any references
> to SCHEMA_A in any of the SQL's or calls to stored procedures in any of
> the data sets.
>
> My options are create a private synonym in SCHEMA_B that references the
> tables and packages in SCHEMA_A. That works for tables but I'm having
> no luck with packages. Even though I create a synonym to PACKAGE_A I
> get an error in the data set wizard if I do not have the full syntax
> {call SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)} If I have {call
> PACKAGE_A.PROCEDURE_A(?,?)} I get an error "SQL statement does not
> return a result set object..." If I qualify it with the schema it works
> fine.
>
> But synonyms are not my ideal solution and I would really like to use
> something that gets called when a connection is made to alter the
> session to change the current schema. "alter session set current_schema
> = SCHEMA_A;"
>
> I found a post that mentions using the 'beforeOpen' DataSource script to
> include something like this...
> this.queryText = "alter session set current_schema = SCHEMA_A;";
>
> I can't seem to get that to work as it never makes that call to the
> database.
>
> So my questions are.
> 1) How do I make a call to the DB in the data source when a connection
> is opened to run a statement like "alter session set current_schema =
> SCHEMA_A;"?
> 2) Why don't synonyms work with packages?
>
> Thanks,
>
> Ed
Re: Oracle - Using different schemas [message #255666 is a reply to message #254548] Mon, 17 September 2007 21:05 Go to previous messageGo to next message
Snjezana Peco is currently offline Snjezana PecoFriend
Messages: 789
Registered: July 2009
Senior Member
You probably forgot to grant the Execute privilege on PACKAGE_A to SCHEMA_B

Snjeza

Ed wrote:
....
> 2) Why don't synonyms work with packages?
>
Re: Oracle - Using different schemas [message #255934 is a reply to message #255666] Fri, 21 September 2007 18:43 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: edk.simsol.com

Nope I have granted execute to the schema and also created a synonym for
it. I can log in through Toad or SQL Developer with the same schema
Birt is using and run it with out the schema name just fine through an
anonymous block. For some reason Birt chokes if you use a different
schema to call a package that is in another schema without the schema
name in the call.

The error I get says
Cannot execute the statement
SQL Statement does not return a ResultSet object
SQL error #1 ORA-06550 line 1 column 7:
PLS-00306: wrong number of types of arguments in call to 'ACTIVITY_REPORT'
ORA-06550: line 1, column7;
PL/SQL statement ignored

I'm using Oracle's 10.2 JDBC drivers.

In Birt my data source connect to SCHEMA_B.
In my query I am using a ref cursor from
SCHEMA_A.REPORTS_PKG.ACTIVITY_REPORT() I have granted execute to
SCHEMA_B and in SCHEMA_B created a local synonym.

If I use SCHEMA_A.REPORTS_PKG.ACTIVITY_REPORT(?,?,?) every thing works
fine. But as soon as I remove SCHEMA_A from the call it gives the above
error.

Anyone run into this issue? Any clues on how to get this to work
without having to put the schema name in??

Thanks,

Ed


Snjezana Peco wrote:
> You probably forgot to grant the Execute privilege on PACKAGE_A to SCHEMA_B
>
> Snjeza
>
> Ed wrote:
> ...
>> 2) Why don't synonyms work with packages?
>>
Re: Oracle - Using different schemas [message #255963 is a reply to message #255934] Sat, 22 September 2007 20:54 Go to previous messageGo to next message
Snjezana Peco is currently offline Snjezana PecoFriend
Messages: 789
Registered: July 2009
Senior Member
I don't believe that your problem is related to BIRT.
Oracle's JDBC drivers have a lot of bugs. Toad and SQL Developer are
Oracle specific tools. I suppose that Toad doesn't use JDBC and SQL
Developer has fixes for different Oracle JDBC bugs.
Try to make a simple Java application that calls your procedure.

Snjeza

Ed wrote:
> Nope I have granted execute to the schema and also created a synonym for
> it. I can log in through Toad or SQL Developer with the same schema
> Birt is using and run it with out the schema name just fine through an
> anonymous block. For some reason Birt chokes if you use a different
> schema to call a package that is in another schema without the schema
> name in the call.
>
> The error I get says
> Cannot execute the statement
> SQL Statement does not return a ResultSet object
> SQL error #1 ORA-06550 line 1 column 7:
> PLS-00306: wrong number of types of arguments in call to 'ACTIVITY_REPORT'
> ORA-06550: line 1, column7;
> PL/SQL statement ignored
>
> I'm using Oracle's 10.2 JDBC drivers.
>
> In Birt my data source connect to SCHEMA_B.
> In my query I am using a ref cursor from
> SCHEMA_A.REPORTS_PKG.ACTIVITY_REPORT() I have granted execute to
> SCHEMA_B and in SCHEMA_B created a local synonym.
>
> If I use SCHEMA_A.REPORTS_PKG.ACTIVITY_REPORT(?,?,?) every thing works
> fine. But as soon as I remove SCHEMA_A from the call it gives the above
> error.
>
> Anyone run into this issue? Any clues on how to get this to work
> without having to put the schema name in??
>
> Thanks,
>
> Ed
>
>
> Snjezana Peco wrote:
>> You probably forgot to grant the Execute privilege on PACKAGE_A to
>> SCHEMA_B
>>
>> Snjeza
>>
>> Ed wrote:
>> ...
>>> 2) Why don't synonyms work with packages?
>>>
Re: Oracle - Using different schemas [message #258750 is a reply to message #255098] Thu, 18 October 2007 19:50 Go to previous messageGo to next message
Eclipse UserFriend
Originally posted by: edk.simsol.com

I'm posting this again to see if anyone has any ideas. You can read teh
original message below but a quick summary is a quick summary. I have
the application schema SCHEMA_A and a reporting schema SCHEMA_B. I'm
calling a procedure in a package in SCHEMA_A that returns a ref cursor.
In Birt my Data Source connects to SCHEMA_B. My Data Set call looks
something like this {call SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)} which
works 100% great. For SCHEMA_B SCHEMA_A granted execute or select on
all packages and tables involved. Also in SCHEMA_B I created private
synonyms that point to the SCHEMA_A objects.

Now since this call is working 100% fine from SCHEMA_B {call
SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)} that means the grants are working
correctly. Now to test the synonyms I removed the schema from the call
so now it looks like this {call PACKAGE_A.PROCEDURE_A(?,?)}. This is
now where I get the error "SQL statement does not return a result set
object...". If I create another data set that does a simple select,
select * from TABLE_A where TABLE_A is in SCHEMA_A it works fine so I
know synonyms do work for tables and DO NOT work for stored procedures.

After doing some more debugging by tracing the session this looks like
its a birt problem. I traces a session where the data set calls a
stored procedure. I think the offending SQL call is this one....

SELECT package_name AS procedure_cat,
owner AS procedure_schem,
object_name AS procedure_name,
argument_name AS column_name,
DECODE(position, 0, 5,DECODE(in_out, 'IN', 1,
'OUT', 4,
'IN/OUT', 2,
0)) AS column_type,
DECODE (data_type, 'CHAR', 1,
'VARCHAR2', 12,
'NUMBER', 3,
'LONG', -1,
'DATE', 91,
'RAW', -3,
'LONG RAW', -4,
'TIMESTAMP', 93,
'TIMESTAMP WITH TIME ZONE', -101,
'TIMESTAMP WITH LOCAL TIME ZONE', -102,
'INTERVAL YEAR TO MONTH', -103,
'INTERVAL DAY TO SECOND', -104,
'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101,1111) AS
data_type,
DECODE(data_type, 'OBJECT', type_owner || '.' || type_name,
data_type) AS type_name,
DECODE (data_precision, NULL, data_length,
data_precision) AS precision,
data_length AS length,
data_scale AS scale,
10 AS radix,
1 AS nullable,
NULL AS remarks,
sequence,
overload,
default_value
FROM all_arguments
WHERE owner LIKE :1 ESCAPE '/'
AND object_name LIKE :2 ESCAPE '/'
AND package_name LIKE :3 ESCAPE '/'
AND (argument_name LIKE :5 ESCAPE '/'
OR (argument_name IS NULL
AND data_type IS NOT NULL))
ORDER BY procedure_schem, procedure_name, overload, sequence

This statement which is run before the data set stored procedure call is
the one I'm suspecting has the problem. This statement is including an
Owner in the where clause which I suspect it is getting from parsing the
stored procedure call "{call SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)}". If
no owner is present in the call I guess its probably populating the
current username as the owner and hence my problem.

Anyway to get around this? Jason? Anyone?

Thanks,
Ed



Ed wrote:
> Anyone have any insight on this??
>
> Eclipse 3.3.0
> Birt 2.2.0
>
> Ed wrote:
>> Our application schema is in lets say SCHEMA_A. My reporting will be
>> done from SCHEMA_B. I want to avoid at all cost to have any
>> references to SCHEMA_A in any of the SQL's or calls to stored
>> procedures in any of the data sets.
>>
>> My options are create a private synonym in SCHEMA_B that references
>> the tables and packages in SCHEMA_A. That works for tables but I'm
>> having no luck with packages. Even though I create a synonym to
>> PACKAGE_A I get an error in the data set wizard if I do not have the
>> full syntax {call SCHEMA_A.PACKAGE_A.PROCEDURE_A(?,?)} If I have
>> {call PACKAGE_A.PROCEDURE_A(?,?)} I get an error "SQL statement does
>> not return a result set object..." If I qualify it with the schema it
>> works fine.
>>
>> But synonyms are not my ideal solution and I would really like to use
>> something that gets called when a connection is made to alter the
>> session to change the current schema. "alter session set
>> current_schema = SCHEMA_A;"
>>
>> I found a post that mentions using the 'beforeOpen' DataSource script
>> to include something like this...
>> this.queryText = "alter session set current_schema = SCHEMA_A;";
>>
>> I can't seem to get that to work as it never makes that call to the
>> database.
>>
>> So my questions are.
>> 1) How do I make a call to the DB in the data source when a connection
>> is opened to run a statement like "alter session set current_schema =
>> SCHEMA_A;"?
>> 2) Why don't synonyms work with packages?
>>
>> Thanks,
>>
>> Ed
Re: Oracle - Using different schemas [message #1507739 is a reply to message #258750] Thu, 11 December 2014 22:16 Go to previous message
Carlos Moreno is currently offline Carlos MorenoFriend
Messages: 1
Registered: December 2014
Junior Member
I'm having the same issue. Confirmed the driver is not the problem since the JEE application uses the synonyms with no problems in the queries. It would be really bad practice to hardcode the schema owner in the call. Currently converting our Crystal Reports to BIRT and the Crystal Reports have no problem with synonyms in packages.
Using
BIRT 4.4.1
OJDBC 6
Oracle 11g

Previous Topic: Embed a Digitial Signature in Birt Report's
Next Topic:Problems with versions
Goto Forum:
  


Current Time: Thu Jul 25 00:21:55 GMT 2024

Powered by FUDForum. Page generated in 0.03361 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top