Home » SQL & PL/SQL » SQL & PL/SQL » dynamic sql not working (oracle 10 g)
dynamic sql not working [message #413598] Thu, 16 July 2009 11:31 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
The procedure is not givng any error message but
EXECUTE IMMEDIATE 'DELETE FROM demand WHERE' || r_c1.MAP_DEMND || '=:PLAN_UNIT_CODE'
USING r_c1.entity_name;
this is not executing.
kindly help me how to sort it out



CREATE OR REPLACE procedure sort_proc IS

TYPE cur_typ IS REF CURSOR;
c cur_typ;

v_sqlquery varchar2(400);
v_entity_name varchar2(400);
v_sqlquery1 varchar2(1400);
v_sql_sort varchar2(400);
g_errm varchar2(400);

V_PLN_DMND_NAME varchar2(200);
V_PLN_CAT varchar2(200);
V_DMND_SHIP_NAME varchar2(200);
V_DUE_DATE varchar2(200);
V_ADJ_DUE_DATE_TIME varchar2(200);
V_DMND_LINE_ITEM_NAME varchar2(200);
V_REQ_ITEM varchar2(200);
V_REQ_QTY varchar2(200);
V_DC varchar2(200);
V_BA_LIMIT varchar2(200);
V_BL_LIMIT varchar2(200);
V_SORT_KEY varchar2(200);
V_CUST_NO varchar2(200);
V_DOMN_NAME varchar2(200);
V_SEQ_CTR varchar2(200);
V_STYLE_COLOR varchar2(200);
V_Coalition varchar2(200);
V_SBU_CODE varchar2(200);
V_SUB_SBU_CODE varchar2(200);
V_PROD_LINE_CODE varchar2(200);
V_PLAN_UNIT_CODE varchar2(200);
V_MKTG_UNIT_CODE varchar2(200);
V_sort_due_date varchar2(200);
V_sort_dmd_type varchar2(200);
V_sort_mfg_routes varchar2(200);
V_sort_adj_due_date varchar2(200);
V_sort_dmd_seq varchar2(200);
V_sort_dmd_qty varchar2(200);






cursor c1 is
select entity_name,MAP_DEMND,
(SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order1) as srt_order1,
(SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order2) as srt_order2,
(SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order3) as srt_order3,
(SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order4) as srt_order4,
(SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order5) as srt_order5,
(SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order6) as srt_order6,
(SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM map_sort_seq WHERE ATTR = srt_order7) as srt_order7
from (select s.* ,m.priority,m.MAP_DEMND
from map_sort_entity m ,sort_sequence s
where m.entity_type=s.entity_type
and s.IS_DEFAULT='N'
order by m.priority)
where rownum <2;


BEGIN
----------------
for r_c1 in c1 loop
execute immediate 'truncate table temp_sort';
begin
v_sqlquery:=' select * from demand where ' || r_c1.MAP_DEMND ||'=:PLAN_UNIT_CODE' ;
---------------------------------------------
OPEN c FOR v_sqlquery USING r_c1.entity_name;
LOOP
BEGIN
FETCH c INTO V_PLN_DMND_NAME , V_PLN_CAT , V_DMND_SHIP_NAME ,V_DUE_DATE ,V_ADJ_DUE_DATE_TIME,
V_DMND_LINE_ITEM_NAME ,V_REQ_ITEM,V_REQ_QTY, V_DC ,
V_BA_LIMIT ,V_BL_LIMIT ,V_SORT_KEY,V_CUST_NO ,V_DOMN_NAME,
V_SEQ_CTR,V_STYLE_COLOR,V_Coalition,V_SBU_CODE,
V_SUB_SBU_CODE ,
V_PROD_LINE_CODE,V_PLAN_UNIT_CODE,V_MKTG_UNIT_CODE ,V_sort_due_date,
V_sort_dmd_type, V_sort_mfg_routes,V_sort_adj_due_date,V_sort_dmd_seq ,
V_sort_dmd_qty ;
EXIT WHEN c%NOTFOUND;

insert into temp_sort (PLN_DMND_NAME , PLN_CAT , DMND_SHIP_NAME ,DUE_DATE ,ADJ_DUE_DATE_TIME,
DMND_LINE_ITEM_NAME ,REQ_ITEM,REQ_QTY, DC ,
BA_LIMIT ,BL_LIMIT ,SORT_KEY,CUST_NO ,DOMN_NAME,
SEQ_CTR,STYLE_COLOR,Coalition,SBU_CODE,
SUB_SBU_CODE ,
PROD_LINE_CODE,PLAN_UNIT_CODE,MKTG_UNIT_CODE ,sort_due_date,
sort_dmd_type, sort_mfg_routes,sort_adj_due_date,sort_dmd_seq ,
sort_dmd_qty )
values
( V_PLN_DMND_NAME , V_PLN_CAT , V_DMND_SHIP_NAME ,V_DUE_DATE ,V_ADJ_DUE_DATE_TIME,
V_DMND_LINE_ITEM_NAME ,V_REQ_ITEM,V_REQ_QTY, V_DC ,
V_BA_LIMIT ,V_BL_LIMIT ,V_SORT_KEY,V_CUST_NO ,V_DOMN_NAME,
V_SEQ_CTR,V_STYLE_COLOR,V_Coalition,V_SBU_CODE,
V_SUB_SBU_CODE ,
V_PROD_LINE_CODE,V_PLAN_UNIT_CODE,V_MKTG_UNIT_CODE ,V_sort_due_date,
V_sort_dmd_type, V_sort_mfg_routes,V_sort_adj_due_date,V_sort_dmd_seq ,
V_sort_dmd_qty );

exception
WHEN OTHERS THEN
g_errm := SUBSTR(SQLERRM, 1 , 64);
dbms_output.put_line(g_errm);
END;
END LOOP;
commit;
CLOSE c;

--------------------------
--Following delte is not working
--------------------------


--dbms_output.put_line( r_c1.MAP_DEMND||'--->>'||r_c1.entity_name);
EXECUTE IMMEDIATE 'DELETE FROM demand WHERE' || r_c1.MAP_DEMND || '=:PLAN_UNIT_CODE'
USING r_c1.entity_name;
commit;

exception
WHEN OTHERS THEN
g_errm := SUBSTR(SQLERRM, 1 , 64);

end;
end loop;

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



END;
/
show errors


Re: dynamic sql not working [message #413599 is a reply to message #413598] Thu, 16 July 2009 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Syntax error in SQL for EXECUTE IMMEDIATE.

Display SQL line before passing to EXECUTE IMMEDIATE
Re: dynamic sql not working [message #413600 is a reply to message #413598] Thu, 16 July 2009 11:41 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not giving an error message because your exception handler is suppressing it.
Remove the exception handlers.
and next time you post code please, please use code tags.
Re: dynamic sql not working [message #413601 is a reply to message #413598] Thu, 16 July 2009 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:
Michel Cadot wrote on Wed, 15 July 2009 14:29
As requested in your other topics: follow the OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel


Re: dynamic sql not working [message #413603 is a reply to message #413601] Thu, 16 July 2009 12:17 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Thanks A lot Michel
Next time onwards I ll follow the guidlines.
like the following way
CREATE OR REPLACE procedure sort_proc IS

TYPE cur_typ IS REF CURSOR;
 c           cur_typ;

v_sqlquery         varchar2(400);
v_entity_name      varchar2(400);
v_sqlquery1        varchar2(1400);
 v_sql_sort        varchar2(400);
g_errm             varchar2(400);

 V_PLN_DMND_NAME        varchar2(200);     
 V_PLN_CAT              varchar2(200); 
 V_DMND_SHIP_NAME       varchar2(200);
 V_DUE_DATE             varchar2(200);
 V_ADJ_DUE_DATE_TIME    varchar2(200);
 V_DMND_LINE_ITEM_NAME  varchar2(200);
 V_REQ_ITEM             varchar2(200);
 V_REQ_QTY              varchar2(200);
 V_DC                   varchar2(200);              
 V_BA_LIMIT             varchar2(200);
 V_BL_LIMIT             varchar2(200);
 V_SORT_KEY             varchar2(200);
 V_CUST_NO              varchar2(200);
 V_DOMN_NAME            varchar2(200);
 V_SEQ_CTR              varchar2(200);
 V_STYLE_COLOR          varchar2(200);
 V_Coalition            varchar2(200);
 V_SBU_CODE             varchar2(200);
 V_SUB_SBU_CODE         varchar2(200);
 V_PROD_LINE_CODE       varchar2(200);
 V_PLAN_UNIT_CODE       varchar2(200);
 V_MKTG_UNIT_CODE       varchar2(200);
 V_sort_due_date        varchar2(200);
 V_sort_dmd_type        varchar2(200); 
 V_sort_mfg_routes      varchar2(200);
 V_sort_adj_due_date    varchar2(200);
 V_sort_dmd_seq         varchar2(200);
 V_sort_dmd_qty         varchar2(200);
 

cursor c1 is 
select entity_name,MAP_DEMND,
 (SELECT ATTR_TYPE || ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order1) as srt_order1,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order2) as srt_order2,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order3) as srt_order3,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order4) as srt_order4,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order5) as srt_order5,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order6) as srt_order6,
 (SELECT ATTR_TYPE|| ' ' || SORT_ORDR FROM  map_sort_seq WHERE ATTR = srt_order7) as srt_order7
