Dynamic Sql

[orajit]

I have written code in Dbms_sql form
Can anybody tell me how to use dynamic sql for below code ,..

If possible please rewriie below code in dynamic sql form and resend it

Thanks in advance ,,,,

 
create or replace function  utlfile_gen ( p_query     in VARCHAR2,
                                          p_separator in VARCHAR2 default '~',
                                          p_dir       in VARCHAR2,
                                          p_filename  in VARCHAR2 )
return number
AUTHID CURRENT_USER
is
    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;
    l_sql varchar2(1000);
begin
    l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 
    for i in 1 .. 1000 loop
        BEGIN 
 
 
            dbms_sql.define_column( l_theCursor, i,l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;
    end loop;
 
    dbms_sql.define_column( l_theCursor, 1, l_columnValue,2000 );
 
    l_status := dbms_sql.execute(l_theCursor);
 
    loop
        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i,l_columnValue );
            utl_file.put( l_output, l_separator ||l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;
    end loop;
    dbms_sql.close_cursor(l_theCursor);
 
    utl_file.fclose( l_output );
    return l_cnt;
end utlfile_gen;
 

[orajit]

Can anybody rewrite the above code in execute immediate format..

Thanks

[debasisdas]

you simply need to frame the syntax at runtime and execute that using EXECUTE IMMEDIATE.

No need to use the old DBMS package.

[orajit]

can u please send me the updated code . Thanks in advance

[orajit]

Hi Debasis,

Can u please replace the dbms_sql to Execute immediate and resend to me,

I am using oracle 10g. dbms_sql is old way of writting the code and it has been replace by execute immediate, Thats why I wanted to use the
execute immediate into my code,

Can u please rewrite the code into execute immediate and send it to me

Thanks for ur help ..

[amitpatel66]

Hi Debasis,

Can u please replace the dbms_sql to Execute immediate and resend to me,

I am using oracle 10g. dbms_sql is old way of writting the code and it has been replace by execute immediate, Thats why I wanted to use the
execute immediate into my code,

Can u please rewrite the code into execute immediate and send it to me

Thanks for ur help ..

Why dont you give a try and let us know in case of any issues??