Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2) (Oracle 11GR2)
PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2) [message #676372] Wed, 05 June 2019 09:31 Go to next message
maik2910
Messages: 2
Registered: June 2019
Junior Member
PLS-00306: wrong number or types of arguments in call to 'FOPEN'

There is an error, someone could help fix this procedure

create or replace procedure movimento as

arquivo_saida utl_file.file_type;

TYPE mov_cur_typ IS REF CURSOR;

mov_cur mov_cur_typ;
MOV_NOTA mov_capa.nota%TYPE;
MOV_DATA mov_capa.DATA%TYPE;
MOV_MOEDA mov_capa.MOEDA%TYPE;
MOV_TOT mov_capa.TOTAL%TYPE;
mov_codigo mov_detalhe.codigo%TYPE;
mov_ean mov_detalhe.ean%TYPE;
mov_descricao mov_detalhe.descricao%TYPE;
mov_qtde mov_detalhe.qtde%TYPE;
mov_valor_unit mov_detalhe.valor_unit%TYPE;
mov_total mov_detalhe.total%TYPE;

CURSOR c1 IS
SELECT D.nota,
rms7to_date(D.DATA),
D.MOEDA,
D.TOTAL,
CURSOR (SELECT e.codigo,
e.ean,
TRIM(e.descricao),
e.qtde,
e.valor_unit,
e.total
FROM mov_detalhe e
WHERE e.nota = d.nota
ORDER BY e.nota) mov_detalhe
FROM mov_capa d
ORDER BY nota;
BEGIN
arquivo_saida:=UTL_File.Fopen('c:\relatorios','arquivo.txt', 'w');
OPEN c1;
LOOP
-- Process each row of query result set
FETCH c1
INTO MOV_NOTA, MOV_DATA, MOV_MOEDA, MOV_TOT, mov_cur;
EXIT WHEN c1%NOTFOUND;

utl_file.PUT_LINE(arquivo_saida,
MOV_NOTA || ',' || MOV_DATA || ',' || MOV_MOEDA || ',' ||
MOV_TOT);

LOOP
-- Process each row of subquery result set
FETCH mov_cur
INTO mov_codigo, mov_ean, mov_descricao, mov_qtde, mov_valor_unit, mov_total;
EXIT WHEN mov_cur%NOTFOUND;
utl_file.PUT_LINE(arquivo_saida,
mov_codigo || ',' || mov_ean || ',' ||
mov_descricao || ',' || mov_qtde || ',' ||
mov_valor_unit || ',' || MOV_TOTAL);
END LOOP;
utl_file.PUT_LINE(arquivo_saida,
'9' || ',' || MOV_MOEDA || ',' || MOV_NOTA || ',' ||
MOV_TOT);
END LOOP;
UTL_File.Fclose(arquivo_saida);
CLOSE c1;
-- END;

end movimento;
Re: PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2) [message #676375 is a reply to message #676372] Wed, 05 June 2019 10:25 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum. Please read and follow How to use [code] tags and make your code easier to read?

Given you're on 11g the first parameter to fopen should be the name of an oracle directory object that points to the directory on the file server (and the directory should be on the same server as the DB).

What you've posted shouldn't give that error though. If anything I would expect ORA-29280: invalid directory path.
Are you sure the code you've posted is what is throwing the error?
What's the full error stack?


Re: PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2) [message #676377 is a reply to message #676375] Wed, 05 June 2019 11:14 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The first argument in fopen in 11g is the name of a directory object, not a path.
Re: PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2) [message #676389 is a reply to message #676372] Thu, 06 June 2019 06:31 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
The error message clearly states there is a problem with the arguments provided to fopon (utl_file.fopen).
So the first thing you should have done is checked the docs for fopen to confirm that what you supplied is what is expected.

https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70919

And there we read:

Quote:

location Directory location of file. This string is a directory object name and must be specified in upper case. Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN.
(emphasis mine).

A 'directory object name' is not the actual name of the OS directory. It is a "directory object". An object created within the database, that acts as a pointer to the os directory.
See https://docs.oracle.com/database/121/SQLRF/statements_5008.htm#SQLRF01207 for more detail on directory objects.
Re: PLS-00306: wrong number or types of arguments in call to 'FOPEN' (CM merged 2) [message #676390 is a reply to message #676389] Thu, 06 June 2019 06:43 Go to previous message
maik2910
Messages: 2
Registered: June 2019
Junior Member
Thanks, I identified the problem, it was just the quotation marks of the line (output_file: = UTL_File.Fopen ('c: \ reports', 'file.txt', 'w');
Previous Topic: Combining 3 queries into 1
Next Topic: Aggregate operation on array
Goto Forum:
  


Current Time: Thu Mar 28 16:31:30 CDT 2024