Home » SQL & PL/SQL » SQL & PL/SQL » Please help me on how to pass date condition Dynamically. (Oracle 11g)
Please help me on how to pass date condition Dynamically. [message #573360] Thu, 27 December 2012 05:18 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I have the following control table.

ID S_OWNER  SOURCE_TABLE     A_OWNER    ARC_TABLE             CONDITION_COLUMN     PERIOD_VALUE   PERIOD_UNIT

1    wedb     Auction_table    wedb     Arc_Auction_table	Auction_date	       15               Days
1    wedb     Sales_table      wedb     ArcSales_table	        Sales_date	       180              Days
1    hr       Accounts_table   hr       Arc_Accounts_table	Account_date	        2	        Years
2    concor   Concur_table     con      Arc_Concur_table        Last_update_date        4               Months

Like this 1000 entries are there in the control_table.

I want to pass all the columns from my control table dynamically.
I am able to pass all the columns dynamically,except the PERIOD_VALUE.
I have stucked up how to implement this condition.

My aim is to get the data which is <= sysdate-PERIOD_VALUES based on PERIOD_UNIT.

For Example:

For Auction_date column I want to get the data which 15 days old.
For Sales_date column I want to get the data which 180 days old.
For Account_date column I want to get the data which 2 yers old.
For Last_update_date column I want to get the data which 4 months old.

The conditions something like this.
condition_column<=(sysdate-15 days)
condition_column<=(sysdate-180 days)
condition_column<=(sysdate-2 years)
condition_column<=(sysdate-4 months)


This is my procedure.
CREATE OR REPLACE PROCEDURE WEDB.procedure_control(
   P_ID IN NUMBER)
IS
   CURSOR C
   IS
        SELECT ID,S_OWNER,SOURCE_TABLE,A_OWNER,ARC_TABLE,CONDITION_COLUMN,PERIOD_VALUE,PERIOD_UNIT
          FROM wedb.CONTROL
         WHERE ID = p_id
      ORDER BY ID, SOURCE_TABLE;
      rec C%ROWTYPE;
BEGIN
   FOR I IN C
   LOOP
    EXECUTE IMMEDIATE
               'INSERT INTO '
            || rec.A_OWNER
            || '.'
            || rec.ARC_TABLE
            || '(SELECT * FROM '
            || rec.S_OWNER
            || '.'
            || rec.SOURCE_TABLE
            || ' WHERE '
            || rec.CONDITION_COLUMN
            || '<=I want to pass dynamic condition'
            || ')';

         EXECUTE IMMEDIATE
               'DELETE FROM '
            || rec.S_OWNER
            || '.'
            || rec.SOURCE_TABLE
            || ' WHERE '
            || rec.CONDITION_COLUMN
            || '<= I want to pass dynamic condition'
            || '';

      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);
END procedure_control;
/

Please help me how to implement this.

Thanks in advance.


