I am calling a stored procedure through free hand sql that is returning 38 rows of data which are incorrect. When I call the stored procedure it returns only 10 rows of data which is correct. Any thoughts would be appreciated.
Thanks!
ipuffer (BOB member since 2002-12-16)
If you attach the script of the stored procedure, we can help you easily.
By the way, the only thing I’m thinking is “Avoid aggregation of duplicate rows”…
Christian Konrads 
(BOB member since 2004-07-21)
Perhaps the stored procedure uses “union all” instead of “union”, or viceversa…
It depends on how your procedure is written.
Christian Konrads (BOB member since 2004-07-21)
Understanding that this is the first time I am attempting to use a stored procedure as a data source in BusObj I will share our script with you. 
Remember it is working perfect in SQLPlus.
Thanks in advance!
CREATE OR REPLACE PROCEDURE insert_icis_pipeline1
(pipeline_report_rc IN OUT icis_ii.curtyp
,p_column1 NUMBER DEFAULT 0
,p_value1 NUMBER DEFAULT 0
,p_column2 VARCHAR2 DEFAULT '0'
,p_value2 VARCHAR2 DEFAULT '0'
,p_column3 VARCHAR2 DEFAULT '0'
,p_value3 VARCHAR2 DEFAULT '0'
,p_column4 VARCHAR2 DEFAULT '0'
,p_value4 VARCHAR2 DEFAULT '0'
,p_date_column VARCHAR2 DEFAULT '0'
,p_date1 VARCHAR2 DEFAULT '0'
,p_date2 VARCHAR2 DEFAULT '0')
AS
v_stmt VARCHAR2(32000);
v_add_clause VARCHAR2(32000) :=' ';
BEGIN
IF p_column1 <> 0
THEN
v_add_clause := ' AND '||p_column1||' = '||p_value1;
END IF;
IF p_column2 <> '0'
THEN
v_add_clause := v_add_clause||CHR(10)||' AND '||p_column2||' = '''||UPPER(p_value2)||'''';
END IF;
IF p_column3 <> '0'
THEN
v_add_clause := v_add_clause||CHR(10)||' AND '||p_column3||' = '''||UPPER(p_value3)||'''';
END IF;
IF p_column4 <> '0'
THEN
v_add_clause := v_add_clause||CHR(10)||' AND '||p_column4||' = '''||UPPER(p_value4)||'''';
END IF;
IF p_date_column <> '0'
THEN
v_add_clause := v_add_clause||CHR(10)||' AND '||p_date_column
||' BETWEEN '||''''||UPPER(p_date1)||''''||' AND '||''''||UPPER(p_date1)||'''';
END IF;
v_stmt:=
'INSERT INTO icis_pipeline
SELECT icis_facility_frs.icis_facility_id
,icis_facility_frs.primary_name
,icis_facility_frs.location_address
,icis_facility_frs.city_name
,icis_facility_frs.state_code
,icis_facility_frs.pgm_sys_id
,v_pipeline.act_seg,
v_pipeline.statute_code||icis_facility_pgm.pgm_sys_id||decode(
v_pipeline.act_seg,0,v_pipeline.activity_id,v_pipeline.act_seg) segment
,icis_data.get_min_pipeline_start_date(v_pipeline.statute_code,
v_pipeline.act_seg,v_pipeline.activity_id) min_start_date
,icis_data.get_max_pipeline_close_date(v_pipeline.activity_id) max_pipeline_close_date
,v_pipeline.region_code
,v_pipeline.activity_id
,v_pipeline.statute_code
,v_pipeline.actual_begin_date
,v_pipeline.activity_type_code
,v_pipeline.actual_end_date
,icis_data.get_pipeline_actual_date(v_pipeline.activity_id,''INFRR'') infrr
,icis_data.get_pipeline_actual_date(v_pipeline.activity_id,''INSRC'') insrc
,icis_data.get_cmd_date(v_pipeline.activity_id) cmd_date
,icis_data.get_sfd_date(v_pipeline.activity_id) Disclosed_date
,icis_data.get_case_screen_date(v_pipeline.activity_id) case_screening_date
,DECODE(v_pipeline.activity_type_code, ''AIF'', xref_enf_type.enf_type_code,NULL) enf_type_code
,icis_data.get_court_enf_nmbr(v_pipeline.activity_id) court_enf_nmbr
,DECODE(v_pipeline.activity_type_code,''JDC'',
icis_data.get_actual_date(v_pipeline.activity_id,''DOJ'')
,icis_data.get_actual_date(v_pipeline.activity_id,''CPOA'')) ea_issued_date
,DECODE(v_pipeline.activity_type_code,''AFR'',
icis_data.get_actual_date(v_pipeline.activity_id,''CAFO'')
,icis_data.get_actual_date(v_pipeline.activity_id,''FOE'')) ea_concluded_date
,icis_data.get_sum_penalty_required_amt(v_pipeline.activity_id,
v_pipeline.statute_code,icis_facility_pgm.icis_facility_id) sum_penalty_required_amt
,DECODE(v_pipeline.activity_type_code,''AFR'',
icis_data.get_pipeline_flag(v_pipeline.activity_id,
v_pipeline.icis_facility_id,v_pipeline.statute_code,''SEP_ID'')
,''JDC'', icis_data.get_pipeline_flag(v_pipeline.activity_id,
v_pipeline.icis_facility_id,v_pipeline.statute_code,''SEP_ID'')
,NULL) sep_flag
,DECODE(v_pipeline.activity_type_code,''AFR'',
icis_data.get_pipeline_flag(v_pipeline.activity_id,
v_pipeline.icis_facility_id,v_pipeline.statute_code,''COMP_ACTION_ID''),
''JDC'', icis_data.get_pipeline_flag(v_pipeline.activity_id,
v_pipeline.icis_facility_id,v_pipeline.statute_code,''COMP_ACTION_ID'')
,NULL) comp_action_flag
,DECODE(v_pipeline.activity_type_code,''AFR'',
icis_data.get_pipeline_flag(v_pipeline.activity_id,
v_pipeline.icis_facility_id,v_pipeline.statute_code,''POLLUTANT_CODE'')
,''JDC'',icis_data.get_pipeline_flag(v_pipeline.activity_id,
v_pipeline.icis_facility_id,v_pipeline.statute_code,''POLLUTANT_CODE'')
,NULL) pollutant_code_flag
FROM icis_facility icis_facility_frs
,icis_facility icis_facility_pgm
,xref_enf_type
,v_pipeline
WHERE v_pipeline.icis_facility_id = icis_facility_pgm.icis_facility_id
AND ((( icis_facility_pgm.facility_uin =icis_facility_frs.facility_uin
AND icis_facility_frs.pgm_sys_acrnm = ''FRS'' )
OR ( icis_facility_pgm.icis_facility_id = icis_facility_frs.icis_facility_id
AND icis_facility_pgm.facility_uin IS NULL
AND icis_facility_pgm.pgm_sys_acrnm = ''ICIS'')))
AND v_pipeline.activity_id = xref_enf_type.activity_id (+)';
v_stmt:= v_stmt||CHR(10)||v_add_clause;
COMMIT;
EXECUTE IMMEDIATE v_stmt;
IF p_date_column <> '0'
THEN
v_stmt := 'DELETE FROM icis_pipeline '||
'WHERE '||p_date_column||' < '''||p_date1||
''' OR '||p_date_column||' > '''||p_date2||
''' OR '||p_date_column||' IS NULL';
EXECUTE IMMEDIATE v_stmt;
END IF;
DECLARE
CURSOR cur_find_act_seg IS
SELECT DISTINCT act_seg
FROM icis_pipeline
WHERE activity_type_code = 'CMD'
AND statute_code IS NULL
AND act_seg <> 0;
BEGIN
FOR rec_find_act_seg IN cur_find_act_seg
LOOP
DECLARE
CURSOR cur_find_cmd IS
SELECT *
FROM icis_pipeline
WHERE act_seg = rec_find_act_seg.act_seg
AND activity_type_code = 'CMD';
BEGIN
FOR rec_find_cmd IN cur_find_cmd
LOOP
DECLARE
CURSOR cur_fill_statute IS
SELECT statute_code
FROM icis_pipeline
WHERE act_seg = rec_find_cmd.act_seg
AND activity_type_code <> 'CMD';
v_count NUMBER := 0;
BEGIN
FOR rec_fill_statute IN cur_fill_statute
LOOP
IF v_count = 0
THEN
UPDATE icis_pipeline
SET statute_code = rec_fill_statute.statute_code
WHERE icis_facility_id = rec_find_cmd.icis_facility_id
AND act_seg = rec_find_cmd.act_seg
AND activity_type_code = 'CMD'
AND activity_id = rec_find_cmd.activity_id;
v_count := 1;
ELSE
INSERT INTO icis_pipeline
SELECT icis_facility_id
,primary_name
,location_address
,city_name
,state_code
,pgm_sys_id
,act_seg
,segment
,min_start_date
,max_pipeline_close_date
,region_code
,activity_id
,rec_fill_statute.statute_code
,actual_begin_date
,activity_type_code
,actual_end_date
,infrr
,insrc
,cmd_date
,disclosed_date
,case_screening_date
,enf_type_code
,court_enf_nmbr
,ea_issued_date
,ea_concluded_date
,sum_penalty_required_amt
,sep_flag
,comp_action_flag
,pollutant_code_flag
FROM icis_pipeline
WHERE icis_facility_id = rec_find_cmd.icis_facility_id
AND act_seg = rec_find_cmd.act_seg
AND activity_type_code = 'CMD'
AND activity_id = rec_find_cmd.activity_id
AND rownum < 2;
END IF;
END LOOP;
END;
END LOOP;
END;
END LOOP;
END;
OPEN pipeline_report_rc FOR
SELECT *
FROM icis_pipeline
ORDER BY act_seg;
END;
/ipuffer (BOB member since 2002-12-16)
You cannot commit in free hand SQL.
You cannot update in free hand SQL.
You cannot insert in free hand SQL.
Christian Konrads (BOB member since 2004-07-21)
My DBA was able to figure out the problem, thanks for your help.
ipuffer (BOB member since 2002-12-16)
Could you explain us, please?
Christian Konrads (BOB member since 2004-07-21)
Here is the actual sql I was using to call the procedure so we were not commiting/updating/inserting via free hand, but through the procedure.
begin
insert_icis_pipeline (:cbo, NULL,NULL,‘icis_facility_frs.pgm_sys_id’,‘110010781092’,‘v_pipeline.region_code’,‘08’,NULL,NULL,NULL,NULL,NULL);
end;
Since we have to use the ‘on commit preserve rows’ feature to get data returned, we didnt realize we also needed to truncate the table before we ran the procedure again. He included the trunc and it is now working.
ipuffer (BOB member since 2002-12-16)
It wasn’t so easy!!
Good! 
Christian Konrads (BOB member since 2004-07-21)