Oracle Native Dynamic SQL

July 20, 2013 7:43 am Published by

Sotto un esempio di una funzione Oracle per creare una query dinamica.

CREATE OR REPLACE FUNCTION GET_TAB1_BY_ITEM(P_FIELD VARCHAR2, P_ITEM VARCHAR2) RETURN 

VARCHAR2
    IS 
    
    QSTR       VARCHAR2(10000);
    RESULT    VARCHAR2(32000);
    
 BEGIN
        
      QSTR := 'SELECT '|| P_FIELD || ' FROM TAB1 WHERE TAB1.ITEM=:p1';
                                                
       BEGIN        
       
           EXECUTE IMMEDIATE QSTR
           INTO RESULT
           USING P_ITEM;
       
       EXCEPTION 
       
          WHEN NO_DATA_FOUND THEN
              RESULT:= '-';
              
           WHEN OTHERS THEN
              RESULT:= '#';
           
       END;
                   
   RETURN(RESULT);
 
 END;
/

http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

Categorised in: ,

This post was written by admin

Comments are closed here.