1

am trying execute this lines in procedure :

FOR I IN (SELECT * FROM TBL_MT_MOATA WHERE MT_RECEIVED_TIME between TO_CHAR(TRUNC(SYSDATE,'Month' )) AND LAST_DAY(TRUNC(SYSDATE, 'Month'))) LOOP
      --dbms_output.put_line(CUR_MONTH);
      INSERT INTO '|| CUR_MONTH ||' ( 
                            MT_ID                          ,                                                                                                                                                                                   
                            CL_ID                          ,                                                                                                                                                                                     
                            OP_ID                          ,                                                                                                                                                                                     
                            MT_PORT                        ,                                                                                                                                                                                 
                            MT_COST_TAG                    ,                                                                                                                                                                                  
                            MT_MSISDN                      ,                                                                                                                                                                                 
                            MT_TYPE_ID                     ,                                                                                                                                                                                    
                            MT_MSG                         ,                                                                                                                                                                                
                            MT_RECEIVED_TIME               ,                                                                                                                                                                                  
                            MT_PROCESSED                   ,                                                                                                                                                                                    
                            MT_STATUS                      ,                                                                                                                                                                                   
                            MT_DELIVERED                   ,                                                                                                                                                                                    
                            MT_DELIVERED_DETAILED          ,
                            MT_DR_IS_EXPIRED               ,
                            MT_IP                          ,
                            MT_RESPONSE_DESC 
                            )                                                                                                                                                                                    

                    VALUES (I.MT_ID                          ,                                                                                                                                                                                   
                            I.CL_ID                          ,                                                                                                                                                                                     
                            I.OP_ID                          ,                                                                                                                                                                                     
                            I.MT_PORT                        ,                                                                                                                                                                                 
                            I.MT_COST_TAG                    ,                                                                                                                                                                                  
                            I.MT_MSISDN                      ,                                                                                                                                                                                 
                            I.MT_TYPE_ID                     ,                                                                                                                                                                                    
                            I.MT_MSG                         ,                                                                                                                                                                                
                            I.MT_RECEIVED_TIME               ,                                                                                                                                                                                  
                            I.MT_PROCESSED                   ,                                                                                                                                                                                    
                            I.MT_STATUS                      ,                                                                                                                                                                                   
                            I.MT_DELIVERED                   ,                                                                                                                                                                                    
                            I.MT_DELIVERED_DETAILED          ,
                            I.MT_DR_IS_EXPIRED               ,
                            I.MT_IP                          ,
                            I.MT_RESPONSE_DESC
                            )  ;

PROBLEM : am getting : table or view does not exist i have tried to use table name am getting no error this error happens when i have variable CUR_MONTH how can use variable in INSERT query ?

UPDATE :

i have used execute immediate as the following :

 EXECUTE IMMEDIATE 'INSERT INTO '|| CUR_MONTH ||' ( 
                        MT_ID                          ,                                                                                                                                                                                   
                        CL_ID                          ,                                                                                                                                                                                     
                        OP_ID                          ,                                                                                                                                                                                     
                        MT_PORT                        ,                                                                                                                                                                                 
                        MT_COST_TAG                    ,                                                                                                                                                                                  
                        MT_MSISDN                      ,                                                                                                                                                                                 
                        MT_TYPE_ID                     ,                                                                                                                                                                                    
                        MT_MSG                         ,                                                                                                                                                                                
                        MT_RECEIVED_TIME               ,                                                                                                                                                                                  
                        MT_PROCESSED                   ,                                                                                                                                                                                    
                        MT_STATUS                      ,                                                                                                                                                                                   
                        MT_DELIVERED                   ,                                                                                                                                                                                    
                        MT_DELIVERED_DETAILED          ,
                        MT_DR_IS_EXPIRED               ,
                        MT_IP                          ,
                        MT_RESPONSE_DESC 
                        )
                        VALUES (
                        '|| I.MT_ID ||'                          ,                                                                                                                                                                                  
                        '|| I.CL_ID ||'                          ,                                                                                                                                                                                     
                        '|| I.OP_ID ||'                          ,                                                                                                                                                                                     
                        '|| I.MT_PORT ||'                        ,                                                                                                                                                                                 
                        '|| I.MT_COST_TAG ||'                    ,                                                                                                                                                                                  
                        '|| I.MT_MSISDN ||'                      ,                                                                                                                                                                                 
                        '|| I.MT_TYPE_ID ||'                     ,                                                                                                                                                                                    
                        '|| I.MT_MSG ||'                         ,                                                                                                                                                                                
                        '|| I.MT_RECEIVED_TIME ||'               ,                                                                                                                                                                                  
                        '|| I.MT_PROCESSED ||'                   ,                                                                                                                                                                                    
                        '|| I.MT_STATUS ||'                      ,                                                                                                                                                                                   
                        '|| I.MT_DELIVERED ||'                   ,                                                                                                                                                                                    
                        '|| I.MT_DELIVERED_DETAILED ||'          ,
                        '|| I.MT_DR_IS_EXPIRED ||'               ,
                        '|| I.MT_IP ||'                          ,
                        '|| I.MT_RESPONSE_DESC || '
                        )';

