Results 1 to 7 of 7

Thread: sql assignment

  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: sql assignment

    Hi all, I am new here, and am running into some problems. I will say I missed quite a few days of class being sick and needing anti biotics, so please dont make fun of me

    my assignment is this


    Use the mtl_system_items_interface table in the INV module

    Allow the input of 2 items. Make sure these items get assigned to the Master Inventory Org and to YOUR Inventory Org.



    Create an anonymous block to insert into mtl_system_items_interface
    o Allow the user to enter the item and description. When user enters Make sure the item and description are entered with in the input field.
    o Use system date for the creation date


    the template the teacher provided is this


    a) Please use the following insert script:
    insert into mtl_system_items_interface
    (organization_id,
    creation_date,
    created_by,
    description,
    segment1,
    process_flag,
    transaction_type,
    set_process_id)
    values
    ( 204,
    sysdate,
    0,
    'Test Items',
    '988-test1',
    1,
    'CREATE',
    989);
    Commit;



    she said we can do it however we wanted, right now im just trying to get the
    insert statement right. here is what i have

    SET VERIFY OFF
    SET SERVEROUTPUT ON
    DECLARE
    item mtl_system_items_interface.segment1%TYPE;
    new_description mtl_system_items_interface.description%TYPE;
    new_creation mtl_system_items_interface.creation_date%TYPE;
    BEGIN
    item := &item;
    new_description := &new_description;

    SELECT VALUES(segment1, creation_date, description)
    FROM mtl_system_items_interface
    WHERE segment1 = item;

    FOR i IN 1..2 LOOP
    INSERT INTO mtl_system_items_interface (segment1, new_creation, description)
    VALUES(item, SYSDATE, new_description);
    END LOOP;
    /*UPDATE mtl_system_items_interface;

    SET segment1 = item,
    creation_date = SYSDATE,
    description = new_description
    WHERE segment1 = item;*/
    END;

    Error report:
    ORA-06550: line 9, column 12:
    PL/SQL: ORA-00936: missing expression
    ORA-06550: line 9, column 5:
    PL/SQL: SQL Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:

    any thoughts on how to proceed?been stuck on just the insert for awhile.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    using ORA & PL/SQL then probably best to ask this in the Oracle forum
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    And please use [code] tags to make your SQL easier to read.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm not sure why you wrote all that stuff. If you have to insert a record, then simply enclose INSERT within BEGIN - END (so that it'll be a PL/SQL anonymous block).

    What is SELECT used for? Syntax is wrong, there's no "SELECT VALUES", but "SELECT INTO" (when you use it in PL/SQL). INSERT has VALUES if you name values; if you insert them using SELECT statement, no VALUES is to be used.

    What is FOR loop doing here? Why would you want to insert two records?

    Take notes from other students in order to get familiar with everything they did while you were absent. Furthermore, you should check documentation in order to learn valid syntax (not the whole document - you (most probably) learnt only basic stuff).

    Once you manage to produce code that compiles, you're half way through that homework assignment. If you still can't make it work, do come back, show us what you did. We'll assist.

  5. #5
    Join Date
    Dec 2010
    Posts
    3
    Thanks for the replys everyone, I did talk with some people in my class and they did give me the notes I missed. I was on the wrong track. Thank you to anyone else who even just viewed this also.

  6. #6
    Join Date
    Dec 2010
    Posts
    3
    Code:
    SET VERIFY OFF
    SET SERVEROUTPUT ON
    DECLARE 
      item            mtl_system_items_interface.segment1%TYPE;
      new_description mtl_system_items_interface.description%TYPE;
      new_creation    mtl_system_items_interface.creation_date%TYPE;
      BEGIN
    
    
      item := &item; 
      new_description := &new_description;
    insert into mtl_system_items_interface 
    (organization_id, creation_date, created_by, description, 
    segment1, process_flag, transaction_type, set_process_id) 
    values ( 204, sysdate, 0, new_description, item , 1, 'CREATE', 989);
      Commit; 
    
    
    END;
    I figured out the part about inserting now the way our instructor wanted it. The problem I am running into now is, she wanted us to put in 2 items, my for loop is not working, and I don't really have a good example of one. Can anyone send me the link for the documentation please? I need the iteration to run 2 times, so I can use the cursor to input 2 items, and 2 descriptions. Right now with the code above it works perfectly for inputting 1 item. I still can't seem to find a for loop that I can get working.

    Code:
    SET VERIFY OFF
    SET SERVEROUTPUT ON
    DECLARE 
      item            mtl_system_items_interface.segment1%TYPE;
      new_description mtl_system_items_interface.description%TYPE;
        BEGIN
       item := &item; 
      new_description := &new_description;
    insert into mtl_system_items_interface 
    (organization_id, creation_date, created_by, description, 
    segment1, process_flag, transaction_type, set_process_id) 
    values (204, sysdate, 0, new_description , item , 1,
    'CREATE', 989);
      Commit; 
       item := &item; 
      new_description := &new_description;
    insert into mtl_system_items_interface 
    (organization_id, creation_date, created_by, description, 
    segment1, process_flag, transaction_type, set_process_id) 
    values (204, sysdate, 0, new_description , item , 1,
    'CREATE', 989);
      Commit; 
    
    END;
    This code will do exactly what I need it to do, my instructor did not specify if we needed a loop, so im not sure i shold even use one now? But can anyone show me what the loop would look like for iterating the item:= &item, with new_description = &new_description? Thanks in advance.
    Last edited by iluvpie60; 12-16-10 at 19:13.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    FOR loop would insert the same item twice - I don't think you are supposed to use it.

    So, either run a PL/SQL block (that inserts a single item) twice (in SQL*Plus, you can do that simply by entering a "slash" (/) - it runs the last statement once again), or insert them using two INSERT statements.

    Running it twice:
    Code:
    SQL> create table test (item number, created date);
    
    Table created.
    
    SQL> begin
      2    insert into test
      3      (item, created)
      4    values
      5      (&item, sysdate);
      6  end;
      7  /
    Enter value for item: 100
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    Enter value for item: 200
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test;
    
          ITEM CREATED
    ---------- -------------------
           100 17.12.2010 08:23:40
           200 17.12.2010 08:23:47
    
    SQL>
    Using two INSERT statements:
    Code:
    SQL> rollback;
    
    Rollback complete.
    
    SQL> begin
      2    insert into test
      3      (item, created)
      4    values
      5      (&item, sysdate);
      6
      7    insert into test
      8      (item, created)
      9    values
     10      (&item, sysdate);
     11  end;
     12  /
    Enter value for item: 300
    Enter value for item: 400
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test;
    
          ITEM CREATED
    ---------- -------------------
           300 17.12.2010 08:24:56
           400 17.12.2010 08:24:56
    
    SQL>
    This is Oracle 10g documentation. You'll need SQL Reference book (you'll find it in the Most Popular section). All books are available on "Books" tab (it's on the top of the page)

Tags for this Thread

Posting Permissions

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