Oracle Stored Procedure

[hsandwick]

Crystal 2008
Oracle 9
SAP BOXI3.1

Test Oracle Stored Procedure below.
Appears to run successfully created in Toad, in HSANDWICK schema.

(1) Cannot access in Crystal Data Source Expert. Is the procedure correct and any idea why it can't be accessed in Crystal?
(2) This is just the first test. Next, the report has subreports - how are they handled in Oracle Stored Procedures?
(3) Parameters - any special treatment in Oracle Stored Procedures?
(4) Report, once successful will reside in and be dynamically accessed in BOXI3.1. Any issues there?
(4) Any other specifics I should know about regarding Oracle SPs and Crystal 2008?

Thank you!


--Step 1.
CREATE OR REPLACE PROCEDURE HSANDWICK.PRC_WORKFORCEMAIN
(
pv_as_of_date_i IN DATE, pv_as_of_date2_i IN DATE,
pv_opdiv_i IN VARCHAR2(8),
ptyp_workforce_main_tab_o OUT NOCOPY HSANDWICK.WORKFORCE_MAIN_TAB,

ERROR_CODE OUT NOCOPY NUMBER
)
IS
CURSOR workforce_cursor
IS
SELECT
PDRMF_ALL.PAY_PLAN_CD,
PDRMF_ALL.TENURE_CD,
PDRMF_ALL.WORK_SCHEDULE_CD,
PDRMF_ALL.AS_OF_DATE,
PDRMF_ALL.SEX_CD,
PDRMF_ALL.SSN,
PDRMF_ALL.NOA_CD,
PDRMF_ALL.RACE_NATL_ORIGIN_CD,
PDRMF_ALL.VETERANS_PREFERENCE_CD,
PDRMF_ALL.HANDICAP_CD,
PDRMF_ALL.EDUCATION_LEVEL_CD,
PDRMF_ALL.SUPERVSRY_STATUS_CD,
PDRMF_ALL.FED_CIVLN_EMPMT_113A_CD,
PDRMF_ALL.BIRTH_DTE,
SAC_HIST_EXTENDED.OPDIV,
SAC_HIST_EXTENDED.STAFFDIV,
SAC_HIST_EXTENDED.ORG_CD,
SAC_HIST_EXTENDED.STAFFDIV_NAME,
SAC_HIST_EXTENDED.OPDIV_NAME,
SAC_HIST_EXTENDED.ORG_TITLE
FROM
HISTDBA.SAC_HIST_EXTENDED SAC_HIST_EXTENDED ,
HISTDBA.PDRMF_ALL PDRMF_ALL
WHERE
SAC_HIST_EXTENDED.ORG_CD = PDRMF_ALL.ORGTNL_COMPONENT_CD and
(PDRMF_ALL.AS_OF_DATE >= pv_as_of_date_i AND
PDRMF_ALL.AS_OF_DATE < pv_as_of_date2_i) AND
SAC_HIST_EXTENDED.OPDIV= pv_opdiv_i AND
((PDRMF_ALL.PAY_PLAN_CD='XX' OR
PDRMF_ALL.PAY_PLAN_CD='YY') OR
(PDRMF_ALL.TENURE_CD='1' OR
PDRMF_ALL.TENURE_CD='2')) AND
--below x is generic
(PDRMF_ALL.FED_CIVLN_EMPMT_113A_CD='[x]' OR
PDRMF_ALL.FED_CIVLN_EMPMT_113A_CD='[xx]' OR
PDRMF_ALL.FED_CIVLN_EMPMT_113A_CD='[xxx]' OR
PDRMF_ALL.FED_CIVLN_EMPMT_113A_CD='[xxxx]')
ORDER BY
SAC_HIST_EXTENDED.OPDIV,
SAC_HIST_EXTENDED.STAFFDIV,
SAC_HIST_EXTENDED.ORG_CD;

BEGIN
ERROR_CODE := 0;
ptyp_workforce_main_tab_o := HSANDWICK.WORKFORCE_MAIN_TAB ();

FOR workkforce_buffer IN workforce_cursor
LOOP
ptyp_workforce_main_tab_o.EXTEND (1);
ptyp_workforce_main_tab_o (ptyp_workforce_main_tab_o.COUNT) :=
WORKFORCE_MAIN_SCLR(workforce_buffer.PAY_PLAN_CD,
workforce_buffer.TENURE_CD,
workforce_buffer.WORK_SCHEDULE_CD,
workforce_buffer.AS_OF_DATE,
workforce_buffer.SEX_CD,
workforce_buffer.SSN,
workforce_buffer.NOA_CD,
workforce_buffer.RACE_NATL_ORIGIN_CD,
workforce_buffer.VETERANS_PREFERENCE_CD,
workforce_buffer.HANDICAP_CD,
workforce_buffer.EDUCATION_LEVEL_CD,
workforce_buffer.SUPERVSRY_STATUS_CD,
workforce_buffer.FED_CIVLN_EMPMT_113A_CD,
workforce_buffer.BIRTH_DTE,
workforce_buffer.OPDIV,
workforce_buffer.STAFFDIV,
workforce_buffer.ORG_CD,
workforce_buffer.STAFFDIV_NAME,
workforce_buffer.OPDIV_NAME,
workforce_buffer.ORG_TITLE
);
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND
THEN
ERROR_CODE := 0;
WHEN OTHERS
THEN
ERROR_CODE := -2;
END PRC_WORKFORCEMAIN;
SHOW ERRORS;


