Home » Archived » BIRT » Oracle - Using different schemas
| Oracle - Using different schemas [message #254548] |
Wed, 05 September 2007 17: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 #258750 is a reply to message #255098] |
Thu, 18 October 2007 15: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
|
|
|
| Re: Oracle - Using different schemas [message #1507739 is a reply to message #258750] |
Thu, 11 December 2014 17:16 |
| Eclipse User |
|
|
|
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
|
|
|
Goto Forum:
Current Time: Wed May 07 18:53:06 EDT 2025
Powered by FUDForum. Page generated in 0.05251 seconds |
Home