EBS : How to execute Dynamic SQL in PLSQL

 Dynamic SQL in PLSQL

In PL/SQL the SQL statement is created as a string, which is then executed using the EXECUTE IMMEDIATE clause

declare
v_techhards varchar2(2000);
begin

for r_techhards in (select * from user_tables) loop

v_techhards:= 'delete '||r_techhards.table_name;

dbms_output.put_line(v_techhards);

execute immediate v_techhards;

end loop;
end;
/ 

=========================================================================

#Oracle SQL#Oracle PLSQL#Oracle Apps#Oracle Database#SQL #Query #Dynamic SQL #execute immediate #Oracle dynamic DDL

EBS : PLSQL : How to generate ID based on input date : Oracle Apps

How to generate ID based on input date in PLSQL  

CREATE OR REPLACE FUNCTION generate_estqs_id (p_quote_date IN DATE)

   RETURN NUMBER

IS

   lv_month            NUMBER;

   lv_year             NUMBER;

   lv_year_next        NUMBER;

   lv_max_no           NUMBER;

   lv_from_date        DATE;

   lv_to_date          DATE;

   lv_quote_date       DATE;

   lv_estimtation_id   NUMBER;

BEGIN

   lv_quote_date := p_quote_date;

   SELECT TO_CHAR (lv_quote_date, 'MM')

     INTO lv_month

     FROM DUAL;

   DBMS_OUTPUT.put_line ('lv_month' || '-' || lv_month);

   IF lv_month IN ('01', '02', '03')

   THEN

      SELECT TO_CHAR (lv_quote_date, 'YY') - 1

        INTO lv_year

        FROM DUAL;

   ELSE

      SELECT TO_CHAR (lv_quote_date, 'YY')

        INTO lv_year

        FROM DUAL;

   END IF;

   lv_year_next := lv_year + 1;

   DBMS_OUTPUT.put_line (lv_year || '-' || lv_year_next);

   SELECT TO_CHAR ('01-Apr-' || lv_year), TO_CHAR ('31-Mar-' || lv_year_next)

     INTO lv_from_date, lv_to_date

     FROM DUAL;

   DBMS_OUTPUT.put_line (lv_from_date || '-' || lv_to_date);

   SELECT   NVL (SUBSTR (NVL (MAX (estimation_id), 0),

                         5,

                         LENGTH (MAX (estimation_id))

                        ),

                 0

                )

          + 1

     INTO lv_max_no

     FROM xx_estqs_hdr

    WHERE TRUNC (creation_date) BETWEEN lv_from_date AND lv_to_date;

   DBMS_OUTPUT.put_line ('lv_max_no' || '-' || lv_max_no);

   --lv_ESTIMTATION_id := lv_year || lv_year_next || '0000000' || lv_max_no;

   lv_estimtation_id := lv_year || lv_year_next || LPAD (lv_max_no, 8, 0);

   RETURN lv_estimtation_id;

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN 'Unable to Generate ESTIMTATION Id';

END;

EBS : Prepare Shell Scripts to download Concurrent program using PLSQL ( Oracle Apps)

 Oracle Apps: How to Prepare shell scripts file to download all custom concurrent Program from the system using plsql

DECLARE

l_string varchar2(2000);

l_conc_prog_string  varchar2(1000);

l_conc_prog_func_string  varchar2(1000);

fileHandler UTL_FILE.FILE_TYPE;

 ctlCode varchar2(2000);

 l_cnt Number :=0;

 

   CURSOR cur_conc_prog

   IS

    SELECT   concurrent_program_name conc_prog

        FROM fnd_concurrent_programs

       WHERE concurrent_program_name LIKE 'XX%'

    ORDER BY concurrent_program_name;

