Code: Select all

-- Create directory dirpath
create or replace directory dirpath as 'D:\ltxuan\Docs';
grant read on directory dirpath to public;
-- Read text file
set serveroutput on
DECLARE
  vLocation           VARCHAR2(100)  := 'dirpath';
  vFilename           VARCHAR2(100) := '021210_ACQ_RES_MHB9704011.dat';
  vTio             utl_file.FILE_TYPE;
  vLinebuf         VARCHAR2(2000);
  vRownum         NUMBER        := 0;
  -- use array to store data FROM each line of the text file   
  TYPE       array_type IS VARRAY(15) OF VARCHAR2(100);
  vColumn    array_type := array_type('');
  --procedure open file
  PROCEDURE prc_open_file(p_filename IN VARCHAR, p_access IN VARCHAR2) is
  BEGIN
    vTio := utl_file.FOPEN(vLocation,p_filename,p_access);
  EXCEPTION
    WHEN OTHERS then
      raise_application_error(-20000,'Unable to open '||vLocation||p_filename);
      --message(sqlerrm);pause;
  END;
  --procedure close file
  PROCEDURE prc_close_file is
  BEGIN
    IF utl_file.IS_OPEN(vTio) then
       utl_file.FCLOSE(vTio);
    END IF;
  END;
   
BEGIN
     
  --extend AND initialize the array to 4 columns
  vColumn.EXTEND(4,1);
  dbms_output.put_line('dirpath');
  dbms_output.put_line(vFilename);
  prc_open_file(vFilename,'r');
  LOOP
      utl_file.GET_LINE(vTio,vLinebuf);

      vColumn(1)  := SUBSTR(vLineBuf, 1, 16);
      vColumn(2)  := SUBSTR(vLineBuf, 17, 6);
      vColumn(3)  := SUBSTR(vLineBuf,23,12);     

      Insert Into MySampleTable
      Values
        (vColumn(1), vColumn(2), vColumn(3));

      EXIT WHEN vLinebuf IS NULL;

  END LOOP;
  prc_close_file;
END;
/

Code: Select all

create or replace directory succeeded.
grant read succeeded.

Error starting at line 6 in command:
DECLARE
  vLocation           VARCHAR2(100)  := 'dirpath';
  vFilename           VARCHAR2(100) := '021210_ACQ_RES_MHB9704011.dat';
  vTio             utl_file.FILE_TYPE;
  vLinebuf         VARCHAR2(2000);
  vRownum         NUMBER        := 0;
  -- use array to store data FROM each line of the text file   
  TYPE       array_type IS VARRAY(15) OF VARCHAR2(100);
  vColumn    array_type := array_type('');
  --procedure open file
  PROCEDURE prc_open_file(p_filename IN VARCHAR, p_access IN VARCHAR2) is
  BEGIN
    vTio := utl_file.FOPEN(vLocation,p_filename,p_access);
  EXCEPTION
    WHEN OTHERS then
      raise_application_error(-20000,'Unable to open '||vLocation||p_filename);
      --message(sqlerrm);pause;
  END;
  --procedure close file
  PROCEDURE prc_close_file is
  BEGIN
    IF utl_file.IS_OPEN(vTio) then
       utl_file.FCLOSE(vTio);
    END IF;
  END;
   
BEGIN
     
  --extend AND initialize the array to 4 columns
  vColumn.EXTEND(4,1);
  dbms_output.put_line('dirpath');
  dbms_output.put_line(vFilename);
  prc_open_file(vFilename,'r');
  LOOP
      utl_file.GET_LINE(vTio,vLinebuf);

      vColumn(1)  := SUBSTR(vLineBuf, 1, 16);
      vColumn(2)  := SUBSTR(vLineBuf, 17, 6);
      vColumn(3)  := SUBSTR(vLineBuf,23,12);     

      Insert Into MySampleTable
      Values
        (vColumn(1), vColumn(2), vColumn(3));

      EXIT WHEN vLinebuf IS NULL;

  END LOOP;
  prc_close_file;
END;
Error report:
ORA-20000: Unable to open dirpath021210_ACQ_RES_MHB9704011.dat
ORA-06512: at line 16
ORA-06512: at line 33
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
dirpath
021210_ACQ_RES_MHB9704011.dat

Pooh wrote:Tôi mới chập chững học sử dụng PL/SQL Developper, xin các bạn giúp dùm.
Trong SQL window, tôi gõ đoạn code như sau :

CREATE OR REPLACE PROCEDURE my_proc2
IS
BEGIN
select * from ten_table where dieu_kien
End my_proc2;
/

Sau đó tôi bấm F8, trong Procedure có xuất hiện my_proc2 rồi nhưng có dấu gạch chéo đỏ phía trước, tôi bấm phải chuột chọn Properties thì thấy Status là INVALID
Mong các bạn giải thích dùm tôi, cấu lệnh trên tôi viết sai chổ nào ?
Thanks

Code: Select all

create or replace procedure my_proc2 is

cursor abc is
select * from ten_table where dieu_kien;
begin
  for r in abc loop
      dbms_output.put_line(r.ten_field);
  end loop;
end; 

darkan wrote:@ Pooh : Bạn phải set serveroutput on thì dòng kia nó mới hiện ra.