Home » SQL & PL/SQL » SQL & PL/SQL » delete with variables
delete with variables [message #245172] Fri, 15 June 2007 06:52 Go to next message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
Hi ,
I need to run a delete query within a pl/sql program.Some of the values in the where clause are variables.I cannot hardcode the values for them.Can anyone help me as to how to do it?.
Iam giving the query below :
DELETE FROM WPHDS_DS_DLVY_SOLDOUT_1 A    
    WHERE EXISTS
         (SELECT B.COMPANY_CD 
                ,B.STR_CD
                ,B.OBJ_DATE
            FROM WPHDW_DW_DLVY_SOLDOUT_30 B 
            WHERE A.COMPANY_CD = B.COMPANY_CD
              AND A.STR_CD     = B.STR_CD
              AND A.OBJ_DATE   = B.OBJ_DATE
              AND A.REP_SKU_CD = B.REP_SKU_CD
              AND A.FIRST_VALD_ST_DATE = B.FIRST_VALD_ST_DATE
              AND B.OBJ_DATE >= Wd_MDFY_ST_DATE
              AND B.OBJ_DATE <= Wd_TODAY
         )
      AND A.OBJ_DATE >= Wd_MDFY_ST_DATE
      AND A.OBJ_DATE <= Wd_TODAY  ;

Here Wd_MDFY_ST_DATE & Wd_TODAY are variables.
Thanks in advance.
Re: delete with variables [message #245174 is a reply to message #245172] Fri, 15 June 2007 06:53 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Write a stored procedure and pass them as parameters ?
Re: delete with variables [message #245177 is a reply to message #245172] Fri, 15 June 2007 07:04 Go to previous messageGo to next message
kumarn
Messages: 13
Registered: June 2007
Location: London
Junior Member
Use EXECUTE IMMEDIATE for executing the dynamic query.

e.g.

declare
v_query  VARCHAR2(<value sufficient to accomodate the query>);

begin

Wd_MDFY_ST_DATE := '<value>';
Wd_TODAY   := '<value>';

v_query :='DELETE FROM WPHDS_DS_DLVY_SOLDOUT_1 A    
    WHERE EXISTS
         (SELECT B.COMPANY_CD 
                ,B.STR_CD
                ,B.OBJ_DATE
            FROM WPHDW_DW_DLVY_SOLDOUT_30 B 
            WHERE A.COMPANY_CD = B.COMPANY_CD
              AND A.STR_CD     = B.STR_CD
              AND A.OBJ_DATE   = B.OBJ_DATE
              AND A.REP_SKU_CD = B.REP_SKU_CD
              AND A.FIRST_VALD_ST_DATE = B.FIRST_VALD_ST_DATE
              AND B.OBJ_DATE >='||Wd_MDFY_ST_DATE||'AND B.OBJ_DATE <='||Wd_TODAY||' )
      AND A.OBJ_DATE >='||Wd_MDFY_ST_DATE
||'AND A.OBJ_DATE <= '||Wd_TODAY;
;

Execute immediate v_query;

end;

Hope it will work.

regards,
Kumar

Re: delete with variables [message #245179 is a reply to message #245177] Fri, 15 June 2007 07:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd rather not use dynamic sql.

MHE
Re: delete with variables [message #245181 is a reply to message #245172] Fri, 15 June 2007 07:13 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
How are you running this ? Is it in SQL*Plus ? If so you could use substitution variables:

undefine Wd_MDFY_ST_DATE
undefine Wd_TODAY

DELETE FROM WPHDS_DS_DLVY_SOLDOUT_1 A    
    WHERE EXISTS
         (SELECT B.COMPANY_CD 
                ,B.STR_CD
                ,B.OBJ_DATE
            FROM WPHDW_DW_DLVY_SOLDOUT_30 B 
            WHERE A.COMPANY_CD = B.COMPANY_CD
              AND A.STR_CD     = B.STR_CD
              AND A.OBJ_DATE   = B.OBJ_DATE
              AND A.REP_SKU_CD = B.REP_SKU_CD
              AND A.FIRST_VALD_ST_DATE = B.FIRST_VALD_ST_DATE
              AND B.OBJ_DATE >= &&Wd_MDFY_ST_DATE
              AND B.OBJ_DATE <= &&Wd_TODAY
         )
      AND A.OBJ_DATE >= &&Wd_MDFY_ST_DATE
      AND A.OBJ_DATE <= &&Wd_TODAY  ;
Re: delete with variables [message #245183 is a reply to message #245177] Fri, 15 June 2007 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Kumar, Cthulhu

DON'T DO THAT YOU WILL KILL YOUR SGA.

USE BIND VARIABLES.

Regards
Michel
Re: delete with variables [message #245187 is a reply to message #245181] Fri, 15 June 2007 07:24 Go to previous messageGo to next message
ravi.kumar
Messages: 1
Registered: June 2007
Location: Bangalore
Junior Member
iam not running it in sqlplus.its a scheduled job.
Re: delete with variables [message #245193 is a reply to message #245187] Fri, 15 June 2007 07:31 Go to previous messageGo to next message
kumarn
Messages: 13
Registered: June 2007
Location: London
Junior Member

Maheer, Michel,

Is there any problem in executing dynamic SQL in a PL/SQL program ?

I suggested dynamic sql because I am using it in my application.

What are the problems ?? Please clarify...
Re: delete with variables [message #245199 is a reply to message #245193] Fri, 15 June 2007 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any problem in executing dynamic SQL in a PL/SQL program ?

It is not validated at compile time.
It generates extra-parsing.

Moreover, your dynamic SQL don't use bind variables so it generates hard-parsing, waste SGA space, generates extra-latches, increase the time latches are taken, kills your CPU, kills your instance, kills your server...

Regards
Michel

[Updated on: Sun, 17 June 2007 01:33]

Report message to a moderator

Re: delete with variables [message #245280 is a reply to message #245172] Fri, 15 June 2007 13:48 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
How do you determine the variables in your scheduled job? Are they going to be passed as command line arguments? Calculated in a program? Read from a table? Depending on your answer, the way to do this changes.
Re: delete with variables [message #245427 is a reply to message #245280] Sun, 17 June 2007 01:24 Go to previous message
janardhanam.k
Messages: 29
Registered: April 2007
Junior Member
This delete is part of a pl/sql code in which the values of the variables are read from a table.i dont want to use dynamic sql.
Previous Topic: synonym
Next Topic: procedure
Goto Forum:
  


Current Time: Wed Dec 07 18:19:28 CST 2016

Total time taken to generate the page: 0.05284 seconds