BEGIN

   -------------------------------Opeining File --------------------------------------------------

     fileHandler := UTL_FILE.FOPEN('APPS_DATA_FILE_DIR', 'Conc_Prog_download_LDT'||'_run.sh', 'W');

     

     

     --------------------------Start Priting Header Portion -----------------------------------------

    l_string := (   '# +===================================================================+'||chr(10) ||

                    '#  |                                                                   |'||chr(10)||

                    '#  |                                                                   |'||chr(10)||

                    '#  |File        : '||'Conc_Prog_download_LDT'||'_run.sh                                |'||chr(10)||

                    '#  |Description : This file will install all front-end                 |'||chr(10)||

                    '#  |Change History:                                                    |'||chr(10)||

                    '#  |---------------                                                    |'||chr(10)||

                    '#  |Version  Date         Author           Remarks                     |'||chr(10)||

                    '#  |-------  -----------  --------------   ----------------------------|'||chr(10)||

                    '#  |1.0 '||    to_Char(sysdate)||'  XX   Initial Version            |'||chr(10)||

                    '#  +===================================================================+'||chr(10)    

        );

      l_string := l_string ||chr(10)||('TIME_STAMP=`date +%Y%m%d%H%M`'||chr(10)||

                                       'LOG_FILE=`echo XX_$TIME_STAMP".log"`'||chr(10)||

                                       'TNS_DETAIL=`echo $HOSTNAME:$ORACLE_PORT:$ORACLE_SID` '||chr(10));

                                       

      UTL_FILE.PUTF(fileHandler, l_string);

      l_string :=                CHR(10) ||   

                 'CHKLOGIN(){  '||CHR(10)|| 

                            'if sqlplus /nolog <<! >/dev/null 2>&1 ' ||CHR(10)|| 

                            '     WHENEVER SQLERROR EXIT 1;   ' ||CHR(10)|| 

                            '      CONNECT $1 ;               ' ||CHR(10)|| 

                            '      EXIT;                      ' ||CHR(10)|| 

                       '!'||CHR(10)|| 

                       '      then                            ' ||CHR(10)||

                       '           echo OK                    ' ||CHR(10)||

                       '      else                            ' ||CHR(10)||

                       '           echo NOK                   ' ||CHR(10)||

                       '      fi                              ' ||CHR(10)||

                       ' }                                    ' ||CHR(10)||CHR(10)||CHR(10);

     

     UTL_FILE.PUTF(fileHandler, l_string);

     l_string := CHR(10) ||

                 'APPS_LOGIN_ID="$1"'||CHR(10)||

                 'XX_LOGIN_ID="$2"'||CHR(10)||CHR(10)||CHR(10)||CHR(10);

     UTL_FILE.PUTF(fileHandler, l_string);       

     

   l_string :=  '# *******************************************************************'||CHR(10)|| 

                '#  Check if APPS Login Id is entered else prompt to get it'||CHR(10)|| 

                '# *******************************************************************'||CHR(10)|| 

    'while [ "$APPS_LOGIN_ID" = "" -o `CHKLOGIN "$APPS_LOGIN_ID" "DUAL"` = "NOK" ]'||CHR(10)|| 

    'do'||CHR(10)|| 

        'if [ "$APPS_LOGIN_ID" = "" ];then'||CHR(10)|| 

            'echo " Enter APPS schema Userid/Passwd (apps/apps): "'||CHR(10)||  

            'read APPS_LOGIN_ID'||CHR(10)|| 

        'else'||CHR(10)|| 

            'echo "Enter APPS Userid/Passwd (apps/apps):  "'||CHR(10)||  

            'read APPS_LOGIN_ID'||CHR(10)|| 

        'fi'||CHR(10)|| 

    'done'||CHR(10)|| 

    'APPS_LOGIN=`echo $APPS_LOGIN_ID | cut -d"/" -f1`'||CHR(10)|| 

    'APPS_PWD=`echo $APPS_LOGIN_ID | cut -d"/" -f2`'||CHR(10);

     UTL_FILE.PUTF(fileHandler, l_string);    

     

     

     

     

     l_string := '# *******************************************************************'||chr(10)||

                 '# Creating Concurrent_Prog in Apps'||chr(10)||

                 '# *******************************************************************';

     UTL_FILE.PUTF(fileHandler, l_string);            

     

     -------------------------End Prinitng Header Portion ------------------------------------------ 

   FOR i IN cur_conc_prog

   LOOP

    l_string := NULL;

    l_conc_prog_string := NULL;

    l_conc_prog_func_string := NULL;

    

----------------------------------------------- CREATING RUN SCRIPT-----------------------------------------------------------------------------------------  

        -------------------------------Start Printing Concurrent Program  -------------------------------------------

       

     l_string  := chr(10)||chr(10)|| '# Start Deploying  : *******************************'|| i.conc_prog||'_CC'||'************************************************************';   

     l_string := l_string ||chr(10)||chr(10)||chr(10)||'echo "Downloading ldt of  Concurrent Programs '||i.conc_prog||'_CC.ldt in Apps ..."  | tee -a $LOG_FILE'||CHR(10);

     l_conc_prog_string := l_string ||CHR(10)||'$FND_TOP/bin/FNDLOAD $APPS_LOGIN_ID 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct ' ||i.conc_prog||'_CC.ldt PROGRAM APPLICATION_SHORT_NAME="'||'XX'||'" CONCURRENT_PROGRAM_NAME="'||i.conc_prog||'"';

                                          

     UTL_FILE.PUTF(fileHandler,chr(10)|| l_conc_prog_string);

     

     /* l_string := (CHR(10)||

       'if [ $? = 0 ];'     ||CHR(10)||            

        'then'              ||CHR(10)||

            'echo "Registration of    conc_prog :- '||i.conc_prog_name||'_conc_prog.ldt successful " | tee -a $LOG_FILE'                       ||CHR(10)||

       'else'               ||CHR(10)||              

            'echo "Error in registration of  conc_prog :- '||i.conc_prog_name||'_conc_prog.ldt Please correct and rerun" | tee -a $LOG_FILE'  ||CHR(10)||

            'exit'          ||CHR(10)||        

        'fi'                ||CHR(10));

     

      UTL_FILE.PUTF(fileHandler, l_string);*/  

      -------------------------------END Printing conc_prog  -------------------------------------------       

      l_cnt := l_cnt + 1;  

   END LOOP;

    UTL_FILE.PUTF(fileHandler, chr(10)||chr(10)|| '#Number Of Concurrent Program Downloaded Succesfully :-'||l_cnt);

    UTL_FILE.FCLOSE(fileHandler);

END;