I create a procedure to write table data into file on the server. The procedure like this:
create or replace PROCEDURE run_query (p_sql IN VARCHAR2
,p_dir IN VARCHAR2
,p_header_file IN VARCHAR2
,p_data_file IN VARCHAR2 := NULL) IS
begin
...............................
end;
Then I call procedure in sqlplus as:
exec run_query('select * from schema.table_name', 'defined directory','file_name.txt');
It works well. But I need to write all tables in one schema to the files. So I create another CONTROL procedure to call the first procedure to get a list of tables from defined schema. Then iterates to write each table to the file. My control procedure looks like this:
create or replace PROCEDURE call_run_query is
v_owner varchar2(40);
v_tab_name varchar2(30);
v_dir varchar2(10) := '/temp/dir';
v_ftype varchar2(3) :='txt';
/* cursor to get table names */
CURSOR c IS
SELECT owner, table_name from all_tables where owner = 'XXX' order by table_name;
BEGIN
-- Open cursor to loop
OPEN c;
LOOP
FETCH c INTO v_owner, v_tab_name;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(q'[(']'||'select * from XXX.'||v_tab_name||q'[']'||','||q'[']'||v_dir||q'[']'||',' ||q'[']'||v_owner||'-'||v_tab_name||'.'||v_ftype||q'[')]');
execute immediate URR_TAB_FILE (q'[(']'||'select * from XXX.'||v_tab_name||q'[']'||','||q'[']'||v_dir||q'[']'||',' ||q'[']'||v_owner||'-'||v_tab_name||'.'||v_ftype||q'[')]');
END LOOP;
CLOSE c;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END call_run_query;
when I compile, it feedback with pls-00306 wrong number or types of arguments in call to the run_query. But the DBMS_OUTPUT return line in loop like this
('select * from xxx.table_name', 'defined directory', 'file_name.txt').
This is the same line I put in sqlplus call. why come out pls-00306? Please advise me how to fix this or give me your suggestion. Thank you in advance.