from (select s.* ,m.priority,m.MAP_DEMND
from  map_sort_entity m ,sort_sequence s
where m.entity_type=s.entity_type
and s.IS_DEFAULT='N'
order by m.priority)
where rownum <2;


BEGIN
----------------
         for r_c1 in  c1 loop
           execute immediate 'truncate table temp_sort';
           begin
           v_sqlquery:=' select  * from demand where ' ||  r_c1.MAP_DEMND ||'=:PLAN_UNIT_CODE' ;
             ---------------------------------------------          
             OPEN c FOR v_sqlquery USING r_c1.entity_name;
             LOOP
             BEGIN
             FETCH c INTO  V_PLN_DMND_NAME ,     V_PLN_CAT , V_DMND_SHIP_NAME ,V_DUE_DATE ,V_ADJ_DUE_DATE_TIME,
                                    V_DMND_LINE_ITEM_NAME   ,V_REQ_ITEM,V_REQ_QTY, V_DC ,              
                                    V_BA_LIMIT ,V_BL_LIMIT ,V_SORT_KEY,V_CUST_NO ,V_DOMN_NAME,
                                    V_SEQ_CTR,V_STYLE_COLOR,V_Coalition,V_SBU_CODE,
                                    V_SUB_SBU_CODE          ,
                                    V_PROD_LINE_CODE,V_PLAN_UNIT_CODE,V_MKTG_UNIT_CODE ,V_sort_due_date,
                                    V_sort_dmd_type, V_sort_mfg_routes,V_sort_adj_due_date,V_sort_dmd_seq  ,
                                    V_sort_dmd_qty   ;
             EXIT WHEN c%NOTFOUND;
       
             insert into temp_sort (PLN_DMND_NAME ,       PLN_CAT , DMND_SHIP_NAME ,DUE_DATE ,ADJ_DUE_DATE_TIME,
                                    DMND_LINE_ITEM_NAME   ,REQ_ITEM,REQ_QTY, DC ,              
                                    BA_LIMIT ,BL_LIMIT ,SORT_KEY,CUST_NO ,DOMN_NAME,
                                    SEQ_CTR,STYLE_COLOR,Coalition,SBU_CODE,
                                    SUB_SBU_CODE          ,
                                    PROD_LINE_CODE,PLAN_UNIT_CODE,MKTG_UNIT_CODE ,sort_due_date,
                                    sort_dmd_type, sort_mfg_routes,sort_adj_due_date,sort_dmd_seq  ,
                                    sort_dmd_qty   )  
                            values
                                  ( V_PLN_DMND_NAME ,     V_PLN_CAT , V_DMND_SHIP_NAME ,V_DUE_DATE ,V_ADJ_DUE_DATE_TIME,
                                    V_DMND_LINE_ITEM_NAME   ,V_REQ_ITEM,V_REQ_QTY, V_DC ,              
                                    V_BA_LIMIT ,V_BL_LIMIT ,V_SORT_KEY,V_CUST_NO ,V_DOMN_NAME,
                                    V_SEQ_CTR,V_STYLE_COLOR,V_Coalition,V_SBU_CODE,
                                    V_SUB_SBU_CODE          ,
                                    V_PROD_LINE_CODE,V_PLAN_UNIT_CODE,V_MKTG_UNIT_CODE ,V_sort_due_date,
                                    V_sort_dmd_type, V_sort_mfg_routes,V_sort_adj_due_date,V_sort_dmd_seq  ,
                                    V_sort_dmd_qty   ); 
             
             exception
             WHEN OTHERS THEN
             g_errm := SUBSTR(SQLERRM, 1 , 64);
             dbms_output.put_line(g_errm);
             END;                   
             END LOOP;
             commit;
             CLOSE c;
              
              --------------------------
              --Following delte is not working
              --------------------------
              
              
               --dbms_output.put_line(  r_c1.MAP_DEMND||'--->>'||r_c1.entity_name);
               EXECUTE IMMEDIATE 'DELETE FROM demand WHERE' || r_c1.MAP_DEMND || '=:PLAN_UNIT_CODE'
               USING r_c1.entity_name;
               commit;

           exception
           WHEN OTHERS THEN
           g_errm := SUBSTR(SQLERRM, 1 , 64);  
                    
          end;
         end loop;

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


 
END;
/
show errors


