Home » Archived » BIRT » Oracle - Using different schemas
| Oracle - Using different schemas [message #254548] |
Wed, 05 September 2007 21:05  |
Eclipse User |
|
|
|
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  |
| Eclipse User |
|
|
|
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 |
| Eclipse User |
|
|
|
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 #255934 is a reply to message #255666] |
Fri, 21 September 2007 18:43 |
| Eclipse User |
|
|
|
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 #258750 is a reply to message #255098] |
Thu, 18 October 2007 19:50 |
| Eclipse User |
|
|
|
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
|
|
| |
Goto Forum:
Current Time: Wed Apr 24 16:02:04 GMT 2024
Powered by FUDForum. Page generated in 0.02901 seconds |
] 
Search
Help
Register
Login
Home
