I have following procedure
CREATE OR REPLACE PROCEDURE p_create_text_file (
loc IN VARCHAR2
, file IN VARCHAR2
, select_statement in varchar2
, line_statement in varchar2
)
IS
fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, 'W');
line VARCHAR2(2000);
BEGIN
FOR rec IN (
/*replace this select*/
select
parameter
, value
from nls_database_parameters
where parameter in ('NLS_RDBMS_VERSION', 'NLS_CHARACTERSET')
/*end of replace*/
)
LOOP
line := rec.parameter || ';' || rec.value;
UTL_FILE.PUT_LINE (fid, line);
END LOOP;
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
END;
/
and I need to replace the select statement with "something" so that it can be passed in an IN parameter SELECT_STATEMENT.
The procedure call should look like this:
begin
p_create_text_file (
loc => 'EXPDIR'
, file => 'exp.log'
, select_statement => 'select parameter, value from nls_database_parameters where parameter in (''NLS_RDBMS_VERSION'', ''NLS_CHARACTERSET'')'
, line_statement => null
);
end;
/
I tried dynamic SQL but it did not work.
The procedure should be able to process any select statement.
As you don't know the columns that wil be returned by the passed-in query at compile time, you can't refer to them inside the loop statically.
You could use the dbms_sql package to do this dynamically:
CREATE OR REPLACE PROCEDURE p_create_text_file (
loc IN VARCHAR2
, file IN VARCHAR2
, select_statement in varchar2
, line_statement in varchar2 -- not used?
)
IS
fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, 'W');
-- for dbms_sql
l_c pls_integer;
l_col_cnt pls_integer;
l_desc_t dbms_sql.desc_tab3;
l_rc pls_integer;
l_varchar varchar2(4000);
BEGIN
-- create cursor and prepare from passed-in statement
l_c := dbms_sql.open_cursor;
dbms_sql.parse(c=>l_c, statement=>select_statement,
language_flag=>dbms_sql.native);
dbms_sql.describe_columns3(c => l_c, col_cnt => l_col_cnt,
desc_t => l_desc_t);
-- define all columns as strings; this will end up with implicit conversion
-- of dates etc. using NLS settings, so shoudl be finsessed based on data
-- actual data type really...
for i in 1..l_col_cnt loop
dbms_sql.define_column(c=>l_c, position=>i,
column=>l_varchar, column_size=>4000);
end loop;
-- execute the query
l_rc := dbms_sql.execute(c=>l_c);
-- fetch each row in turn
while dbms_sql.fetch_rows(c=>l_c) > 0 loop
-- for each column from describe
for i in 1..l_col_cnt loop
-- get the column value for this row (again, as string...)
dbms_sql.column_value(l_c, i, l_varchar);
-- write out to file, with delimiter after first column
if i > 1 then
UTL_FILE.PUT (fid, ';');
end if;
UTL_FILE.PUT (fid, l_varchar);
end loop;
UTL_FILE.NEW_LINE (fid);
end loop;
dbms_sql.close_cursor(l_c);
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
END;
/
That, bascally, parses the passed-in statement, executes it, fetches each row, gets each column value in turn (as a string, which could/should be expanded to avoid implicit conversions), and writes ach of those out to the file in turn - adding a delimiter between them, and a final new-line after each row.
When called from your anonymous block that creates a file containing:
NLS_CHARACTERSET;AL32UTF8
NLS_RDBMS_VERSION;11.2.0.4.0
Be aware, thoguh, that this will run anythign it's given, including DDL (which is executed when parsed). If you don't control how this is called, and really even if you do, you should add validation of the passed-in statement to verify that is is actually just a query.
You might find it simpler to explore other methods, such as external tables (as @Kaushik suggested), or client functionality.
As @kfinity suggested in a comment, you could use a ref cursor to parse and execute the query, which should prevent anything nasty from being run. The dbms_sql package has a function to convert a ref cursor to a native cursor, so using that insetad of the explicit open, parse and execute steps:
CREATE OR REPLACE PROCEDURE p_create_text_file (
loc IN VARCHAR2
, file IN VARCHAR2
, select_statement in varchar2
, line_statement in varchar2 -- not used?
)
IS
fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, 'W');
-- for initial parse and execute
l_refcursor sys_refcursor;
-- for dbms_sql
l_c pls_integer;
l_col_cnt pls_integer;
l_desc_t dbms_sql.desc_tab3;
l_rc pls_integer;
l_varchar varchar2(4000);
BEGIN
-- open ref cursor for the statement
open l_refcursor for select_statement;
-- convert ref cursor to dbms_sql cursor
l_c := dbms_sql.to_cursor_number(l_refcursor);
dbms_sql.describe_columns3(c => l_c, col_cnt => l_col_cnt,
desc_t => l_desc_t);
-- define all columns as strings; this will end up with implicit conversion
-- of dates etc. using NLS settings, so shoudl be finsessed based on data
-- actual data type really...
for i in 1..l_col_cnt loop
dbms_sql.define_column(c=>l_c, position=>i,
column=>l_varchar, column_size=>4000);
end loop;
-- fetch each row in turn
while dbms_sql.fetch_rows(c=>l_c) > 0 loop
-- for each column from describe
for i in 1..l_col_cnt loop
-- get the column value for this row (again, as string...)
dbms_sql.column_value(l_c, i, l_varchar);
-- write out to file, with delimiter after first column
if i > 1 then
UTL_FILE.PUT (fid, ';');
end if;
UTL_FILE.PUT (fid, l_varchar);
end loop;
UTL_FILE.NEW_LINE (fid);
end loop;
dbms_sql.close_cursor(l_c);
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
END;
/
... which produces the same output file.
Incidentally, if you wanted to you could also write the column names out as a header row, before the fetch-rows loop:
-- write column names as header row
for i in 1..l_col_cnt loop
if i > 1 then
UTL_FILE.PUT (fid, ';');
end if;
UTL_FILE.PUT (fid, l_desc_t(i).col_name);
end loop;
UTL_FILE.NEW_LINE (fid);