4 Replies Latest reply on Jun 14, 2018 11:35 AM by Alibune

    Passing parameters to a procedure through APEX

    Alibune

      Hi everyone!

       

      I'm having some trouble in my application, i had the following code into a procedure

      Insert into table_name (INTCODIGO,STRNOME,STRESPECIFICACAO)
      select  c001
           , c002
           , c003
      from apex_collections col
      where collection_name = 'XLS' || :P201_TAB and seq_id >5 and is_numeric(c001)=1 and c001 !=' ';
      
      

       

      and it was working, but i need to change this into a stored procedure and only pass the parameter with apex, i've tried to make like this:

       

      create or replace PROCEDURE insert_xls (indice IN USER_TABLES.table_name%type, aba IN integer) IS
      
      
      BEGIN
      
      
      DECLARE L varchar2(20) := ' ''XLS''' ||aba|| ''' ';
      
      
      BEGIN
      execute IMMEDIATE 
      'Insert into ' || indice || '(INTCODIGO,STRNOME,STRESPECIFICACAO) 
      select  c001
           , c002
           , c003
      from apex_collections col
      where collection_name = '||L|| ' and seq_id >5 and is_numeric(c001)=1;'  ;
      END;
      END insert_xls;
      

       

      but it is not working, i've tried several changes in this code but can't make it work, can anybody help me?

        • 1. Re: Passing parameters to a procedure through APEX
          RomeuBraga

          Just to test I made this:

           

          DECLARE
          
          
          indice VARCHAR2(100) := 'MYTABLE';
          aba NUMBER := 50;
          v_output VARCHAR2(4000);
          L varchar2(20) := ' ''XLS''' ||aba|| ''' ';  
              
          BEGIN  
          
          
          v_output := 'Insert into ' || indice || '(INTCODIGO,STRNOME,STRESPECIFICACAO)   
          select  c001  
               , c002  
               , c003  
          from apex_collections col  
          where collection_name = '||L|| ' and seq_id >5 and is_numeric(c001)=1;'  ;  
          
          
          htp.p(v_output);
          
          
          END;  
          
          
          
          
          
          
          
          
          

           

          The output was:

           

          Insert into MYTABLE(INTCODIGO,STRNOME,STRESPECIFICACAO)  
          select c001  
            , c002  
            , c003  
          from apex_collections col  
          where collection_name = 'XLS'50' and seq_id >5 and is_numeric(c001)=1;
          

           

          Try to do:

           

          create or replace PROCEDURE "INSERT_XLS" (indice IN USER_TABLES.table_name%type, aba IN integer) AS 
          
          L varchar2(20) := ' ''XLS' ||aba|| ''' ';    
            
          BEGIN  
            
          execute IMMEDIATE   
          'Insert into ' || indice || '(INTCODIGO,STRNOME,STRESPECIFICACAO)   
          select  c001  
               , c002  
               , c003  
          from apex_collections col  
          where collection_name = '||L|| ' and seq_id >5 and is_numeric(c001)=1;'  ;  
          
          END;
          
          • 2. Re: Passing parameters to a procedure through APEX
            fac586

            Alibune wrote:

             

            I'm having some trouble in my application, i had the following code into a procedure

            1. Insertintotable_name(INTCODIGO,STRNOME,STRESPECIFICACAO)
            2. selectc001
            3. ,c002
            4. ,c003
            5. fromapex_collectionscol
            6. wherecollection_name='XLS'||:P201_TABandseq_id>5andis_numeric(c001)=1andc001!='';

            and it was working, but i need to change this into a stored procedure and only pass the parameter with apex, i've tried to make like this:

            1. createorreplacePROCEDUREinsert_xls(indiceINUSER_TABLES.table_name%type,abaINinteger)IS
            2. BEGIN
            3. DECLARELvarchar2(20):='''XLS'''||aba||'''';
            4. BEGIN
            5. executeIMMEDIATE
            6. 'Insertinto'||indice||'(INTCODIGO,STRNOME,STRESPECIFICACAO)
            7. selectc001
            8. ,c002
            9. ,c003
            10. fromapex_collectionscol
            11. wherecollection_name='||L||'andseq_id>5andis_numeric(c001)=1;';
            12. END;
            13. ENDinsert_xls;

            but it is not working, i've tried several changes in this code but can't make it work, can anybody help me?

             

            Why does the insert have to be performed dynamically on different tables? Different tables with the same column structure usually indicate design problems.

             

            Concatenation and quote escaping should be avoided when working with dynamic SQL. They are (as you have discovered) invariably a source of error, but more importantly this approach is vulnerable to SQL injection attack and has adverse performance implications. Instead:

             

             

            create or replace procedure insert_xls (
                indice in user_tables.table_name%type
              , aba    in integer)
            is 
            
              collection_name apex_collections.collection_name%type := 'XLS' || to_char(aba, 'tm9'); 
            
              dml varchar2(4000) := q'{
            insert into %s
              (  intcodigo
                , strnome
                , strespecificacao)
            select
                  c001 
                , c002 
                , c003 
            from
                apex_collections col 
            where
                collection_name = :n
            and seq_id > 5
            and is_numeric(c001) = 1
            }'; 
            
            begin
            
              dml := apex_string.format(dml, dbms_assert.sql_object_name(indice));
            
              apex_debug.message('insert_xls dml = %s', dml);
            
              execute immediate dml
                using collection_name;
            
            end insert_xls;
            
            1 person found this helpful
            • 3. Re: Passing parameters to a procedure through APEX
              Alibune

              Not worked, got this error:

               

              • is_internal_error: false
              • ora_sqlcode: -911
              • ora_sqlerrm: ORA-00911: caractere inválido
              • component.type: APEX_APPLICATION_PAGE_PROCESS
              • component.id: 84656658347969652
              • component.name: INSERT_XLS
              • error_backtrace:
                ORA-06512: em "ESTUDO.INSERT_XLS", line 7 ORA-06512: em line 2 ORA-06512: em "SYS.DBMS_SQL", line 1825 ORA-06512: em "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1880 ORA-06512: em "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1895 ORA-06512: em "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 936 ORA-06512: em "APEX_050100.WWV_FLOW_PROCESS_NATIVE", line 71 ORA-06512: em "APEX_050100.WWV_FLOW_PROCESS_NATIVE", line 1132 ORA-06512: em "APEX_050100.WWV_FLOW_PLUGIN", line 2399 ORA-06512: em "APEX_050100.WWV_FLOW_PROCESS", line 200
              • error_statement:
                begin BEGIN insert_xls( indice => :P201_LISTA, aba => :P201_TAB ); END; end;
              • 4. Re: Passing parameters to a procedure through APEX
                Alibune

                Thanks fac!

                 

                It worked.