Home » SQL & PL/SQL » SQL & PL/SQL » sequence in Dynamic sql (oracle 10 g)
sequence in Dynamic sql [message #416435] Sun, 02 August 2009 11:47 Go to next message
bond007
Messages: 58
Registered: March 2009
Member
Hi friend,
Need your help for the following procedure.
While I am putting the sequencce in the dynamic sql this code throws error.

CREATE OR REPLACE procedure sort_proc IS

TYPE cur_typ IS REF CURSOR;
c                  cur_typ;
v_sqlquery         varchar2(1400);
v_entity_name      varchar2(400);
v_sql_sort         varchar2(1400);
g_errm             varchar2(400);
cnt                number(6);
cursor c1 is 
select * from parm_search order by priority,entity_name;

BEGIN
EXECUTE IMMEDIATE 'truncate table demand_out';
----------------
cnt:=0;
   for r_c1 in  c1 loop
   begin
     cnt:=cnt+1;
     EXECUTE IMMEDIATE 'truncate table temp_sort';
     EXECUTE IMMEDIATE 'insert into temp_sort select  * from demand where ' || r_c1.MAP_DEMND || '=:PLAN_UNIT_CODE'
               USING r_c1.entity_name;
             commit;
     EXECUTE IMMEDIATE 'DELETE FROM demand WHERE ' || r_c1.MAP_DEMND || '=:PLAN_UNIT_CODE'
               USING r_c1.entity_name;
              commit;
     
-------------- here  DEMAND_OUT_SEQ.NEXTVAL  doesnot  work and throws error
 
EXECUTE IMMEDIATE'insert into demand_out select  DEMAND_OUT_SEQ.NEXTVAL  as sort_ordr ,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
as text,'''||cnt||'_FILE_NM_'|| r_c1.MAP_DEMND ||
''' from temp_sort order by '|| r_c1.srt_order1||','||r_c1.srt_order2||','||r_c1.srt_order3||
','||r_c1.srt_order4||','||r_c1.srt_order5||','||r_c1.srt_order6
||','||r_c1.srt_order7;

commit;
     exception
           WHEN OTHERS THEN
           g_errm := SUBSTR(SQLERRM, 1 , 64); 
          dbms_output.put_line(  g_errm ||r_c1.entity_name);  
  
            
      end;            
    end loop;

-------------- here  DEMAND_OUT_SEQ.NEXTVAL  works fine 

insert into demand_out select  DEMAND_OUT_SEQ.NEXTVAL  as sort_ordr ,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  as text,'999_file_nm_demand'
        from demand;
commit;
EXECUTE IMMEDIATE 'truncate table demand';


 
END;
/
show errors





I am getting the following error
ORA-02287: sequence number not allowed hereARJ
ORA-02287: sequence number not allowed hereDPA
ORA-02287: sequence number not allowed hereF02
ORA-02287: sequence number not allowed hereFG2
ORA-02287: sequence number not allowed hereIPA
ORA-02287: sequence number not allowed hereIPC
ORA-02287: sequence number not allowed here050
ORA-02287: sequence number not allowed here390
ORA-02287: sequence number not allowed here15
ORA-02287: sequence number not allowed hereC6
ORA-02287: sequence number not allowed hereF4
ORA-02287: sequence number not allowed hereFC
ORA-02287: sequence number not allowed here01
ORA-02287: sequence number not allowed here02
ORA-02287: sequence number not allowed here04
ORA-02287: sequence number not allowed here05
ORA-02287: sequence number not allowed here17
ORA-02287: sequence number not allowed here20
ORA-02287: sequence number not allowed here47
ORA-02287: sequence number not allowed here53
ORA-02287: sequence number not allowed here54
ORA-02287: sequence number not allowed here64
ORA-02287: sequence number not allowed here5
ORA-02287: sequence number not allowed hereD
ORA-02287: sequence number not allowed hereL
ORA-02287: sequence number not allowed hereN

PL/SQL procedure successfully completed

SQL> 



[Updated on: Sun, 02 August 2009 13:32] by Moderator

Report message to a moderator

Re: sequence in Dynamic sql [message #416436 is a reply to message #416435] Sun, 02 August 2009 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: sequence in Dynamic sql [message #416438 is a reply to message #416435] Sun, 02 August 2009 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Both statements are not the same ones so you can't compare.
Display (output) the string in execute immediate.

Regards
Michel
Re: sequence in Dynamic sql [message #416439 is a reply to message #416438] Sun, 02 August 2009 13:23 Go to previous messageGo to next message
bond007
Messages: 58
Registered: March 2009
Member
Yes michel .Both are not same.
But how can we use the sequence in the execute immediate.
Re: sequence in Dynamic sql [message #416440 is a reply to message #416435] Sun, 02 August 2009 13:26 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>But how can we use the sequence in the execute immediate.


Display (output) the string in execute immediate.
Re: sequence in Dynamic sql [message #416441 is a reply to message #416439] Sun, 02 August 2009 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bond007 wrote on Sun, 02 August 2009 20:23
Yes michel .Both are not same.
But how can we use the sequence in the execute immediate.

No there are not.
Quote:
'''||cnt||'_FILE_NM_'|| r_c1.MAP_DEMND ||''' from temp_sort order by '|| r_c1.srt_order1||','||r_c1.srt_order2||','||r_c1.srt_order3||','||r_c1.srt_order4||','||r_c1.srt_order5||','||r_c1.srt_order6||','||r _c1.srt_order7;

Quote:
from demand;

Regards
Michel
Re: sequence in Dynamic sql [message #416443 is a reply to message #416441] Sun, 02 August 2009 16:56 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And the whole thing will not work this way anyway.

First, you are turning your application into a single-user application.

Second, you still seem to be determined to use dynamic SQL despite that still being the worst option to do anything.
Previous Topic: Variable stors the value of another variable
Next Topic: BULK COLLECT -- FOR ALL Exceptions
Goto Forum:
  


Current Time: Tue Dec 06 15:47:06 CST 2016

Total time taken to generate the page: 0.10556 seconds