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  |
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  |
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 |
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 |
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
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 23 10:39:16 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
