Home » SQL & PL/SQL » SQL & PL/SQL » Error (Oracle 9i)
Error [message #438702] Mon, 11 January 2010 22:46 Go to next message
srikkanthms86
Messages: 6
Registered: January 2010
Location: chennai
Junior Member
Hai
I have created a procedure for reading a flat text file using utl_file.The coding is

create or replace procedure text_copy(test varchar)
as
declare
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
begin
vSFile := utl_file.fopen('AUTO_MAIL', 'test','r');
IF utl_file.is_open(vSFile) THEN
LOOP
utl_file.get_line(vSFile, vNewLine);
IF vNewLine IS NULL THEN
EXIT;
END IF;
INSERT INTO test
(fld1, fld2)
VALUES
(vNewLine, 'test.txt');
end loop;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No data found');
END;
utl_file.fclose(vSFile);
END;
/
show err;
--select * from test;

and i have one error.


PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function package pragma procedure subty pe type use <an identifier> form current cursor external language The symbol "begin" was substituted for "DECLARE" to continue.

Pls try to rectify my problem...


Regards
Srikkanth.M
  • Attachment: ss.txt
    (Size: 0.96KB, Downloaded 103 times)
Re: Error [message #438705 is a reply to message #438702] Mon, 11 January 2010 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


CREATE OR REPLACE PROCEDURE Text_copy
     (test  VARCHAR)
AS
  vsfile    utl_file.file_type;
  vnewline  VARCHAR2(200);
BEGIN
  vsfile := utl_file.Fopen('AUTO_MAIL','test','r');
  
  IF utl_file.Is_open(vsfile) THEN
    LOOP
      utl_file.Get_line(vsfile,vnewline);
      
      IF vnewline IS NULL THEN
        EXIT;
      END IF;
      
      INSERT INTO test
                 (fld1,
                  fld2)
      VALUES     (vnewline,
                  'test.txt');
    END LOOP;
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.Put_line('No data found');
END text_copy;
/ 
Re: Error [message #438747 is a reply to message #438702] Tue, 12 January 2010 01:09 Go to previous message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DECLARE is the beginning of an ANYNONYMOUS PL/SQL block and is not part of a stored procedure syntax.

Application Developer's Guide - Fundamentals

Regards
Michel

Previous Topic: joining 3 tables - how Oracle decides about execution plan
Next Topic: Triggers
Goto Forum:
  


Current Time: Sun Sep 25 23:12:26 CDT 2016

Total time taken to generate the page: 0.13392 seconds