Re: dynamic sql not working [message #413606 is a reply to message #413603] Thu, 16 July 2009 12:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Now if instead of "is not working" we would have the actual error, that would be something.

And if you would just have read the replies so far, and commented out the buggy-by-default "when other" part to see the actual error, that would have been something, too.

But on first glance, I suspect the problem is the missing blank after the "WHERE".
Re: dynamic sql not working [message #413608 is a reply to message #413606] Thu, 16 July 2009 12:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. At least your IMMEDIATE problem.

The problem on the long run will be that the general approach will break horribly and unpredictably in any production multi-user environment sooner or later anyway.
Re: dynamic sql not working [message #413612 is a reply to message #413608] Thu, 16 July 2009 13:05 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Quote:
The problem on the long run will be that the general approach will break horribly and unpredictably in any production multi-user environment sooner or later anyway.



Thomass , sorry I didnt get it

[Updated on: Thu, 16 July 2009 13:35] by Moderator

Report message to a moderator

Re: dynamic sql not working [message #413614 is a reply to message #413612] Thu, 16 July 2009 13:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You suppress all exceptions, and your approach doesn't work in a multi-user environment. People will get wrong data, and nobody will be able to figure out why, because you suppressed all exceptions.

Re: dynamic sql not working [message #413643 is a reply to message #413598] Thu, 16 July 2009 22:31 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
bond007,

I've stared at posted coded for a decent number of minutes
& the more I look at it the less I like it & the less I understand it.

Below are some comments in no particular order.

1) Dynamic SQL scales as well as goats fly.
2) Row by Row is Slow by slow [FETCH c INTO]
Rows are FETCHed into local variables simply to be used in INSERT statement.
Which could be done a single SQL & no PL/SQL.
3) As a general rule TEMP tables are not needed in Oracle.
I am curious exactly what temp_sort is used for outside this procedure.
4) The CBO work best when it has accurate statistics .
So truncating & loading brand new data, can result in pronounced performance problems.
5) Your most recent challenge has been on "DELETE FROM demand" which seems out of place since it is not referenced in any other place in this code.
I guess folks no longer believe or practice module coding.

Good Luck when this code goes into Production!
Previous Topic: Working one query but trouble with some extension please help me
Next Topic: How to pass the result in refcursor to a table without using LOOP
Goto Forum:
  


Current Time: Sun Dec 11 02:37:33 CST 2016

Total time taken to generate the page: 0.07998 seconds