Home » SQL & PL/SQL » SQL & PL/SQL » how to use USING clause for dynamically appended where clauses (oracle 10g)
how to use USING clause for dynamically appended where clauses [message #442455] Mon, 08 February 2010 04:12 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

we are writing the procedure in the manned described above.but i am confused with the single and double quotes.when to put single and when to use double quotes while appending dynamically adding where conditions.


i think instead of this we can write USING clause.how to use USING clause for the above procedure?
procedure testprod(p_fdate        in Varchar2,
                   p_tdate        in varchar2,
                   p_type in varchar2) IS

  where_clause VARCHAR2(32567) := '';

BEGIN

  where_clause := where_clause || ' AND (trunc(t.invoice_date)  BETWEEN ''' ||
                  p_fdate || ''' AND ''' || p_tdate || ''')';

  where_clause := where_clause || ' AND T.STATUS IN (''' ||
                  upper(p_type) || ''')';

  execute immediate 'INSERT INTO GT_TEMP
      (PARTY,
       REFERENCE
     )
      (SELECT 
              DECODE(T.STATUS,
                     ''SALES'',
                     T.RECEIVER,
                     T.SENDER) PARTYID,
              T.REFERENCE
         FROM DOCUMENT      T
            
        WHERE T.CHARGE_STATUS IN (''INVOICED'', ''PARTLY_INVOICED'')
         ' || where_clause || ')';

END;
Re: how to use USING clause for dynamically appended where clauses [message #442473 is a reply to message #442455] Mon, 08 February 2010 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a very bad programming practice.
Do NOT concatenate the values in the where clause, use bind variables.

In addition, you do not need dynamic query, all is static (but values).

Database PL/SQL User's Guide and Reference
Chapter 13 PL/SQL Language Elements
Section INSERT Statement
(Sorry no link, site is down).

Regards
Michel
Re: how to use USING clause for dynamically appended where clauses [message #442476 is a reply to message #442473] Mon, 08 February 2010 06:40 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Here is the link

sriram Smile
Re: how to use USING clause for dynamically appended where clauses [message #442477 is a reply to message #442476] Mon, 08 February 2010 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, nice to have a backup site when Oracle's one is down (which is quite often these days).

Regards
Michel
Re: how to use USING clause for dynamically appended where clauses [message #442480 is a reply to message #442477] Mon, 08 February 2010 07:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Thanks, nice to have a backup site when Oracle's one is down (which is quite often these days).

I've noticed that too - it seems to be down once or twice every week at the moment.
Re: how to use USING clause for dynamically appended where clauses [message #442484 is a reply to message #442455] Mon, 08 February 2010 07:55 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
procedure testprod(p_fdate        in Varchar2,
                   p_tdate        in varchar2,

then
  where_clause := where_clause || ' AND (trunc(t.invoice_date)  BETWEEN ''' ||
                  p_fdate || ''' AND ''' || p_tdate || ''')';



This is a conflict.
Re: how to use USING clause for dynamically appended where clauses [message #442534 is a reply to message #442455] Mon, 08 February 2010 22:48 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Michel,

can i write like this.

procedure testprod(p_fdate        in Varchar2, 
                   p_tdate        in varchar2, 
                   p_type in varchar2) IS 
 
  where_clause VARCHAR2(32567) := ''; 
   
  SQL1 VARCHAR2(32567); 
 
BEGIN 
 
          
SQL1 := 'INSERT INTO GT_TEMP  (PARTY, REFERENCE)'; 
SQL1 := SQL1||'(SELECT DECODE(T.STATUS,''SALES'',T.RECEIVER,T.SENDER) PARTYID,T.REFERENCE  PARTYID,T.REFERENCE FROM DOCUMENT T'; 
SQL1 := SQL1||'WHERE T.CHARGE_STATUS IN (''INVOICED'', ''PARTLY_INVOICED'')'; 
SQL1 := SQL1||' AND (trunc(t.invoice_date)  BETWEEN :P_DATE1 AND :P_DATE2'; 
SQL1 := SQL1||' AND T.STATUS IN ('':P_TYPE'')'; 
 
EXECUTE IMMEDATE SQL1 USING P_FDATE,P_TDATE,P_TYPE; 
 
END;
Re: how to use USING clause for dynamically appended where clauses [message #442535 is a reply to message #442534] Mon, 08 February 2010 22:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>p_fdate in Varchar2,
>p_tdate in varchar2,

If "p_fdate" and/or "p_tdate" are really dates as in calendar dates, then storing them in VARCHAR2 is wrong & asking for problems in the future.

Please provide examples of values for these 2 arguments.
Re: how to use USING clause for dynamically appended where clauses [message #442543 is a reply to message #442534] Mon, 08 February 2010 23:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT use EXECUTE IMMEDIATE if nothing is dynamic.
Use directly INSERT.

Regards
Michel
Re: how to use USING clause for dynamically appended where clauses [message #442549 is a reply to message #442543] Mon, 08 February 2010 23:22 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
you mean to say like this.



INSERT INTO GT_TEST
SELECT DECODE(T.STATUS,'SALES',T.RECEIVER,T.SENDER) PARTYID,T.REFERENCE FROM DOCUMENT T
WHERE T.CHARGE_STATUS IN ('INVOICED', 'PARTLY_INVOICED')
AND trunc(t.invoice_date) BETWEEN :P_DATE1 AND :P_DATE2
USING P_FDATE,P_TDATE.
Re: how to use USING clause for dynamically appended where clauses [message #442559 is a reply to message #442549] Tue, 09 February 2010 01:58 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Close, just:
INSERT INTO GT_TEST
SELECT DECODE(T.STATUS,'SALES',T.RECEIVER,T.SENDER) PARTYID, T.REFERENCE 
FROM DOCUMENT T
WHERE T.CHARGE_STATUS IN ('INVOICED', 'PARTLY_INVOICED') 
  AND trunc(t.invoice_date) BETWEEN P_FDATE AND P_TDATE;

Regards
Michel
Previous Topic: Object does not exist or marked for delete
Next Topic: Is there a way to tell what Oracle tables are getting updated? (2 merged)
Goto Forum:
  


Current Time: Mon Nov 04 06:12:13 CST 2024