How to use oracle package in crystal report ?

[Former Member]

Hi,

I create a stored procedure in oracle package for a report. From Crystal Report XI I can preview the data.

Scott is the schema name. AREAL1 is my stored procedure in a package named MYLIST.

From jdev, if I use qualified name = SCOTT.AREAL1, I get error 'com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: JDBC Error: ORA-06550: line 1, column 16: PLS-00302: component 'SCOTT' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored -

---

Error code:-2147467259 Error code name:failed'

If I change qualified name to SCOTT.MYLIST.AREAL1, the error is 'com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: The table 'SCOTT.MYLIST.AREAL1' could not be found.---- Error code:-2147467259 Error code name:failed'

The difference in datasource location (crystal report) between report with stand alone stored procedure and packaged procedure is

stand alone procedure packaged procecdure

Catalog SCOTT

Owner SCOTT MYLIST

How do I get the catalog attribute in Crystal Java Reporting Component (JRC) ? What is the right qualified name for this ?

Thanks

[former_member11696]

Oracle connection does not require the owner. Change: table.setQualifiedName("SCOTT.MYLIST."+table.getName());

to

table.setQualifiedName("MYLIST."+table.getName());

Assuming MYLIST is the Oracle Instance name

[Former Member]

Hi,

Have you solve the problem ? I have a similar pb and I can't "qualify" correctly my package ?

When I save my report, i open it. I see all information but can't see the fields returns by my proc .

Thanks for your answer

Best regards

[Former Member]

Hi,

If you are able to run it through designer then you can check the connection string and qualified names in designer.

Goto Database Expert, click on Set Datasource Location, You will get all properties related to your database.

And the table_qualified)name should be in form "Catalog.Owner.Tablename"

Thanks,

Tej

[Former Member]

Hi Tejaswini Patil

Thanks for your answer.

I try hardcoding Catalog_name.Package_name.Stored_procedure_name in jdev (in switch_tables). Still receive error

'com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: The table 'SCOTT.MYLIST.AREAL1' could not be found.---- Error code:-2147467259 Error code name:failed'

It seems error come from databaseController.setTableLocation(table, tables.getTable(i));

Do you have any idea to set table location with the right attributes ?

Thanks

Peggy

Edited by: PeggyMuliawan on Jul 22, 2009 9:20 AM

[Former Member]

Hi Peggy,

Can you paste the code that you have written for your setTable Location?

Regards,

Tej

[Former Member]

Hi Tejaswini Patil

Here is the code :

private static void switch_tables(
             DatabaseController databaseController,          String pSchemaName,  String pDBUserName,   String pDBPassword, 
             String pServerName,                                        String pDBPort,             String pDBSID,              String pJNDIName
             ) throws ReportSDKException
       { 
           final String CONNECTION_STRING=
                   "!oracle.jdbc.driver.OracleDriver!jdbc:oracle:thin:{userid}/{password}@"+pServerName+":"+pDBPort+":"+pDBSID;
           final String DATABASE_DLL = "crdb_jdbc.dll";
           final String CONNECTION_URL = "jdbc:oracle:thin:@"+pServerName+":"+pDBPort+":"+pDBSID;
           Tables tables = databaseController.getDatabase().getTables();
           for (int i = 0; i < tables.size(); i++) {
                   ITable table = tables.getTable(i);
                   table.setName(table.getName());
                   table.setAlias(table.getAlias());
                    if (table.getQualifiedName().indexOf("Command") < 0) {
                        table.setQualifiedName("SCOTT.MYLIST."+table.getName()); 
                   } 
                   IConnectionInfo connectionInfo = table.getConnectionInfo();
                   PropertyBag propertyBag = new PropertyBag();
                   propertyBag.put("Trusted_Connection", "false");
                   propertyBag.put("Server Name", pServerName); //Optional property.
                   propertyBag.put("JDBC Connection String", CONNECTION_STRING);
                   propertyBag.put("Connection URL", CONNECTION_URL);
                   propertyBag.put("Use JDBC", "true");
                   propertyBag.put("Server Type", "JDBC (JNDI)");
                   propertyBag.put("Database DLL", DATABASE_DLL);
                   if (pJNDIName != null ) {
                     System.out.println("JNDI Name"+" "+pJNDIName );
                     propertyBag.put("JNDI Datasource Name", pJNDIName );
                   }
                   connectionInfo.setAttributes(propertyBag);
                   connectionInfo.setUserName(pDBUserName);
                   connectionInfo.setPassword(pDBPassword);
                   connectionInfo.setKind(ConnectionInfoKind.SQL);
                   table.setConnectionInfo(connectionInfo);
                   databaseController.setTableLocation(table, tables.getTable(i));
           }}

Thanks

Peggy

[ted_ueda]

You're likely encountering a few issues:

1. Early Crystal Reports for Eclipse 2.0 runtimes had a bug where if you modify stored procedure connectivity (it should be IProcedure and not ITable for stored procs), it ends up converting it to a SQL Table, which it can't find.

2. There was a limitation with the JDBC Oracle connectivity that affected access to stored procedures in packages - JDBC Oracle just didn't expose package names. So a workaround was made to map expected behavior, so you'd be able to reference schema.package.stored_proc as you're doing.

So what I think is happening here is the following:

You're saving the ITable, and it thinks it's a Table and not a Stored Proc. I would use the correct interface. And also make sure you have the latest CR4E 2.0 runtime.

Sincerely,

Ted Ueda