Oracle stored procedures, export SQL to text, dynamic SQL, dbms_ SQL use

1. Create or replace package sqlexp_pack is
3. -- Author: yanlei
4. -- Created: 11:25:09
5. -- Purpose: Oracle Export
6. -- SQL export text string
7. Function sqltotext (sqlstr in varchar2) return long;
8. -- SQL Export File
9. Function sqltofile (sqlstr in varchar2, filename in varchar2)
10. Return varchar2;
12. End sqlexp_pack;
13. /
14. Create or replace package body sqlexp_pack is
15. -- SQL export text string
16. Function sqltotext (sqlstr in varchar2) return long is
18. Colname varchar2 (4000 );
19. Rownum integer;
20. Colnum number default 0; -- number of fields
22. Linestr varchar2 (20000 );
23. Yltbl dbms_ SQL .desc_tab; -- table information
24. Ylcursor integer default dbms_ SQL .open_cursor; -- SQL output cursor
25. Rstr long;
26. Begin
28. Dbms_ SQL .parse (ylcursor, sqlstr, dbms_ SQL .native); -- parse dynamic SQL statements;
29. Dbms_ SQL .describe_columns (ylcursor, colnum, yltbl); -- get the metadata of dynamic SQL
30. For I in 1 .. colnum Loop
31. Dbms_ SQL .define_column (ylcursor, I, colname, 4000); -- defines dynamic SQL Columns
33. End loop;
35. Rownum: = dbms_ SQL .execute (ylcursor); -- execute dynamic SQL statements. Return Value. For non-query statements, execute executes the statement and returns the number of rows processed. For a query, execute returns the result of determining the activity set. The returned value is 0.
36. If rownum <> 0 then
37. Return '';
38. End if;
39. Rstr: = '';
40. While (dbms_ SQL .fetch_rows (ylcursor)> 0) loop
42. Linestr: = '';
43. For I in 1 .. colnum Loop
44. Dbms_ SQL .column_value (ylcursor, I, colname); -- Obtain the field value
45. Linestr: = linestr | colname;
46. If I <colnum then
47. Linestr: = linestr | ',';
48. End if;
50. End loop;
52. Rstr: = rstr | linestr | CHR (10 );
54. End loop;
56. Dbms_ SQL .close_cursor (ylcursor );
57. Return rstr;
58. Exception
59. When others then
60. Dbms_ SQL .close_cursor (ylcursor );
61. Raise;
62. End sqltotext;
63. -- SQL Export File
64. Function sqltofile (sqlstr in varchar2, filename in varchar2)
65. Return varchar2 is
66. STR long;
67. Begin
68. STR: = sqltotext (sqlstr );
69. Ylfileop_pack.writefile (filename, STR );
70. Return 'successfully ';
71. End;
73. End sqlexp_pack;
74. /
75. --- Reprinted copyright by yanleigis Email: landgis@126.com