Home » RDBMS Server » Performance Tuning » How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix
How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix [message #64909] Mon, 01 March 2004 03:06 Go to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
Hello, We are using Oracle 9i and now for some Data Loading from Oracle 7.3 to Oracle 9i we want to generate Text file in Oracle 7.3 but util_file package is not worked in oracle 7.3. we have viewed  the value of util_file_dir in V$Parameter. and it has been set to null. and cannot be modified. so how to write or generate text file in Oracle 7.3 ? here in our case there is no communication in Oracle 9i and Oralce 7.3 Server. as listener service is not rrunning in Oracle 7.3 server.  can anybody Help or suggest how to write text file using PL/SQL.

Thanks in Advance

Arvind
Re: How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix [message #64913 is a reply to message #64909] Mon, 01 March 2004 21:52 Go to previous messageGo to next message
Prasad
Messages: 104
Registered: October 2000
Senior Member
Hi Aravind,

1)I think even 7.3.4 also supports UTL_FILE_DIR=*, which specifies anywhere you can write or read the file.

2) You are doing select or loading records into the database perl also will do the job
install DBI and DBD modules from cpan.perl.com
which facilitates you to write to flat file by selecting from database.

3) Why Still Oracle 7.3, You can migrate to 9i.
9i YOu can use External table concept for reading from external flat files.

Please choose which ever appropriate to you.

Thanks and Regards
Prasad
Re: How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix [message #64915 is a reply to message #64913] Tue, 02 March 2004 00:15 Go to previous messageGo to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
Hello Prasad, Thanks for taking Interest. Actually Currently we are working in ORACLE 9i itself. but at our Client site Existing system is in Oracle 7.3.2.2.0 on SCO Open Unix 5.0. and Listner Sevices in not running on Oralce 7.3 on unix machine. I think earlier S/W Consultant Firm has actually deleted Listner Related files. Now we have to Load and Migrate Old Existing Data into our Oracle 9i based System. so if Listner Works then we can directly Load Data through PL/SQL. But now we have to Load Data through Plain Text file, By giving command in Control file to Load Data. I am writing Oracle Version and utl_file_dir parameter from Inistialization Parameter file ie V$Parameter. Also when we tried to change the utl_file_dir paramrter then it gives Error.

Connected to:
Oracle7 Server Release 7.3.2.2.0 - Production Release
With the distributed option
PL/SQL Release 2.3.2.2.0 - Production

SQL> select * from v$version;

BANNER
-------------------------------------------------------
Oracle7 Server Release 7.3.2.2.0 - Production Release
PL/SQL Release 2.3.2.2.0 - Production
CORE Version 3.5.2.0.0 - Production
TNS for AT&T System V/386: Version 2.3.2.1.0 - Production
NLSRTL Version 3.2.2.0.0 - Production

SQL> select name,value from v$parameter where name = 'utl_file_dir';

NAME VALUE
---------------------------------------------------------------- --------------------------
utl_file_dir

SQL> Alter system set utl_file_dir = *;
Alter system set utl_file_dir = *
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

also Actually I am not aware of Perl/DBI/DBD . How can we get information of that.
Can it be possible that Export File(Exported from Oracle 7.3 on unix) be import in Oracle 9i on Windows 2000 Server. Because I know about that if Operating system changes, than normally file is not imported. If we export with giving Block Numbers(for Different O.S.)then we can import. so just confirm that what value can can give while exporting on unix machine

Thanks again taking Interest
Arvind
Re: How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix [message #64922 is a reply to message #64913] Tue, 02 March 2004 23:39 Go to previous messageGo to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
Actually we changed mannually the value of utl_file_dir=* in initalization parameter file. and it has been changed successfully. Now when i Create a Procedure the it gives error. it Procedure is

CREATE OR REPLACE PROCEDURE PR_CUSTFILE IS
Cursor CurCust IS SELECT SUVIDHA_ID, NAME_PREFIX, CUST_FNAME,CUST_MNAME,CUST_LNAME,FATHERNAME,REG_DATE,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,PIN_CODE,TEL_NO,FAX_NO,E_MAIL,PAN_NO,NATION,AGE,CUST_STAT,REMARKS FROM BMHCUST ORDER BY Suvidha_Id;

OutCol SYS.UTL_FILE.FILE_TYPE;
TodayDate Varchar2(10);
BatchYear Varchar2(4) ;
BatchMonth Varchar2(2) ;
BatchDay Varchar2(2) ;
File_Name Varchar2(30);
-------------------------------------------------------

BEGIN

IF SYS.UTL_FILE.IS_OPEN(OutCol) THEN
SYS.UTL_FILE.FCLOSE(OutCol);
END IF;
------------------------------------------------------- SELECT To_char(Sysdate,'DD'),To_char(Sysdate,'MM'),To_char(Sysdate,'RRRR')
INTO BatchDay,BatchMonth,BatchYear
FROM Dual;
------------------------------------------------------- File_Name := 'Cust'||BatchDay||BatchMonth||BatchYear||'.PRN';
OutCol := SYS.UTL_FILE.FOPEN('/usr/d/oracle7',File_Name,'W',32000);
FOR Cust in CurCust
LOOP
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.Suvidha_ID,' '),10,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.NAME_PREFIX,' '),5,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.CUST_FNAME,' '),30,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.CUST_MNAME,' '),20,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.CUST_LNAME,' '),30,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.FATHERNAME,' '),70));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(To_Char(Cust.REG_DATE,'DD/MM/RRRR'),' '),10,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.ADDRESS1,' '),60,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.ADDRESS2,' '),60,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.ADDRESS3,' '),60,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.CITY,' '),30,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.STATE,' '),30,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.PIN_CODE,' '),7,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.TEL_NO,' '),30,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.FAX_NO,' '),30,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.E_MAIL,' '),60,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.PAN_NO,' '),20,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.NATION,' '),20,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(To_Char(Cust.AGE),' '),3,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.CUST_STAT,' '),1,' '));
SYS.UTL_FILE.PUTF(OutCol,'%s',Rpad(NVL(Cust.REMARKS,' '),200,' '));
SYS.UTL_FILE.NEW_LINE(OutCol,1);
SYS.UTL_FILE.FFLUSH(OutCol);
END LOOP;
------------------------------------------------------- SYS.UTL_FILE.FCLOSE(OutCol);
IF SYS.UTL_FILE.IS_OPEN(OutCol) THEN
SYS.UTL_FILE.FCLOSE(OutCol);
END IF;
END;
/