now am getting the following error :

ORA-00936: missing expression

Please advice ...

  • 3
    You need to use EXECUTE IMMEDIATE (docs.oracle.com/cd/B19306_01/appdev.102/b14261/…) for running dynamic sql. – a1ex07 Feb 20 '14 at 20:04
  • The insert statement in the execute immediate will not work for "string" or date types, as it produces INSERT INTO ... VALUES (..., text, ...) instead of VALUES(..., 'text', ...). Better use the execute immediate using clause (docs.oracle.com/cd/E16655_01/appdev.121/e17622/…). EXECUTE IMMEDIATE 'INSERT INTO ... VALUES (:b1, :b2, ...)' USING I.MT_ID, I.CL_ID, ...; – Balazs Papp Feb 23 '14 at 9:11
  • I presume you don't have Enterprise Edition with Partitioning Option? Because if you do, you could avoid all of this work altogether. – Colin 't Hart Feb 25 '14 at 10:34
1

Thanks Balazs papp ,

your answer was right 100% , i have used the following code :

EXECUTE IMMEDIATE 'INSERT INTO '|| CUR_MONTH ||' ( 
                            MT_ID                          ,                                                                                                                                                                                   
                            CL_ID                          ,                                                                                                                                                                                     
                            OP_ID                          ,                                                                                                                                                                                     
                            MT_PORT                        ,                                                                                                                                                                                 
                            MT_COST_TAG                    ,                                                                                                                                                                                  
                            MT_MSISDN                      ,                                                                                                                                                                                 
                            MT_TYPE_ID                     ,                                                                                                                                                                                    
                            MT_MSG                         ,                                                                                                                                                                                
                            MT_RECEIVED_TIME               ,                                                                                                                                                                                  
                            MT_PROCESSED                   ,                                                                                                                                                                                    
                            MT_STATUS                      ,                                                                                                                                                                                   
                            MT_DELIVERED                   ,                                                                                                                                                                                    
                            MT_DELIVERED_DETAILED          ,
                            MT_DR_IS_EXPIRED               ,
                            MT_IP                          ,
                            MT_RESPONSE_DESC 
                            )
                            VALUES (
                            :b1,:b2,:b3,:b4,:b5,:b6,:b7,:b8,:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16
                            )'
                            USING 
                            I.MT_ID                           ,                                                                                                                                                                                  
                            I.CL_ID                           ,                                                                                                                                                                                     
                            I.OP_ID                           ,                                                                                                                                                                                     
                            I.MT_PORT                       ,                                                                                                                                                                                 
                            I.MT_COST_TAG                    ,                                                                                                                                                                                  
                            I.MT_MSISDN                      ,                                                                                                                                                                                 
                            I.MT_TYPE_ID                     ,                                                                                                                                                                                    
                            I.MT_MSG                         ,                                                                                                                                                                                
                            I.MT_RECEIVED_TIME                ,                                                                                                                                                                                  
                            I.MT_PROCESSED                   ,                                                                                                                                                                                    
                            I.MT_STATUS                      ,                                                                                                                                                                                   
                            I.MT_DELIVERED                  ,                                                                                                                                                                                    
                            I.MT_DELIVERED_DETAILED          ,
                            I.MT_DR_IS_EXPIRED               ,
                            I.MT_IP                          ,
                            I.MT_RESPONSE_DESC
                            ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.