Re: Please help me on how to pass date condition Dynamically. [message #573363 is a reply to message #573360] Thu, 27 December 2012 05:34 Go to previous messageGo to next message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Wouldn't CASE (or DECODE) help here? For example:
case when period_unit = 'Days'   then sysdate - period_value
     when period_unit = 'Months' then add_months(sysdate, -period_value)
     when period_unit = 'Years'  then add_months(sysdate, -period_value * 12)
end

Re: Please help me on how to pass date condition Dynamically. [message #573366 is a reply to message #573360] Thu, 27 December 2012 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And still your stupid:
Quote:
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);

But not a surprise, the whole design is stupid why not the code?

Regards
Michel
Re: Please help me on how to pass date condition Dynamically. [message #573398 is a reply to message #573366] Thu, 27 December 2012 23:12 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am not able to understand what you said,is it regarding exception block or my whole code.
Please explain it.
In future any table is requested for archive ,how can we handle it dynamically.
Please provide some sample code.
Re: Please help me on how to pass date condition Dynamically. [message #573399 is a reply to message #573398] Thu, 27 December 2012 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
>Please help me how to implement this.
How do I teach my goat to fly?
Re: Please help me on how to pass date condition Dynamically. [message #573400 is a reply to message #573360] Thu, 27 December 2012 23:55 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply.
How ever I am getting some error.

select distinct retention_unit from APPS_GLOBAL.control_table1
where group_id  = 1;


The values in the control_table are d,y

I am getting the error.

ORA-00903: invalid table name
ORA-06512: at line 21


This is the code I am executing.

DECLARE
   CURSOR C
   IS
        SELECT GROUP_ID,
               SOURCE_TABLE_OWNER_NAME,
               SOURCE_TABLE_NAME,
               ARCHIVE_TABLE_OWNER_NAME,
               ARCHIVE_TABLE_NAME,
               CONDITION_COLUMN_NAME,
               RETENTION_VALUE,
               RETENTION_UNIT,
               FLAG
          FROM APPS_GLOBAL.control_table1
         WHERE GROUP_ID = 1
      ORDER BY GROUP_ID, source_table_name;

   rec   c%ROWTYPE;
BEGIN
   FOR rec IN C
   LOOP
      EXECUTE IMMEDIATE
            'INSERT INTO '
         || rec.ARCHIVE_TABLE_OWNER_NAME
         || '.'
         || rec.ARCHIVE_TABLE_NAME
         || '(SELECT * FROM '
         || rec.SOURCE_TABLE_OWNER_NAME
         || '.'
         || rec.SOURCE_TABLE_NAME
         || ' WHERE '
         || rec.CONDITION_COLUMN_NAME
         || '<='
         || CASE WHEN LOWER (rec.RETENTION_UNIT) = 'd' THEN 'sysdate - ' || rec.RETENTION_VALUE
               WHEN LOWER (rec.RETENTION_UNIT) = 'y'  THEN 'add_months(sysdate,' || rec.RETENTION_VALUE || '*-12)'
            END
         ||')';
   END LOOP;

   COMMIT;
END;
/


Please help me.

Thanks
Re: Please help me on how to pass date condition Dynamically. [message #573401 is a reply to message #573400] Fri, 28 December 2012 00:00 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am providing DDL and insert statetment for my control table.

CREATE TABLE APPS_GLOBAL.CONTROL_TABLE1
(
  GROUP_ID                  NUMBER,
  SOURCE_TABLE_OWNER_NAME   VARCHAR2(50 CHAR),
  SOURCE_TABLE_NAME         VARCHAR2(50 CHAR),
  ARCHIVE_TABLE_OWNER_NAME  VARCHAR2(50 CHAR),
  ARCHIVE_TABLE_NAME        VARCHAR2(50 CHAR),
  CONDITION_COLUMN_NAME     VARCHAR2(50 CHAR),
  RETENTION_VALUE           NUMBER,
  RETENTION_UNIT            CHAR(1 CHAR),
  FLAG                      CHAR(1 CHAR)
);


Insert into APPS_GLOBAL.CONTROL_TABLE1
   (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, ARCHIVE_TABLE_NAME, 
    CONDITION_COLUMN_NAME, RETENTION_VALUE, RETENTION_UNIT, FLAG)
 Values
   (1, 'APPS_GLOBAL', 'EMP', 'APPS_GLOBAL', 'A_RAM', 
    'HIREDATE', 1, 'Y', 'A');

Insert into APPS_GLOBAL.CONTROL_TABLE1
   (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, ARCHIVE_TABLE_NAME, 
    CONDITION_COLUMN_NAME, RETENTION_VALUE, RETENTION_UNIT, FLAG)
 Values
   (1, 'APPS_GLOBAL', 'DOJ_RAM', 'APPS_GLOBAL', 'B_RAM', 
    'DOJ', 180, 'D', 'A');

Insert into APPS_GLOBAL.CONTROL_TABLE1
   (GROUP_ID, SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME, CONDITION_COLUMN_NAME, 
    RETENTION_VALUE, RETENTION_UNIT, FLAG)
 Values
   (1, 'APPS_GLOBAL', 'STUUDENT_RAM', 'APPS_GLOBAL', 'STUD_DOJ', 
    2, 'Y', 'P');


Re: Please help me on how to pass date condition Dynamically. [message #573404 is a reply to message #573401] Fri, 28 December 2012 02:18 Go to previous messageGo to next message
flyboy
Messages: 1780
Registered: November 2006
Senior Member
Have you really run that code with data you provided? If so, did it really fail with ORA-00903 error in the dynamic statement?
I did not as I do not have schemas/tables used in that dynamic query, so it would fail with "ORA-00904: Table or view does not exist".
The code with provided data looks fine at the first look though.
So, as only you have relevant environment, standard way of debugging dynamic code is writing its content, examining it for evident errors; if nothing is found, run it separately and check its output.
As you decided to abuse dynamic code, you should start doing it.

Also, the error message is very straightforward - at least one of columns SOURCE_TABLE_OWNER_NAME, SOURCE_TABLE_NAME, ARCHIVE_TABLE_OWNER_NAME or ARCHIVE_TABLE_NAME contains value which is not a nonquoted identifier, as described in SQL Language Reference.
For 11gR2, it is placed here: http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27570

Full description of the error you posted is also available here: http://ora-00903.ora-code.com/
Quote:
ORA-00903: invalid table name
Cause: A table or cluster name is invalid or does not exist. This message is also issued if an invalid cluster name or no cluster name is specified in an ALTER CLUSTER or DROP CLUSTER statement.
Action: Check spelling. A valid table name or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #. The name must be less than or equal to 30 characters and cannot be a reserved word.

By the way, all those columns have greater length than 30 characters. Why?
Re: Please help me on how to pass date condition Dynamically. [message #573408 is a reply to message #573404] Fri, 28 December 2012 03:18 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Sorry your insert code look like this
INSERT INTO APPS_GLOBAL.(SELECT * FROM APPS_GLOBAL.STUUDENT_RAM WHERE STUD_DOJ<=add_months(sysdate,2*-12))

INSERT INTO APPS_GLOBAL.B_RAM(SELECT * FROM APPS_GLOBAL.DOJ_RAM WHERE DOJ<=sysdate - 180)

INSERT INTO APPS_GLOBAL.A_RAM(SELECT * FROM APPS_GLOBAL.EMP WHERE HIREDATE<=add_months(sysdate,1*-12)) 

Re: Please help me on how to pass date condition Dynamically. [message #573422 is a reply to message #573408] Fri, 28 December 2012 05:43 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have added ||')' paranthasis for each CASE stament to avoiid the error.
Now I am getting the below error
ORA-00903: invalid table name
ORA-06512: at line 42

I am using below code.

Please find my below code.

DECLARE
   stmt   LONG;

   CURSOR C
   IS
        SELECT GROUP_ID,
               SOURCE_TABLE_OWNER_NAME,
               SOURCE_TABLE_NAME,
               ARCHIVE_TABLE_OWNER_NAME,
               ARCHIVE_TABLE_NAME,
               CONDITION_COLUMN_NAME,
               RETENTION_VALUE,
               RETENTION_UNIT,
               FLAG
          FROM APPS_GLOBAL.control_table1
         WHERE GROUP_ID = 1
      ORDER BY GROUP_ID, source_table_name;

   rec    c%ROWTYPE;
BEGIN
   FOR rec IN C
   LOOP
stmt := 'INSERT INTO '
         || rec.ARCHIVE_TABLE_OWNER_NAME
         || '.'
         || rec.ARCHIVE_TABLE_NAME
         || '(SELECT * FROM '
         || rec.SOURCE_TABLE_OWNER_NAME
         || '.'
         || rec.SOURCE_TABLE_NAME
         || ' WHERE '
         || rec.CONDITION_COLUMN_NAME
         || '<=';
 
CASE LOWER (rec.RETENTION_UNIT)
    WHEN 'd'
    THEN
     stmt:= stmt||'sysdate -'||rec.RETENTION_VALUE||')';
     ELSE
     stmt := stmt||'add_months(sysdate,'|| rec.RETENTION_VALUE||'*-12)'||')';
END CASE;
execute immediate stmt;
   END LOOP;

   COMMIT;
END;
/


The values for the field RETENTION_UNIT in my control_table1 are D and Y.

Before making the date comparison condition it was working fine.
If I made it as dynamic why I am getting the error.
If I use condition as below it's working fine.

EXECUTE IMMEDIATE
               'INSERT INTO '
            || I.ARCHIVE_TABLE_OWNER_NAME
            || '.'
            || I.ARCHIVE_TABLE_NAME
            || '(SELECT * FROM '
            || I.SOURCE_TABLE_OWNER_NAME
            || '.'
            || I.SOURCE_TABLE_NAME
            || ' WHERE '
            || I.CONDITION_COLUMN_NAME
            || '<=ADD_MONTHS(SYSDATE,-24)'
            || ')';


Please help me .
Thanks.
Re: Please help me on how to pass date condition Dynamically. [message #573423 is a reply to message #573422] Fri, 28 December 2012 06:03 Go to previous messageGo to next message
Littlefoot
Messages: 19893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't execute it; display variable's contents (using DBMS_OUTPUT.PUT_LINE) so that you'd see what you are trying to execute.
Re: Please help me on how to pass date condition Dynamically. [message #573430 is a reply to message #573422] Fri, 28 December 2012 06:38 Go to previous messageGo to next message
flyboy
Messages: 1780
Registered: November 2006
Senior Member
Maybe you ran it with different data, did not you?

The error you are facing now is not caused by the where condition. It is what the message clearly says - invalid table name in FROM clause.

As mularikri correctly spotted, there is missing value of ARCHIVE_TABLE_NAME in the last insert statement, so the generated INSERT is invalid.
INSERT INTO APPS_GLOBAL.(SELECT * FROM APPS_GLOBAL.STUUDENT_RAM WHERE STUD_DOJ<=add_months(sysdate,2*-12))

Is not something missing here after the first dot?

What about fixing your data?
Good luck.
Re: Please help me on how to pass date condition Dynamically. [message #573482 is a reply to message #573422] Sat, 29 December 2012 07:35 Go to previous message
flyboy
Messages: 1780
Registered: November 2006
Senior Member
It is good you posted a feedback. Unfortunately, you chose the wrong forum: https://forums.oracle.com/forums/thread.jspa?threadID=2480317&start=15&tstart=0
Additionally, on OTN forum you did not mark your problem as solved nor gave any points for helpful/correct answer yet (December, 29th, 13:30 GMT).
So it seems to me you are now capable of solving problems on your own and you are no longer depending on help from forums.
Congratulations!
Previous Topic: Associative array - no data found
Next Topic: ORA-29283: invalid file operation
Goto Forum:
  


Current Time: Mon Dec 22 16:12:11 CST 2014

Total time taken to generate the page: 0.11268 seconds