Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006

    Unanswered: Oracle Stored Procedure to read a table and write to another table

    Hi all,

    I am in the process of learning about Stored Procedures in Oracle ,(version 9) and wondered if someone can show me how I can write a stored procedure to read a simple table, loop through each row (checking each field's length) and then writing the results to a new table.

    For example
    a table, table1 has 3 columns: ID, Col1, Col2
    the SP needs to read table1
    cycle through each row and check the lengths of Col1 and Col2 in turn
    If Col1 OR Col2 are less than 5 charcters long, it shoud skip to the next row.
    If Col1 AND Col2 are 5 characters or longer then the entire row should be written to table2 (which has the same field names)

    I appreciate any help you can offer.


  2. #2
    Join Date
    Mar 2007
    There is no need to use a cursor loop, you may use a single INSERT statement (which may be used in procedure):
    INSERT INTO table2( ID, Col1, Col2 )
    SELECT ID, Col1, Col2
    FROM table1
    WHERE LENGTH(Col1) >= 5 AND LENGTH(Col2) >= 5;
    If this is a homework on cursor usage, please re-read the materials you have; alternatively you may study PL/SQL User's Guide and Reference book, available with all Oracle documentation e.g. online on There is also a section, how to create a procedure.

  3. #3
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    You don't need an SP for that:
    insert into table2 (id, col1, col2) 
      select id, col1, col2 from table1
      where length(col1) > 4 and length(col2) > 4
    One thing to learn about stored procedures is that they should not be used when a simple SQL statement is sufficient.
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2006
    sorry guys,
    my example was just a simplified version of a more complicated job - just so I can get an idea of the syntax involved in loops and comparisons. I didn't want to make my post too complicated!

    What I actually need to do,
    is read several rows from one table.
    compare 5 columns from that table with another table to get a 'best match' (where NULL values are considered wildcards and definite non-matching columns result in the entire row to be deemed a non-match)
    Then with that best match ID, link to a 3rd table to retrieve 2 account numbers fro each of the original tables rows - and then write to a new table (oh, and produce a text file!)


  5. #5
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    Post DDL for tables.
    Post DML for test data.

    Post expected/desired results.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Dec 2003
    Here's some crude pseudo-code to get you started. Writing to a file will require a DIRECTORY OBJECT and the UTL_FILE package, but attack that in a second step. Get the following working first (read from 2 tables, query a 3rd, then write to a table):

      cursor two_tbl_csr is
      select t1.f1,t1.f2,t1.f3,t1.f4,t1.f5
      from table1 t1, table 2 t2
      where t1.f1=nvl(t2.f1,t1.f1) and     -- if the t2 field is null, set it 
            t1.f2=nvl(t2.f2,t1.f2) and     -- equal to the value from t1. That's
            t1.f3=nvl(t2.f3,t1.f3) and     -- probably not exactly what you need..
            t1.f4=nvl(t2.f4,t1.f4) and     -- but it's my stab at your "treat the
            t1.f5=nvl(t2.f5,t1.f5);        -- nulls like wildcards"
      l_acct_num1 number(10);
      l_acct_num2 number(10);
      for two_tbl_row in two_tbl_csr
        select acct_num1, acct_num2
        into   l_acct_num1, l_acct_num2
        from   table3 t3
        where  fk = two_tbl_row.f1; 
        insert into other_table (f1, f2) values (l_acct_num1, l_acct_num2);
      end loop;

Posting Permissions

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