Need fundamental explanation of ORacle Package for Crystal

[nkshah]

experts,

i already developed one Storec Procedure with Ref Cursor in that.

and i defined packages and package body with PRocedrue and Ref Cursor.

Everything Works Perfect.

I added procedure in Crystal Reports and Working Excellent.

I want to know that, Why we need to defined package with Procedure and Ref Cursor ?

Because, one Oracle PRocedure compiled and Run, we directly adding Procedure in Reports and Package is
not coming in Picture.

Then, what is the use of Package here ? (i know kfundamental about Oracle Package - like a Function overloading in C++)

But i want to understand in term so Crystal Reports usage ...

Any help and explanation is well come.

Thanks..

 

[oradba101]

Did the package I uploaded help in any way?

Regards,

William Chadbourne
Programmer/Analyst

 

[oradba101]

This is from the official Crystal Reports documentation:

In order for Crystal Reports to report off Oracle stored procedures, all of the following requirements must be satisfied:
• To use an ODBC connection to access an Oracle stored procedure, you must create a package that defines the REF CURSOR. This REF CURSOR must be strongly bound to a static pre-defined structure (see Strongly Bound REF CURSORs vs. Weakly Bound REF CURSORs). This package must be created separately and before the creation of the stored procedure.
• A native connection to Oracle in Crystal Reports 9 and Crystal Reports 10 can be used to access an Oracle stored procedure that was created within a package and also an Oracle stored procedure that references a weakly bound REF CURSOR
• The stored procedure must have a parameter that is a REF CURSOR type. Crystal Reports uses this parameter to access and define the result set that the stored procedure returns.
• The REF CURSOR parameter must be defined as IN OUT (read/write mode). After the stored procedure has opened and assigned a query to the REF CURSOR, Crystal Reports will perform a FETCH call for every row from the query’s result. This is why the parameter must be defined as IN OUT.
• The parameters can only be defined as IN (input) parameters. Crystal Reports is not designed to work with OUT parameters.
• The REF CURSOR variable must be opened and assigned its query within the procedure.
• The stored procedure can only return one record set. The structure of this record set must not change based on parameters.
• The stored procedure cannot call another stored procedure.
• If you are using the CR ODBC driver, verify that the option Procedure Return Results is selected as On in the ODBC Driver Configuration setup under the Advanced tab.
• If you are using the native Oracle driver and using hard-coded date selection within the stored procedure, the date selection must use either a string representation format of YYYY-DD-MM (where the date field = 2004-01-01) or the To_Date formula function with the same format specified (where date field = To_Date(2004-01-01’,’YYYY-MM-DD’).
• Most importantly, the stored procedure must be able to execute successfully in Oracle’s SQL *Plus utility.
If all of these requirements have been met, verify that the database driver that you are using works with that version of Oracle.

Regards,

William Chadbourne
Programmer/Analyst

 

[Turkbear]

Hi,
You do not need to ask this in multiple forums ( 1 Crystal and 2 Oracle's so far) - most folks here read several forums and by using the correct one ( in this case 10G, since it is really about Oracle) you stand a better chance of coordinated answers.

After you get your answer as to why a package instead of just a procedure, ( see this:

http://download.oracle.com/docs/cd/A58617_01/server.804/a58227/ch14.htm

),
then you may have other issues that could be posted in one of the Crystal Reports forums.





To Paraphrase:"The Help you get is proportional to the Help you give.."