| how to initialise init.ora file [message #42519] |
Mon, 07 April 2003 04:13  |
ramesh srivastava
Messages: 3
Registered: April 2003
|
Junior Member |
|
|
i have posted my initial problem of copyeing the output of sql queries into a text file in reply to which i got the solution to use the UTL utility package in my procedure.I have done the same but there aroused another problem i.e. first we have to initialise the init.ora file and write the following lines in the UTIL para:-UTL_FILE_DIR=$ORACLE_HOME/sql/files
or
UTL_FILE_DIR=C:oraclefiles
For this i have contacted my system administrator and he also inserted the above lines in the init.ora file but still i am getting run time error messages when i try to execute the sql procedure.So please suggest how to initialise the init.ora file to run the following procedure:-
create or replace procedure tablenames (file_path1 in varchar2,file_path2 in varchar2,file_name1 in varchar2,file_name2 in varchar2)is
CURSOR CUR1 IS SELECT TNAME FROM TAB WHERE TNAME LIKE 'V%' and tabtype = 'TABLE' AND TNAME NOT IN ('VALID','VAMT_COA');
A NUMBER(10);
incr number(10);
SSQL VARCHAR2(100);
SCURSOR INTEGER;
SCURSOR1 INTEGER;
rows_processed INTEGER;
loopcounter integer;
file_handle UTL_FILE.FILE_TYPE;
BEGIN
incr := 0 ;
loopcounter:=0;
while loopcounter<=1
loop
if loopcounter<1 then
file_handle:=UTL_FILE.FOPEN(file_path1,file_name1,'W');
else
file_handle:=UTL_FILE.FOPEN(file_path2,file_name2,'W');
end if;
FOR I IN CUR1
LOOP
SSQL := 'SELECT COUNT(*) FROM ' || 'FA'||'.' ||I.TNAME;
IF dbms_sql.IS_OPEN(SCURSOR) THEN
dbms_sql.CLOSE_cursor(SCURSOR);
END IF;
SCURSOR := dbms_sql.open_cursor;
dbms_sql.parse(SCURSOR,SSQL,dbms_sql.native);
dbms_sql.define_column(SCURSOR, 1, A);
rows_processed := dbms_sql.execute(SCURSOR);
IF dbms_sql.fetch_rows(SCURSOR) > 0 THEN
dbms_sql.column_value(SCURSOR, 1, A);
if loopcounter<1 then
if a > 0 then
if(incr=0) then
UTL_FILE.PUTF(file_handle,'TABLENAME: NO. OF ROWS:');
end if;
incr := incr + 1;
DBMS_OUTPUT.PUT_LINE(i.tname);
DBMS_OUTPUT.PUT_LINE('no of rows' || A);
UTL_FILE.PUT_LINE(file_handle,i.tname);
UTL_FILE.PUT_LINE(file_handle,A);
end if;
else
if a<=0 then
if(incr=0) then
UTL_FILE.PUTF(file_handle,'TABLENAME: NO. OF ROWS:');
end if;
incr:=incr + 1;
dbms_output.put_line(i.tname);
dbms_output.put_line('no. of rows' || A);
UTL_FILE.PUT_LINE(file_handle,i.tname);
UTL_FILE.PUT_LINE(file_handle,A);
end if;
end if;
END IF;
END LOOP;
if loopcounter<1 then
DBMS_OUTPUT.PUT_LINE('total no of tables having data '|| incr );
else
DBMS_OUTPUT.PUT_LINE('total no of tables without data '|| incr );
end if;
loopcounter:=loopcounter+1;
incr:=0;
UTL_FILE.FCLOSE(file_handle);
end loop;
END;
THANKS IN ADVANCE.
RGDS
ramesh
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
