Crystal Reports 8.5 & Oracle Stored Procedure

[ChiTownDiva]

Hi guys...

This is going to be difficult because I didn't write the stored procedure and I don't have access to it because it's written by an outside source.

I understand that the only stored procedure that can be written in oracle that will work in Crystal Reports is a ref cursor.

Without getting too much in depth the store procedure is:

PROCEDURE tfna (
in cursor IN OUT t_EmployeeRef,
in_begin_dt IN VARCHAR2,
in_end_dt IN VARCHAR2,
in_rc_code IN VARCHAR2,
in_company IN VARCHAR2,
in_state IN VARCHAR2,
in_mgt_level IN VARCHAR2,
in_job_title IN VARCHAR2,
in_crse1 IN VARCHAR2);

The record set output Record (t_EmployeeRef) looks like:

Position Field Name Field Type/Size
1 Student_Name PERSON Varchar2(62)
2 RC_Code PERSON.LOCAL_PERS_ID/Varchar2(20)
3 CUID_UID PERSON.LOGIN_ID/Varchar2 (20)
4 SSN PERSON.SSN/Varchar2 (20)
5 Job_Title PERSON.JOB_TITLE/Varchar2 (40)
6 Company PERSON.COMPANY/Varchar2 (30)
7 State PERSON.ADDRESS_STATE/Varchar2 (10)
8 Mgt_Level PERSON.MGT_LEVEL/Varchar2 (10)
9 Supervisor PERSON table supervisor name from SSN lookup. Varchar2(62)
10 Sup_phone PERSON table supervisor phone number type Varchar2(20)
11 Sup_CUID PERSON.LOGIN_ID/Varchar2 (20)
12 Person_Status PERSON.PERSON_STATUS/Varchar2 (20)


The "Supervisor" and "Student_Name" are composite fields.

When I go to create a report from the stored procedure, I get the list of fields from the SQL portion of the stored procedure:

Address_state
Company
Initcap(lower(concat(p.person.pers_last_name....)
Job_Title
Local_pers_id
Mgt_Level
Nvl((Initcap(lower(concat(supv.pers_last_name...)
login_id
login_id
pers_phone_num
ssn
Person_status
To_char(p.job_title_effective.....)

According to the programmer, I should be seeing the Field Names from the output record--Not the fields in the sql part of the procedure.

Is this right?

Thanks.

ChiTownDivaus

 

[synapsevampire]

Here's the CR whitepaper, give it to the DBA:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_oracle_stored_procedures.pdf.asp

-k

 

[ChiTownDiva]

Hi Synapsevampire...

He has the white paper. I need to understand because he's saying that there is something wrong with Crystal Reports. The procedure is bringing back two of the same fields from the same table...login_id. One should be the supervisors' login_id and one should be the students'.

Here's a snippet of the query:

SELECT INITCAP(LOWER(CONCAT(P.PERS_LAST_NAME||', ',P.PERS_FIRST_NAME))) AS "Student Name", P.LOCAL_PERS_ID AS "RC Code/HR", P.PERS_COST_CD AS "Cost Code",
P.LOGIN_ID AS "ID", P.PERS_SSN AS "SSN", P.JOB_TITLE AS "Job Title Code", P.COMPANY AS "Company", P.ADDRESS_STATE AS "State", P.MGT_LEVEL AS "Mgt Level", NVL((INITCAP(LOWER(CONCAT(SUPV.PERS_LAST_NAME||', ', SUPV.PERS_FIRST_NAME)))), 'NONE') AS "Supervisor", SUPV.PERS_PHONE_NUM AS "Sup Phone Num", SUPV.LOGIN_ID AS "Sup_ID", TO_CHAR(P.JOB_TITLE_EFFECTIVE_DT, 'MM/DD/YYYY') AS "JTC", P.PERSON_STATUS AS "Status"

I think he has the supervisors' ID defined in his output record the same as the students'.

What do yo think?

ChiTownDivaus

 

[lyanch]

I create only one global cursor with no delarations of fields to use over and over again..

I then name my fields in my stored proc. You are right, you are getting the names in the SQL.

Here is my general cursor package declaration:

PACKAGE Report_Package
IS TYPE Report_Type IS REF CURSOR;
END Report_Package;

Then I call for it in my SP's as such:

PROCEDURE RSP_Capacity (
Capacity IN OUT Report_Package.Report_Type,
StartDate in date,
EndDate in date)

It will automatically use the field names and types in my open cursor for SQL...

The white paper isn't exactly clear on this.. but it works well for me and i am not constantly defining new cursors.

Lisa

 

[ChiTownDiva]

I guess I don't completely understand Ref Cursors. What I need are the fields in the output

 

[ChiTownDiva]

Thanks Lyanch for responding...

I guess I don't completely understand Ref Cursors. What I need are the fields in the output record, t_EmployeeRef for my report. I know Crystal is not designed to work with OUT parameters, so how do you get to the all of the other fields that are not defined as an IN parameter? So even though I sending IN the course number, date etc., what I need back is the students name, supervisors name, etc.

What I need to know is everybody who hasn't completed a class along with their supervisor information.

Thanks in advance...

ChiTownDivaus

 

[lyanch]

You need to give the field names to the input query into the cursor.. in your example SQL it is doing exactly that.. with the "AS" portion. You need to *not* define the fields in the ref cursor. The cursor needs to be in a package, and you need to refer to the package in your parameter declaration.. in your case something like:

PROCEDURE tfna (
in cursor IN OUT Package.cursor,
in_begin_dt IN VARCHAR2,
in_end_dt IN VARCHAR2,
in_rc_code IN VARCHAR2,
in_company IN VARCHAR2,
in_state IN VARCHAR2,
in_mgt_level IN VARCHAR2,
in_job_title IN VARCHAR2,
in_crse1 IN VARCHAR2);

In my set up of my package, the cursor is Report_type and the package is Report_Package.

When you do this, you will get the field names from the SQL, in which you *DO* give it names as in your SQL snippet....


Lisa