• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

how to put a declaration in a procedure?

Hello all,

I have a function that runs a select statement in oracle and mount a. txt file on my server but a business user will need to run this function and I need to place it inside a procedure and give it only permission to run this procedure. The part of creation and user permission has already been made just do not know how to put the entire command line in a procedure to be performed, following the parameters below:


declare
    ARQUIVO            UTL_FILE.FILE_TYPE;
    
    CURSOR c_Item IS
      select 
         '1' ||                                      -- Tipo de Registro
         rpad(c.cdcid,10,' ') ||                           -- Cid
         rpad(c.nocid,70,' ')                              -- Descricao do Cid
         AS reg
       from 
         cid_10 c;
begin
    ARQUIVO := UTL_FILE.FOPEN('/opt/oracle/oradata/PSController','v80_cid.txt','w');

    UTL_FILE.PUT_LINE(ARQUIVO, '018.0     ');
    FOR r_Item IN c_Item LOOP
      UTL_FILE.PUT_LINE(ARQUIVO, r_ITEM.reg);
    END LOOP;

    UTL_FILE.FCLOSE(ARQUIVO);

    EXCEPTION

  	WHEN UTL_FILE.INVALID_PATH THEN
  		DBMS_OUTPUT.PUT_LINE('Caminho Invalido!');
  		UTL_FILE.FCLOSE(ARQUIVO);

  	WHEN UTL_FILE.READ_ERROR THEN
  		DBMS_OUTPUT.PUT_LINE('Erro durante a Leitura.');
  		UTL_FILE.FCLOSE(ARQUIVO);

  	WHEN UTL_FILE.WRITE_ERROR THEN
  		DBMS_OUTPUT.PUT_LINE('Erro durante a Escrita.');
  		UTL_FILE.FCLOSE(ARQUIVO);

  	WHEN OTHERS THEN
  		DBMS_OUTPUT.PUT_LINE('Ocorreu um erro desconhecido!');
  		UTL_FILE.FCLOSE(ARQUIVO);

end;

Open in new window

0
eduardo12fox
Asked:
eduardo12fox
  • 2
  • 2
1 Solution
 
eduardo12foxAuthor Commented:
I RESOLVED
0
 
zzynxSoftware engineerCommented:
Then please close this question by accepting your own comment
0
 
slightwv (䄆 Netminder) Commented:
>>Then please close this question by accepting your own comment

No.  Please delete the question.
0
 
zzynxSoftware engineerCommented:
Of course, slightwv, you're right.  
Since the author didn't post the solution it's useless to PAQ.
0
 
eduardo12foxAuthor Commented:
create or replace procedure producao.gera_arquivo_CID_versao_80 is

    ARQUIVO            UTL_FILE.FILE_TYPE;
   
    CURSOR c_Item IS
   
   
            select
               '1' || rpad(c.cdcid,10,' ') || rpad(c.nocid,70,' ') AS reg
             from cid0 c;
         
BEGIN
     ARQUIVO := UTL_FILE.FOPEN('/opt/oracle/oradata/TESTE','v80_AAA.txt','w');

    UTL_FILE.PUT_LINE(ARQUIVO, '018.0     ');
    FOR r_Item IN c_Item LOOP
      UTL_FILE.PUT_LINE(ARQUIVO, r_ITEM.reg);
    END LOOP;

    UTL_FILE.FCLOSE(ARQUIVO);

    EXCEPTION

        WHEN UTL_FILE.INVALID_PATH THEN
              DBMS_OUTPUT.PUT_LINE('Caminho Invalido!');
              UTL_FILE.FCLOSE(ARQUIVO);

        WHEN UTL_FILE.READ_ERROR THEN
              DBMS_OUTPUT.PUT_LINE('Erro durante a Leitura.');
              UTL_FILE.FCLOSE(ARQUIVO);

        WHEN UTL_FILE.WRITE_ERROR THEN
              DBMS_OUTPUT.PUT_LINE('Erro durante a Escrita.');
              UTL_FILE.FCLOSE(ARQUIVO);

        WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Ocorreu um erro desconhecido!');
              UTL_FILE.FCLOSE(ARQUIVO);


end;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now