EXEC PR_CUSTFILE

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE PR_CUSTFILE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
24/4 PL/SQL: Statement ignored
24/14 PLS-00306: wrong number or types of arguments in call to 'FOPEN'
SQL>

Actully this Procedure worked in ORACLE9i on Windows 2000 Server but gives Error in Oracle 7.3.2 on sco open unix server 5.0
Is there any other syntax of utl file open in Unix

Thanks
Arvind
Re: How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix [message #65196 is a reply to message #64915] Fri, 04 June 2004 23:15 Go to previous messageGo to next message
Doichin
Messages: 1
Registered: June 2004
Junior Member
You can not change the utl_file_dir parameter using ALTER SYSTEM command. Use OEM if your server is started with a spfile or edit your pfile and restart the instance. Refer to v$parameter column named is_sysmodifiable to check how to alter parameters.

Regards,
Doichin
Re: How to change the utl_file_dir initialization perameter in ORACLE 7.3 on unix [message #65197 is a reply to message #64915] Sat, 05 June 2004 00:13 Go to previous messageGo to next message
Arvind Bhandari
Messages: 50
Registered: May 2003
Member
Hello Doichin , thanks for taking interest. At that time i was trying Alter sytem command Now i done mannuly ie have shutdown the Database and change utl_file_dir parameter mannually in init.ora
ie utl_file_dir=*
and start the database now it works

thanks again

Arvind
Re: How to change the utl_file_dir initialization perameter in ORACLE 7.3 on un [message #65625 is a reply to message #64922] Tue, 16 November 2004 10:19 Go to previous message
Amit
Messages: 166
Registered: February 1999
Senior Member
i want to check my line perameter
Previous Topic: ORA-27100: shared memory realm already exists
Next Topic: Tuning Queries Help Required Urgently Oracle 9i
Goto Forum:
  


Current Time: Wed Jan 27 10:11:59 CST 2021