[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

Declare an array inside an Oracle package and use array values in a query

HI,

I have  a question.

How can I declare an array of dates inside a procedure in a package?

Inside this procedure PURGE_TABLES below, I want to declare an array containing max business dates and storing the results of this query -->

select max(business_date)
from DOC_DOCU_HIST
where business_date >= (select add_months( trunc(max(business_date), 'MM') , -P_PARAM_VALUE1) from DOC_DOCU_HIST) group by trunc(business_date, 'MM')

Once I store the results in an array, say BUS_DT_ARR, I want to pass those values in the query below -->

delete from DOC_DOCU_HIST c
where substr(to_char(c.RUN_DATE, 'YYYYMMDD'),0,10) < (select max(substr(to_char((c.run_date - P_PARAM_VALUE2), 'YYYYMMDD'),0,10)) from DOC_DOCU_HIST c) and c.BUSINESS_DATE not in (------I WANT TO PASS THE BUS_DT_ARR ARRAY VALUES HERE -----)

How do I do that?

I already declared P_PARAM_VALUE1 and P_PARAM_VALUE2 inside my package

Here is the package I have written till now -->

------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE DOC_TEMP_PKG AS


PROCEDURE PURGE_TABLES
(
P_PARAM_VALUE1 OUT PARAMS.PARAM_VALUE%TYPE,
P_PARAM_VALUE2 OUT PARAMS.PARAM_VALUE%TYPE,
);

END;
/

CREATE OR REPLACE PACKAGE BODY DOC_TEMP_PKG AS

PROCEDURE PURGE_TABLES (
P_PARAM_VALUE1 OUT RDW_PARAMS.PARAM_VALUE%TYPE,
P_PARAM_VALUE2 OUT RDW_PARAMS.PARAM_VALUE%TYPE
)

IS

BEGIN

SELECT PARAM_VALUE INTO P_PARAM_VALUE1
from PARAMS where PARAM_NAME = 'NUMBER_OF_MONTHENDS';

SELECT PARAM_VALUE INTO P_PARAM_VALUE2
from PARAMS where PARAM_NAME = 'NUMBER_OF_DAYS';



END;

END DOC;
/
0
sunny82
Asked:
sunny82
1 Solution
 
Amitkumar PSr. ConsultantCommented:
Can you share the sample data available in you table.

Also, provide the exact requirement, so that team can give you the proper solution to the problem.
0
 
sujith80Commented:
You dont really need an array there. You might just use an inline select

delete from DOC_DOCU_HIST c
where substr(to_char(c.RUN_DATE, 'YYYYMMDD'),0,10) < (select max(substr(to_char((c.run_date - P_PARAM_VALUE2), 'YYYYMMDD'),0,10))
from DOC_DOCU_HIST c) and c.BUSINESS_DATE not in (
    select max(business_date)
    from DOC_DOCU_HIST
    where business_date >= (select add_months( trunc(max(business_date), 'MM') , -P_PARAM_VALUE1) from DOC_DOCU_HIST)
    group by trunc(business_date, 'MM')
)


If you are looking for an example of implementing it using arrays, you might need to declare an object type of dates and use TABLE clause etc.
0
 
sunny82Author Commented:
@sujith80: inline select will work. But specifically looking how to use an array in this both in spec and body of package because the query is getting too big and complex, so looking for alternate solutions. The syntax and how to use it in this case will be greatly appreciated.
0
 
flow01Commented:
To make complexity smaller consider using for example a temporary table
create global temporary table SELECTED_BUSINESS_DATES(selected_date date)

However if you want to use an array from a package here is an example in the attached file
20120914.txt
0
 
sunny82Author Commented:
That helps. Thanks a lot.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now