Newbie PLSQL devekoper needs help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Newbie PLSQL devekoper needs help

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    Newbie PLSQL devekoper needs help

    Hi. these are my requirements

    1.read a csv file that has 3 fields
    employee_id,department_name,subdepartment_name

    2.compare that data with table sop_departments that has columns: department_name,
    subdepartment_name and employee_id from sop_employee

    Compare the data. if the csv file has a new record then insert it into sop_employees

    insert it into sop employee: values employee_id and department_id from (sop_departments).

    I started putting the pl sql together but I am not good with pl sql
    =============================================================
    CREATE OR REPLACE PROCEDURE LOAD_SOP_DATA IS

    file_handle UTL_FILE.FILE_TYPE
    department_name CHAR;
    subdepartment_name CHAR;
    retrieved_buffer VARCHAR2(100);


    BEGIN

    CURSOR COMPARE

    IS
    select department_name, subdepartment_name
    ...............
    ...............
    ..............
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('no_data_found');
    UTL_FILE.FCLOSE(file_handle);
    WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
    UTL_FILE.FCLOSE(file_handle);
    WHEN UTL_FILE.READ_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
    UTL_FILE.FCLOSE(file_handle);
    END;

    Thanks in advance for help
    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    1) You don't need a cursor on the table.
    2) You need to open the file, read the first line and declare a while loop that exits when you get no data found.
    3) Inside the above loop do an update that updates the table if the records are newer.
    4) close the file
    5) include an exception section that closes the file and reraises any error.

    If you really want to learn PL/SQL get a good PL/SQL book and write the procedure. Doing is the best form of learning.

  3. #3
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    thats fine but for now can i pls get some help. its urgent and it will take me some time.
    "High Salaries = Happiness = Project Success."

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    There is no need to write pl/sql code at all...

    1)create a table called sop-t table
    2)load the records into this table
    3)write your insert statement as follows
    insert into sop_employee
    select department_name,subdepartment_name ,employee_id from sop_employee
    minus
    select department_name,subdepartment_name ,employee_id from sop-t

    truncate sop-t table and then later drop it..

    regards
    Hrishy

  5. #5
    Join Date
    Jun 2001
    Posts
    40
    It you do decide you want to go the PL/SQL route you want want to visit this URL:

    http://www.quest-pipelines.com/Pipel...s/Gen_util.sql

    It's a PL/SQL package you can download that parses a line from a CSV file and returns it as a nested table array. I reckon it could make life considerably simpler.

    Hope it helps.

    Austin

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