[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 ,,,,

Expand|Select|Wrap|Line Numbers

1.  
2. create or replace function  utlfile_gen ( p_query     in VARCHAR2,
3.                                           p_separator in VARCHAR2 default '~',
4.                                           p_dir       in VARCHAR2,
5.                                           p_filename  in VARCHAR2 )
6. return number
7. AUTHID CURRENT_USER
8. is
9.     l_output        utl_file.file_type;
10.     l_theCursor     integer default dbms_sql.open_cursor;
11.     l_columnValue   varchar2(2000);
12.     l_status        integer;
13.     l_colCnt        number default 0;
14.     l_separator     varchar2(10) default '';
15.     l_cnt           number default 0;
16.     l_sql varchar2(1000);
17. begin
18.     l_output := utl_file.fopen( p_dir, p_filename, 'w' );
19.  
20.     dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
21.  
22.     for i in 1 .. 1000 loop
23.         BEGIN 
24.  
25.  
26.             dbms_sql.define_column( l_theCursor, i,l_columnValue, 2000 );
27.             l_colCnt := i;
28.         exception
29.             when others then
30.                 if ( sqlcode = -1007 ) then exit;
31.                 else
32.                     raise;
33.                 end if;
34.         end;
35.     end loop;
36.  
37.     dbms_sql.define_column( l_theCursor, 1, l_columnValue,2000 );
38.  
39.     l_status := dbms_sql.execute(l_theCursor);
40.  
41.     loop
42.         exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
43.         l_separator := '';
44.         for i in 1 .. l_colCnt loop
45.             dbms_sql.column_value( l_theCursor, i,l_columnValue );
46.             utl_file.put( l_output, l_separator ||l_columnValue );
47.             l_separator := p_separator;
48.         end loop;
49.         utl_file.new_line( l_output );
50.         l_cnt := l_cnt+1;
51.     end loop;
52.     dbms_sql.close_cursor(l_theCursor);
53.  
54.     utl_file.fclose( l_output );
55.     return l_cnt;
56. end utlfile_gen;
57.  

[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]

Originally Posted by 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 ..

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