UTL_FILE with FOPEN, GET_LINE, and Wrong Number of Arguments?
Date: 1997/07/28
Message-ID: <01bc9b97$14d98d20$39b849c7_at_ANGEL_A.DEP.STATE.FL.US>
[Quoted] Can anyone help me search for the obvious oversite? I have tried many variations and I keep getting the "PLS-00306: wrong number or types of arguments in call to 'SAMP_LOAD_SRV'." I did a describe on the procedure and found the correct number and types of arguments. I have included the procedure and table description in the body of this message. I would appreciate any suggestions. I'm already using SQL*Loader but require a programmatic option.
SQL> execute samp_load_srv('\u15\www_dev\utl','test.txt'); begin samp_load_srv('\u15\www_dev\utl','test.txt'); end;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'SAMP_LOAD_SRV'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
The UTL_FILE package requires INIT.ORA file to have a secure directory location specified as follows: utl_file_dir = /u15/www_dev/utl or \tmp etc.....
SQL> desc samp_load_srv
PROCEDURE samp_load_srv
Argument Name Type In/OutDefault?
- ----------------------- ------
P_FILEDIR VARCHAR2 IN P_FILENAME VARCHAR2 IN P_TOTALINSERTED NUMBER IN/OUT
PROCEDURE samp_load_srv(
P_FileDir IN Varchar2, P_FileName IN Varchar2, P_TotalInserted IN OUT Number ) AS V_FileHandle UTL_FILE.FILE_TYPE; --V_FileHandle TEXT_IO.FILE_TYPE; V_NewLine VarChar2(100);--InputLine V_FAC_ID samploc_data.PK$FAC_ID%TYPE; V_POINT_ID samploc_data.FK$POINT_ID%TYPE; V_LAT_DD samploc_data.LAT_DD%TYPE; V_LAT_MM samploc_data.LAT_MM%TYPE; V_LAT_SS samploc_data.LAT_SS%TYPE; V_LONG_DD samploc_data.LONG_DD%TYPE; V_LONG_MM samploc_data.LONG_MM%TYPE; V_LONG_SS samploc_data.LONG_SS%TYPE; V_SL_METHOD samploc_data.SL_METHOD%TYPE; V_SL_COMMENTS samploc_data.SL_COMMENTS%TYPE; V_SL_CONTRACTOR samploc_data.SL_CONTRACTOR%TYPE; /*Positions of tabs within InputLine*/ V_Firsttab Number; V_Secondtab Number; V_Thirdtab Number; V_Fourthtab Number; V_Fifthtab Number; V_Sixthtab Number; V_Seventhtab Number; V_Eighthtab Number; V_Ninthtab Number; V_Tenthtab Number;
BEGIN
--Open file for reading
V_Filehandle:=UTL_FILE.FOPEN(p_FileDir,p_FileName,'r'); --V_Filehandle:=TEXT_IO.FOPEN(p_FileDir,p_FileName,'r');
--Initialize output number of records
P_TotalInserted:=0;
--Loop over the file, reading in each line. GETLINE will
--raise NO_DATA_FOUND when it is done. Exit condition.
LOOP
BEGIN
UTL_FILE.GET_LINE(V_FileHandle,V_NewLine); --TEXT_IO.GET_LINE(V_FileHandle,V_NewLine); EXCEPTION
When NO_DATA_FOUND Then
EXIT;
END;
--Use INSTR to find locations of tabs
V_Firsttab:=INSTR(V_NewLine,'<tab>',1,1); V_Secondtab:=INSTR(V_NewLine,'<tab>',1,2); V_Thirdtab:=INSTR(V_NewLine,'<tab>',1,3); V_Fourthtab:=INSTR(V_NewLine,'<tab>',1,4); V_Fifthtab:=INSTR(V_NewLine,'<tab>',1,5); V_Sixthtab:=INSTR(V_NewLine,'<tab>',1,6); V_Seventhtab:=INSTR(V_NewLine,'<tab>',1,7); V_Eighthtab:=INSTR(V_NewLine,'<tab>',1,8); V_Ninthtab:=INSTR(V_NewLine,'<tab>',1,9); V_Tenthtab:=INSTR(V_NewLine,'<tab>',1,10);
--Use SUBSTR to extract the fields
V_FAC_ID:=SUBSTR(V_NewLine,1,V_Firsttab-1); V_POINT_ID:=SUBSTR(V_NewLine,V_Firsttab+1,V_Secondtab-V_Firstt ab-1); V_LAT_DD:=SUBSTR(V_NewLine,V_Secondtab+1,V_Thirdtab-V_Secondta b-1); V_LAT_MM:=SUBSTR(V_NewLine,V_Thirdtab+1,V_Fourthtab-V_Thirdtab -1); V_LAT_SS:=SUBSTR(V_NewLine,V_Fourthtab+1,V_Fifthtab-V_Fourthta b-1); V_LONG_DD:=SUBSTR(V_NewLine,V_Fifthtab+1,V_Sixthtab-V_Fifthtab -1); V_LONG_MM:=SUBSTR(V_NewLine,V_Sixthtab+1,V_Seventhtab-V_Sixtht ab-1); V_LONG_SS:=SUBSTR(V_NewLine,V_Seventhtab+1,V_Eighthtab-V_Seven thtab-1); V_SL_METHOD:=SUBSTR(V_NewLine,V_Eighthtab+1,V_Ninthtab-V_Eight htab-1); V_SL_COMMENTS:=SUBSTR(V_NewLine,V_Ninthtab+1,V_Tenthtab-V_Nint htab-1); V_SL_CONTRACTOR:=SUBSTR(V_NewLine,V_tenthtab+1);
--Insert the new record into table
INSERT INTO
samploc_data(PK$FAC_ID,FK$POINT_ID,LAT_DD,LAT_MM,LAT_SS,LONG_DD,LONG_M
M,LONG_SS,SL_METHOD,SL_COMMENTS,SL_CONTRACTOR) VALUES (V_FAC_ID, V_POINT_ID, V_LAT_DD, V_LAT_MM, V_LAT_SS, V_LONG_DD, V_LONG_MM, V_LONG_SS, V_SL_METHOD, V_SL_COMMENTS, V_SL_CONTRACTOR); P_TotalInserted:=P_TotalInserted+1;
END LOOP;
--Close the file
UTL_FILE.FCLOSE(V_FileHandle);
--TEXT_IO.FCLOSE(V_FileHandle);
COMMIT;
EXCEPTION
--Handle the exceptions
/*
WHEN no_data_found THEN
TEXT_IO.PUT_LINE('Closing the file...');
TEXT_IO.FCLOSE(V_FileHandle);
*/
-- WHEN TEXT_IO.INVALID_OPERATION THEN
WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(V_FileHandle);
-- TEXT_IO.FCLOSE(V_FileHandle);
Raise_Application_Error(-20051,'Load samploc_data: Invalid
Operation');
-- WHEN TEXT_IO.INVALID_FILEHANDLE THEN
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(V_FileHandle);
--TEXT_IO.FCLOSE(V_FileHandle);
Raise_Application_Error(-20052,'Load samploc_data: Invalid File
Handle');
-- WHEN TEXT_IO.READ_ERROR THEN
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(V_FileHandle);
--TEXT_IO.FCLOSE(V_FileHandle);
Raise_Application_Error(-20053,'Load samploc_data: Read Error');
WHEN OTHERS THEN
UTL_FILE.FCLOSE(V_FileHandle);
--TEXT_IO.FCLOSE(V_FileHandle);
RAISE;
END samp_load_srv;
SQL> desc samploc_data
Name Null? Type ------------------------------- -------- ---- PK$FAC_ID NOT NULL NUMBER(12) FK$POINT_ID VARCHAR2(14) LAT_DD NUMBER(3) LAT_MM NUMBER(3) LAT_SS NUMBER(6,4) LONG_DD NUMBER(3) LONG_MM NUMBER(3) LONG_SS VARCHAR2(6) SL_METHOD VARCHAR2(2) SL_COMMENTS VARCHAR2(30) SL_CONTRACTOR VARCHAR2(5)
SQL> If you can help, please reply as soon as you are able.
Thanks
Arnold Received on Mon Jul 28 1997 - 00:00:00 CEST