Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Posts
    38

    Unanswered: insert value into transaction

    Hi:
    In a transaction, how could the transaction ask the user to enter a value, then store it into a table.

    Regards,
    Frenk
    Last edited by fj8283888; 05-17-04 at 12:45.

  2. #2
    Join Date
    Apr 2004
    Posts
    38

    Transaction

    Pls Let Me Know If It Is Some Error From The Topic

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Transactions don't ask users for input, it is application programs that do that. Your application could be Oracle Forms, Visual Basic, Pro*C or whatever.

  4. #4
    Join Date
    Apr 2004
    Posts
    38

    Transaction

    Tony thanks for reply
    the code is one of my testing
    Code:
    declare
    
            CID Catch.CatchID%type;
            FID Fish.FishID%type;
            PID Purchaser.PurchaserID%type;
            SID Sale.SaleID%type;
            CSH DealerCash.Cash%type;
    
    begin
       select FishID into FID from Fish where FishType = '&FishType';
       select PurchaserID into PID from Purchaser where PurchaserName = '&PurchaserName';
       CID := &Catchid;
       select SaleID into SID from Sale where catc
    the code above can request use to enter values?
    Regards,
    Frenk

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, there the client application (SQL Plus) is prompting for values for FishType and PurchaserName. However, SQL Plus does not make a good end-user application - it is really only useful for running batch jobs and for testing code. For example, you cannot repeatedly prompt the user for input inside a loop in SQL Plus, which you can in any of the programming environments I mentioned earlier.

    Bear in mind that it is SQL Plus (an application) that is prompting the user for input here, it is not the database that is doing it.

  6. #6
    Join Date
    Apr 2004
    Posts
    38

    Transaction

    Tony:

    Hi it's me again, I try to put the code into ORACLE, I add the end command at the end of the source code, but the system did not stop, until I press Ctrl^C, could you please teach me how to fix it.

    Regards,
    Frenk

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your code appears incomplete above. It should end like this:

    ...
    END;
    /

    The END; defines the end of the PL/SQL block to be executed, and the / tells SQL Plus to execte the block.

  8. #8
    Join Date
    Apr 2004
    Posts
    38

    Transaction

    SQL> declare
    2 FID Fish.FishID%type;
    3 begin
    4 select fishid into FID from fish where fishtype='&fishtype';
    5 end;



    i typed the code as above, but it is not working?
    Regards,
    Frenk
    Last edited by fj8283888; 05-17-04 at 14:32.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Did you also type in the "/" on its own on a line? Otherwise, SQL Plus is just waiting for further instructions from you.

  10. #10
    Join Date
    Apr 2004
    Posts
    38

    Transaction

    Code:
    SQL>  declare
      2  FID Fish.FishID%type;
      3  begin
      4  select fishid into FID from fish where fishtype='&fishtype';
      5  end;
      6  /
    Enter value for fishtype: Shark
    old   4: select fishid into FID from fish where fishtype='&fishtype';
    new   4: select fishid into FID from fish where fishtype='Shark';
    
    PL/SQL procedure successfully completed.
    I want to display the fish id, how could I do it?

    Regards,
    Frenk

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Dbms_output.put_line
    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.

  12. #12
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    anacedent is right. the dbms_output will do the job.

    If you need an example, take a look at it: Output run time information from Oracle PL/SQL code

    however in your case if the query returns more than one row you cannot do it

    select fishid into FID from fish where fishtype='&fishtype';
    that's why for every query you MUST use cursors, then loop through it to do the inserts and eventually the dbms_output. for example:

    Code:
    create or replace procedure fishy (v_fishtype varchar2)
    is 
    cursor fish
     is select fishid from fish where fishtype=UPPER(v_fishtype);
    begin
      for fish_rec into fish loop
         dbms_output.put_line("Fish ID is:" || TO_CHAR(fish_rec.fishid));
       end loop;
    end;
    Then run your procedure from SQL*PLus:

    SQL> exec fushy(&fishtype);


    PS. The above is an example. It's not tested or complete. You should learn to write your own code and test it properly


    HTH,

    clio_usa - OCP 8/8i/9i DBA
    Last edited by clio_usa; 05-17-04 at 23:00.

Posting Permissions

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