Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Exclamation Unanswered: i need serious help... URGENTLY

    please can someone just teach me how to accept users option/choice and through the choice, certain procedures would be carried out? sounds simple to me.. but i know nothing abt the syntax... how to incorporate sql*plus to pl/sql.

    the one below (which is a test draft incurred a compilation error)
    -- HEAD
    CREATE OR REPLACE
    PACKAGE order_object as
    PROCEDURE choose_option;
    -- PROCEDURE add_record;
    END;

    --BODY
    CREATE PACKAGE BODY order_object as
    PROCEDURE choose_option
    IS
    DECLARE
    -- local variables
    --user_choice NUMBER;
    BEGIN
    dbms_output.putline('=====Please enter choice below=====');

    dbms_output.putline('<< Press 1 to enter order with 1 product only >>');
    dbms_output.putline('<< Press 2 to enter order with multiple products >>');
    commit;
    END;
    END order_object;

    please assist this newbie. soon. thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    2
    syntax for body is this .....

    create or replace
    PACKAGE BODY order_object is
    PROCEDURE choose_option is
    user_choice NUMBER;
    BEGIN
    dbms_output.put_line('=====Please enter choice below=====');

    dbms_output.put_line('<< Press 1 to enter order with 1 product only >>');
    dbms_output.put_line('<< Press 2 to enter order with multiple products >>');
    commit;

    END;

    END;

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    thanks!... well it works ..=)

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You now have a procedure that when run in SQL Plus with SERVEROUTPUT set ON produces some output prompting the user to press 1 or 2. Now what? The user types a 1 or 2 at the SQL> prompt and ...? SQL Plus will display line 1 or line 2 of the last SQL executed!

    SQL Plus is not a realistic choice of user interface for an application. You should write the application in something like Oracle Forms, VB, Pro*C or whatever, that allows true user interaction.

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    =) I know that... this is just an assignment... learning pl/sql and the packages... so i don't quiteknow how to create a transaction apart from the normal sqlldr. its just a simple assignment... which i don't have the luxury of time ...=P and yeah. i still have probs now...
    ok so i need online input of all the attributes... using just the sql*plus. how? using accept and prompt?? and then pass it through the parameters of pl/sql?

    still not gettin a hang of this pl.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Best just not to think of SQL Plus as interactive, but "command driven", with just a little prompting possible. If you have a procedure like this:

    PROCEDURE order_one_product ( p_product_id, p_qty, p_customer_id )

    ...then you could create a SQL Plus script called run_order_one.sql to run it that looks like this:

    prompt You are ordering one product
    exec order_one_product( &product_id, &qty, &cutomer_id )

    Now the user can run it like this:

    SQL> @run_order_one

    ...and will be prompted like this:

    You are ordering one product
    Enter value for product_id:
    Enter value for qty:
    Enter value for customer_id:

    That's about as "interactive" as SQL Plus gets!

Posting Permissions

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