Fetch multiple values in a single variable and use that to compare with a single value in another variable

Question

I have a requirement to fetch multiple values through a select query and into a single variable and then compare the value of that variable with another variable having only single value.

I am new to PL/SQL programming. I have searched this forum and got the results for using TYPE/ collections but not sure how to use the same in my code.

Below is a snippet of my code:

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  TYPE snap IS TABLE OF GDWARC_JPN.DIM_ORG_UNIT_HIST.snapshot_period%TYPE;
BEGIN 
  SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 
  SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap FROM GDWARC_JPN.DIM_ORG_UNIT_HIST; -- multiple values like DEC-2016, JAN-2016 etc . snapshot_period is date column 

  if arg1 in (snap ) then
    execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition arg1';

Answer 1

You can Use Member of clause to test if the element is a part of collection

    if some_value member of arg1 then 
`      dbms_output.put_line('Member') ;
    end if ;

The code posted by you have a few issues related to declaration and use of collection variable, following is a more correct version .

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  TYPE snap IS TABLE OF GDWARC_JPN.DIM_ORG_UNIT_HIST.snapshot_period%TYPE;
  snap_tab snap := snap() ; --declare a new collection variable and use this 
BEGIN 
  SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 
  SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap_tab/*changed*/ FROM GDWARC_JPN.DIM_ORG_UNIT_HIST; -- multiple values like DEC-2016, JAN-2016 etc . snapshot_period is date column 

  if arg1 member of snap_tab /*changed*/ then
    execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition arg1';

Answer 2

You can simply use a loop to check all the values returning from your query:

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
BEGIN 
  arg1 := to_char(sysdate,'MON-yyyy'); /* you do not need to trunc and/or a query here */
  --
  /* you can use a loop to scan all your values */
  for i in (
              SELECT (to_char(snapshot_period,'MON-yyyy')) val
              FROM GDWARC_JPN.DIM_ORG_UNIT_HIST
           )
  loop
      if i.val = arg1 then
        /* arg1 must be out of the fixed string */
        execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition ' || arg1;
      end if;
  end loop;
end;  

However you code seems to loop through a table, but you only truncate the partition corresponding to sysdate; so, your code can be simplified:

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  vCheck number;
BEGIN 
  arg1 := to_char(sysdate,'MON-yyyy'); /* you do not need to trunc and/or a query here */
  /* you only need to check whether a value for sysdate exists or not */
  select count(1)
  into vCheck
  from GDWARC_JPN.DIM_ORG_UNIT_HIST
    where to_char(snapshot_period,'MON-yyyy') = arg1;
  --
  if vCheck > 0 then
          /* arg1 must be out of the fixed string */
          execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition ' || arg1;
  end if;
end;  

Also, notice that you do not need a varchar2 to check your dates, but you can simply trunc them both to month and check the results of trunc and that you have an error in dynamic string, given that arg1 should be a variable value, not hardcoded into the string.

Answer 3

I'm unable to comment so I'll write new response

Thanks Prabhat, your code compiled fine but when i tried running the proc , it threw error as : Error report - ORA-01858: a non-numeric character was found where a numeric was expected ORA-06512: at "GDWARC_JPN.MNTH_SNAPSHOT", line 9 ORA-06512: at line 1 01858. 00000 - "a non-numeric character was found where a numeric was expected" Could you please help.

Your problem is that you are using different data types.

SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap_tab/*changed*/ FROM GDWARC_JPN.DIM_ORG_UNIT_HIST;

For start, you are using date, converting it to char and then saving it to date again. Don't know how can you even compile this

SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 

When you convert sysdate to your desired format and save it to varchar variable, it's not date anymore. Then you are comparing it with a date and because you have your format on the date, it's not recognized. So either use date in all variables and TRUNC option to extract only month and year from it (although I'm not sure you can cast members of table in member of, maybe you'll need to use for in that situation) or turn snap table to varchar type

EXAMPLE

CREATE OR REPLACE PROCEDURE MNTH_SNAPSHOT AS
  arg1 varchar2(10) ;
  TYPE snap IS TABLE OF varchar2(10);
  snap_tab snap := snap() ; --declare a new collection variable and use this 
BEGIN 
  SELECT (to_char(trunc(sysdate, 'MM'), 'MON-yyyy')) INTO arg1 FROM dual; 
  SELECT (to_char(snapshot_period,'MON-yyyy')) BULK collect into snap_tab/*changed*/ FROM GDWARC_JPN.DIM_ORG_UNIT_HIST; -- multiple values like DEC-2016, JAN-2016 etc . snapshot_period is date column 

  if arg1 member of snap_tab /*changed*/ then
    execute immediate 'alter table GDWARC_JPN.DIM_ORG_UNIT_HIST truncate partition arg1';