Step 2.
DROP TYPE HSANDWICK.WORKFORCE_MAIN_TAB
/
DROP TYPE HSANDWICK.WORKFORCE_MAIN_SCLR
/
CREATE OR REPLACE TYPE HSANDWICK.WORKFORCE_MAIN_SCLR AS OBJECT
(
PAY_PLAN_CD VARCHAR2(2),
TENURE_CD VARCHAR2(2),
WORK_SCHEDULE_CD VARCHAR2(2),
AS_OF_DATE DATE,
SEX_CD VARCHAR2(1),
SSN VARCHAR2(20),
NOA_CD VARCHAR2(3),
RACE_NATL_ORIGIN_CD VARCHAR2(2),
VETERANS_PREFERENCE_CD VARCHAR2(2),
HANDICAP_CD VARCHAR2(2),
EDUCATION_LEVEL_CD VARCHAR2(2),
SUPERVSRY_STATUS_CD VARCHAR2(1),
FED_CIVLN_EMPMT_113A_CD VARCHAR2(1),
BIRTH_DTE DATE,
OPDIV VARCHAR2(8),
STAFFDIV VARCHAR2(4),
ORG_CD VARCHAR2(15),
STAFFDIV_NAME VARCHAR2(72),
OPDIV_NAME VARCHAR2(72),
ORG_TITLE VARCHAR2(72)
)
/
CREATE OR REPLACE TYPE HSANDWICK.WORKFORCE_MAIN_TAB AS TABLE OF HSANDWICK.WORKFORCE_MAIN_SCLR
/

===========
Results for step 2:
Type dropped.
Type dropped.
Type created.
Type created.

--Step 3.
DECLARE
PV_AS_OF_DATE_I DATE;
PV_AS_OF_DATE2_I DATE;
pv_opdiv_i VARCHAR2(8);
ptyp_workforce_main_tab_o HSANDWICK.WORKFORCE_MAIN_TAB;
ERROR_CODE NUMBER;
BEGIN
dbms_output.enable(9999999);
--dates below generic
PV_AS_OF_DATE_I := '01-JUL-[1900]';
PV_AS_OF_DATE2_I := '30-SEP-[1900]';
pv_opdiv_i := 'ATSDR';
DBMS_OUTPUT.put_line ('Datetime started ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY:hh:mi:ss'));
ptyp_workforce_main_tab_o := HSANDWICK.WORKFORCE_MAIN_TAB ();
ERROR_CODE := NULL;
HSANDWICK.PRC_WORKFORCEMAIN ( pv_as_of_date_i, pv_as_of_date2_i, pv_opdiv_i, ptyp_workforce_main_tab_o, ERROR_CODE );
dbms_output.put_line('rtn cd: ' || ERROR_CODE);
dbms_output.put_line('count : ' || ptyp_workforce_main_tab_o.COUNT());

FOR cur_buf IN 1 ..ptyp_workforce_main_tab_o.COUNT() LOOP
dbms_output.put_line(ptyp_workforce_main_tab_o(cur_buf).PAY_PLAN_CD
|| ' -- ' || ptyp_workforce_main_tab_o(cur_buf).TENURE_CD
|| ' -- ' || ptyp_workforce_main_tab_o(cur_buf).WORK_SCHEDULE_CD

|| ' -- ' || ptyp_workforce_main_tab_o(cur_buf).AS_OF_DATE
|| ' -- ' || ptyp_workforce_main_tab_o(cur_buf).OPDIV
|| ' --End ' );
END LOOP;

DBMS_OUTPUT.put_line ('Datetime Ended ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY:hh:mi:ss'));
END;

 

[Turkbear]

Hi,
As a start, for an Oracle SP to be recognized by Crystal ( or used ) it MUST return a REF CURSOR ( CR then sees it as a dataset, sort of).

Also your SHOW ERRORS and DBMS_OUTPUT will have no effect since you are not in an interactive Oracle environment.

May be time to hit the docs on using CR with Oracle.

http://www.scribd.com/doc/2069216/Crystal-Reports-oracle-stored-procedures

http://www.sdn.sap.com/irj/boc/go/p...a60745253?QuickLink=index&overridelayout=true

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

 

[hsandwick]

Thanks so much, Turkbear!

Exactly what was needed.

These don't mention Crystal 2008. I'm assuming the same applies ... ???

Best,
Hsandwick

 

[Turkbear]

Hi,
Glad to help..
Don't know much about the 2008 version but I expect the same would apply.




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

 

[hsandwick]

Excellent.
Thanks again!