2 Replies Latest reply: Apr 12, 2007 12:49 AM by Billy~Verreynne RSS

    Taking user input in a PL/SQL block

    569820
      Can someone tell me if it is possible to to take user input from within a PL/SQL loop? I mean I want that for each iteration in the loop, the program first take user input, and then based on that input, it perform specific operations. Then it repeats this process for each subsequent iteration of loop.

      If it is not possible in PL/SQL, is there any other alternative?

      Thanks.
        • 1. Re: Taking user input in a PL/SQL block
          Hans Forbrich
          Well .... except for Forms and Reports, PL/SQL runs in the server. You know ... way over there in the data center.

          I'm afraid I have no idea how to get the server to ask questions of the user, especially when it essentially operates in a kind of batch mode.

          Perhaps you could use some tool installed on the user's machine - some client utility such as SQLPlus - to ask the questions and pass them on to the server. In which case you might be looking at, say 'substitution variables' or other 'variables' as may be documented in some SQLPlus User manual on a site like http://www.tahiti.com
          • 2. Re: Taking user input in a PL/SQL block
            Billy~Verreynne
            > I'm afraid I have no idea how to get the server to ask questions of the user,
            especially when it essentially operates in a kind of batch mode.

            Yep. To expand a tad on what Hans mentioned here.

            To accept input, the process needs to be able to read from this input device. Let's make it a keyboard. The PL/SQL process needs to read keyboard input.

            The keyboard is attached to a PC 1000km away in a branch office. The Oracle server process, running on a server in the data centre, executes this PL/SQL code.

            So now this PL/SQL process has to automagically hack its way across the network. It needs to remove the keyboard plug from that client PC. It somehow must get that keyboard rewired, across that 1000km, into the server platform at the data centre. And only then can the Oracle server process running that PL/SQL code read from that keyboard input device and accept user input.

            It is important, when developing in Oracle, to grasp just what client-server is and how it works. And not to confuse something like a SQL*Plus Client Process (submitting PL/SQL code) with the Oracle Server Process (parsing and executing that submitted PL/SQL code).