PL/SQL Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: PL/SQL Procedure

  1. #1
    Join Date
    Jul 2005
    Posts
    2

    PL/SQL Procedure

    Hello everyone! I have a produre that I can not get to compile. I keep getting the message "Warning: Procedure created with compilation errors." I'm not getting any help when I type "show errors" Below is my code and input.txt file is attached.
    -----------------------------------------------------------------
    CREATE OR REPLACE PROCEDURE insert_rec
    (p_FileDir in Varchar2, p_FileName Varchar2)
    as
    --Declare
    V_count Number;
    V_cust_num Number(6);
    V_cust_name Varchar2(25);
    V_cust_addr Varchar2(25);
    V_city Varchar2(20);
    V_cust_zip Number(5);
    V_Cred_Lim Number(1);
    V_Bal_Due Number(7);
    V_Cust_Phone Char(12);
    V_Rec_Type Number(1);
    V_Valid Number(1);
    V_Line Varchar2(20);
    V_FileHandle UTL_FILE.FILE_TYPE;

    BEGIN
    V_FileHandle := UTL_File.Fopen (P_FileDir, P_FileName,'r');

    LOOP
    BEGIN
    UTL_File.Get_Line(V_FileHandle, V_Line);

    EXCEPTION
    When No_Data_Found Then Exit;
    END;


    V_cust_num := substr(V_Line, 1, 6);
    V_cust_name := substr(V_Line, 7, 25);
    V_cust_addr := substr(V_Line, 32, 25);
    V_city := substr(V_Line, 57, 20);
    V_cust_zip := substr(V_Line, 77, 5);
    V_Cred_Lim := substr(V_Line, 82, 1);
    V_Bal_Due := substr(V_Line, 83, 7);
    V_Cust_Phone := substr(V_Line, 90, 12);
    V_Rec_Type := substr(V_Line, 102, 1);

    Select count(*) into V_Valid
    From cust_entry
    Where cust_number = V_cust_num;

    IF V_Valid := 1 THEN
    INSERT INTO CUST_ENTRY
    VALUES(V_cust_num, V_cust_name, V_cust_addr, V_city, V_cust_zip, V_Cred_Lim, V_Bal_Due, V_Cust_Phone, V_Rec_Type);
    END IF;

    END LOOP;

    UTL_File.FClose(V_FileHandle);

    END;
    /
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,973
    Does the schema owner have a direct grant for utl_file? Look at dba_dependencies to see what if any dependencies that you might be missing.
    this space intentionally left blank

  3. #3
    Join Date
    Jul 2005
    Posts
    2
    dba_dependencies? I'm not sure what you mean. I just logged on to oracle using scott tiger.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Shouldn't
    Code:
    IF V_Valid := 1 THEN
    be
    Code:
    IF V_Valid = 1 THEN
    ?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Bug alert

    INSERT INTO CUST_ENTRY
    VALUES(V_cust_num, V_cust_name, V_cust_addr, V_city, V_cust_zip, V_Cred_Lim, V_Bal_Due, V_Cust_Phone, V_Rec_Type);
    IMHO doing an insert without the column list specified is a bug waiting to happen.

    Cheers

    Tim...
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  6. #6
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    I'm not getting any help when I type "show errors"
    What does it say exactly?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width