Re: Execute immediate

From: Matthias Rogel <rogelREMOVE_at_THISweb.de>
Date: Fri, 07 Feb 2003 12:13:57 +0100
Message-ID: <3E4394F5.2060900@THISweb.de>

Niall Litchfield wrote:
> "Adrien" <Adrien_at_nospam.com> wrote in message
> news:200326-12118-911965_at_foorum.com...
>
>>
> Yes. execute immediate is a way of dynamically executing SQL not
PL/SQL just
> use the PLSQL in the normal way.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>
>

attached a code fragment which writes a excel-csv-file for *ANY* query (nearly) using dynamic PL/SQL

can you do this without dynamic PL/SQL ?

Matthias

function toExcel(query in varchar2,

                   filename in varchar2,
                   fieldseparator in varchar2,
                   clientcharacterset in varchar2,
                   dateformat in varchar2,
                   decimalplaces in integer,
                   printheader in integer
                   ) return integer is

-- returns #of rows written
plsqlblock varchar2(32767);
c number;
d number;
j integer;
col_cnt integer;
rec_tab dbms_sql.desc_tab;
Result integer := 0;
writecmd varchar2(32);
closecmd varchar2(32);

begin

   c := dbms_sql.open_cursor;
   dbms_sql.parse(c, query, dbms_sql.native);

   d := dbms_sql.execute(c);

   dbms_sql.describe_columns(c, col_cnt, rec_tab);

   dbms_sql.close_cursor(c);

   writecmd := 'utl_file.put_line';
   closecmd := 'utl_file.fclose';

   plsqlblock := 'declare outf utl_file.file_type;' || chr(10) ||

           'begin ' || chr(10) ||
           'outf := utl_file.fopen(:ExcelPath, :filename || ''.csv'', 

''w'', 32767);' || chr(10);

   plsqlblock := plsqlblock ||

           'if :clientcharacterset is null then null; end if;' || chr(10) ||

           'if :dateformat is null then null; end if;' || chr(10) ||
           'if :decimalplaces is null then null; end if;' || chr(10);

   if printheader != 0 then  -- print header
      plsqlblock := plsqlblock || writecmd || '(outf, ''';
      for j in 1..col_cnt loop
         if j = col_cnt then
            plsqlblock := plsqlblock || rec_tab(j).col_name;
         else
            plsqlblock := plsqlblock || rec_tab(j).col_name || 
fieldseparator;
         end if;
      end loop;
      plsqlblock := plsqlblock || ''');' || chr(10);

   end if;

   plsqlblock := plsqlblock || 'for orec in (' || query || ') loop ' || chr(10);

   plsqlblock := plsqlblock || writecmd || '(outf,';

   for j in 1..col_cnt loop

      if rec_tab(j).col_type = 12 then   -- DATE
         plsqlblock := plsqlblock || 'to_char(orec.' || 
rec_tab(j).col_name || ', :dateformat)';
      elsif rec_tab(j).col_type = 2 then   -- NUMBER
         plsqlblock := plsqlblock || 'round(orec.' || 
rec_tab(j).col_name || ', :decimalplaces)';
      else -- CHAR/VARCHAR(2)
         plsqlblock := plsqlblock || 'convert(orec.' || 
rec_tab(j).col_name || ', :clientcharacterset)';
      end if;
      if j < col_cnt then
         plsqlblock := plsqlblock || '||''' || fieldseparator || '''||';
      end if;

   end loop;

   plsqlblock := plsqlblock || ');' || chr(10);

   plsqlblock := plsqlblock || ':Result:=:Result+1;' || chr(10);
   plsqlblock := plsqlblock || 'end loop;' || chr(10);
   plsqlblock := plsqlblock || closecmd || '(outf);' || chr(10);
   plsqlblock := plsqlblock || 'exception when others then begin begin ' 

|| closecmd || '(outf); exception when others then null; end; raise; end;';

   plsqlblock := plsqlblock || 'end;';

   execute immediate plsqlblock using in ExcelPath, in filename, in clientcharacterset, in dateformat, in decimalplaces, in out Result;

   return Result;

   exception when others then begin

        begin dbms_sql.close_cursor(c); exception when others then null; end;

        raise;
      end;

end toExcelInternal; Received on Fri Feb 07 2003 - 05:13:57 CST