Read a file inside a package procedure in pl sql

Question

I am trying to read a text file inside a Pl Sql procedure but to no avail -- syntax errors it seems. What am I doing wrong? My suspicion is I am not declaring something where I should be. Here is the first path of the package body:

CREATE OR REPLACE PACKAGE BODY COP_DBO.PACKAGE_TEMPLATE

 AS
  --
  --*****************************************************************************************************
  -- Purpose: Just a template
  --
  -- Inputs:
  --      in_vSTR    String
  --
  -- Returns:
  --      None
  --
  -- Mod History:
  --      06/29/2016 KEvin Palmer - Created initial version of this procedure
  --
  -- Error Handling:
  --      An error is raised if errors are encountered building or executing the SQL.
  --
  --*****************************************************************************************************
f UTL_FILE.FILE_TYPE;
s VACHAR2(200);
BEGIN
f := UTL_FILE.FOPEN('\\sp0034avrt\winixdb$\cow\dev', 'certs_file.txt', 'R');

UTL_FILE.GET_LINE(f,s);

UTL_FILE.FLCOSE(f);

dbms_outpit.put_line(s);
end;




  sql_statments arr_sql_t := arr_sql_t(); --initialize a empty lis 
  --------------------------------------------------------------------------------
  /*                    PROCEDURE AND VARIABLE 
                        INITILIZATION FOR  COW_DATALOAD_V2
  /***************************************************************************/
  ------------------------------------------------------------------------------
  --*********** PUT YOUR LIST OF CERTS BELOW ******************

  v_certList arr_claims_t := arr_claims_t('3803617642',
                                          '3805126441',
                                          '3876849047',
                                          '3873116383',
                                          '3873306670',
                                          '3878876718');

  --COP VARIABLES---
  new_copId   NUMBER; --NEW COP ID 
  prod_copId  NUMBER; --PROD COP ID
  new_seq_id  NUMBER; --NEW SEQ ID  
  suppl_count NUMBER; --supplemental count

  v_SQL     VARCHAR2(7000);
  v_certLst VARCHAR2(2000);
  n_success NUMBER := 0; --Count of success found
  n_total   NUMBER := 0; --Total Records proccessed
  n_suppl   NUMBER := 0; --Total Records proccessed
  n_orders  NUMBER := 0; --Total lmso orders downloaded

  /*cop procedure*/
  PROCEDURE COP_DATALOAD_V2(arr_claims arr_claims_t,
                            arr_sql    arr_sql_t) AS
  BEGIN

After that begin I have two procedures. Everything after end for the file stuff is highlighted with some type of syntactic error. What am I doing wrong?

EDIT: How is this question a duplicate of that othre question? I don't see it. Sorry if it's obvious. I don't see the similarity at all.

You've got a few obvious mis-spellings. Just look around the line number that the compiler tells you and fix those words (VACHAR; FLCOSE etc). — Peter M., Jul 07 '16 at 00:44
Possible duplicate of [Array in IN() clause oracle PLSQL](http://stackoverflow.com/questions/15515772/array-in-in-clause-oracle-plsql) — Christian Palmer, Jul 07 '16 at 13:16
I fixed those errors but now it wont recognize the Procedure .Says it's an invalid statement. Furthermore, why doesn't the compiler point out these spelling mistakes? I am using toad. All the errors it gives me are almost always totally off the mark! — chickenman, Jul 07 '16 at 13:51

score 1 · Accepted Answer · answered Jul 13 '16 at 13:00

Typical package body declaration looks like this

create or replace package body package_name is

  var_name number;

  procedure proc_name is
  begin
    do_smth;
  end;

begin
   init_smth;
end ;

Variables, methods, initialization. In your code as opposite:

create or replace package body package_name is

  var_name number;

begin
   init_smth;
end ;

  var_name2 number;

  procedure proc_name is
  begin
...

Variables, initialization, variables again, methods. It looks like you should place pieces of code in right places

Read a file inside a package procedure in pl sql

1 Answers1