read user input in oracle procedure

[aomara]

I write a procedure with no paramters.
I would like to write a code within the procedure body that ask the user to enter a value for a parameter and then read this value so as to us it in calculation?
I thought "DBMS_OUTPUT.GET_LINE" or "ACCEPT" can help, but iam not sure that any one of them works or how to use it.
Can any one help ?

 

[Dagon]

You can't do this within a stored procedure. You can put the accept before the stored procedure and then pass the value the user enters as an input parameter.

DBMS_OUTPUT.GET_LINE is not used to get user input. It is used to retrieve the data stored in the buffer by DBMS_OUTPUT.PUT_LINE.

 

[SantaMufasa]

Aomara,

Unfortunately, Oracle has not done a good job creating a reasonable user interface for use with SQL*Plus and particularly PL/SQL. In the examples you mention, there are problems:
1) DBMS_OUTPUT.GET_LINE only retrieves data that resides in the OUTPUT buffer of the DBMS_OUTPUT package. It does not allow for/provide for user input from the keyboard. (Dumb, huh.)

2) ACCEPT might give you what you want under very limited circumstances. I presume you want to allow user input for each invocation of your stored procedure. If this is the case, the only way that ACCEPT will work is:
a) If the ACCEPT statement runs from a SQL*Plus script (since ACCEPT is a SQL*Plus command), and,
b) The procedure follows the ACCEPT command in the SQL*Plus script and is in the form of an anonymous PL/SQL block (not a STORED PROCEDURE).

This set of circumstances relates to nature of the ACCEPT statement. It produces a named literal, not a variable (...you cannot VARY the item such as "&x = &x+1"; you can only create a literal, named value with ACCEPT). Then you can refer to the named literal either in another SQL*Plus statement, a SQL statement, or a PL/SQL block. But (and this is a big "but") when you reference the named literal in any of those three venues, it becomes just that...a literal in your code as though you had typed it into the code itself. Oracle then processes code including your literal as part of the interpretation (read "compile") process. Therefore, the value of the literal is "permanent" during the life of the code. If the code is re-executed by re-running a script, and since the script is re-interpreted for each execution, it appears to do what you want.

The problem comes if/when you refer to the ACCEPT item in a STORED PROCEDURE. The value of the item compiles permanently into the code...Not what you want in this case.

I believe your easiest solution would be to create a procedure that does use IN arguments (parameters). Following is a sample illustrating the definition of a simple, contrived) stored procedure the uses parameters:
Section 1 -- Procedure definition:

create or replace procedure show_tax1 (state_hold varchar2, amt_hold number) is
	tax_rate_hold	number;
begin
	select tax_rate into tax_rate_hold from US_Sales_Tax_Rates where State_ID = 
		upper(state_hold);
	dbms_output.put_line('Sales tax on a purchase of $'||amt_hold||
		' in the state of '||
		state_hold||' = $'||amt_hold*tax_rate_hold||'.'); 
exception
	when others then
		dbms_output.put_line ('*** ERROR: Could not process calculation for state named: ['||
			state_hold||'].');
end;
/

Section 2 -- Sample invocation (of above stored procedure):

(Following code stored in script named "tt_113a.sql")
set echo off
set serveroutput on
accept state_in prompt "Enter U.S. state abbrev. where the purchase occurred: "
accept amt_in prompt "What was the amount of the purchase?: "
exec show_tax1 ('&state_in','&amt_in')

SQL> @tt_113a
Enter U.S. state abbrev. where the purchase occurred: ut
What was the amount of the purchase?: 10
Sales tax on a purchase of $10 in the state of ut = $.65.

But, if you say, "I write a procedure with no parameters..." and the procedure is a stored procedure, and you want different user input for each invocation of your stored procedure, then one method to achieve this is via global, persistent variables. Global persistent variables have the following behaviours:
a) are defined in the variable section of a PACKAGE definition.
b) are newly created during the first reference to the variable during the referencing session.
c) are visible only to the creating session.
d) persist (remain visible) during the entire session not matter how many scripts or blocks of code execute during that session.
e) are fully variable/modifiable.

Because I often need to use this concept, I have a standard, user-defined package that defines a couple of global, persistent variables of the most frequently used data types:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor "Dasages, LLC" makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM 
REM Please contact the author via email (dave@dasages.com) when 
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic 
REM  documentation with this script.]
REM **************************************************************
REM About this script/file:
REM
REM This is a generalized package to produce two persistent 
REM variables each for dates, numbers, and varchar variables.
REM The package also creates functions to return the values stored
REM in the persistent variables.
REM
REM **************************************************************
create or replace package dh_ws is
	ws_date1	date;
	ws_date2	date;
	WS_NUMBER1	number;
	WS_NUMBER2	number;
	ws_varchar1	varchar(2000);
	ws_varchar2	varchar(2000);
	function get_n (x in varchar2) return number;
	function get_d (x in varchar2) return date;
	function get_x (x in varchar2) return varchar2;
	pragma restrict_references(get_n,WNDS);
	pragma restrict_references(get_d,WNDS);
	pragma restrict_references(get_x,WNDS);
end;
/
create or replace package body dh_ws is
	function get_n (x in varchar2) return number is
	begin
		if upper(x) = 'WS_NUMBER1' then
			return WS_NUMBER1;
		end if;
		if upper(x) = 'WS_NUMBER2' then
			return WS_NUMBER2;
		end if;
	end get_n;
	function get_d (x in varchar2) return date is
	begin
		if upper(x) = 'WS_DATE1' then
			return ws_DATE1;
		end if;
		if upper(x) = 'WS_DATE2' then
			return ws_DATE2;
		end if;
	end get_d;
	function get_x (x in varchar2) return varchar2 is
	begin
		if upper(x) = 'WS_VARCHAR1' then
			return ws_VARCHAR1;
		end if;
		if upper(x) = 'WS_VARCHAR2' then
			return ws_VARCHAR2;
		end if;
	end get_x;
end;
/

The above package not only creates the global, persistent variables, but it creates access functions to "GET" variable values directly by SQL and stored procedure/functions.

Below is a contrived example that illustrate how you can use these global, persistent variables for user input to a stored PL/SQL procedure that has no arguments/parameters.
Section 3 -- Revised code definition of stored procedure using global, persistent variables (to avoid incoming arguments for the stored procedure):

********************************************************************************
create or replace procedure show_tax2 is
	tax_rate_hold	number;
begin
	select tax_rate into tax_rate_hold from US_Sales_Tax_Rates where State_ID = 
		upper(dh_ws.get_x('ws_varchar1')); -- State_Hold
	dbms_output.put_line('Sales tax on a purchase of $'
                ||dh_ws.get_n('ws_number1')|| -- Amt_hold
		' in the state of '||
		dh_ws.get_x('ws_varchar1')||' = $'||
                dh_ws.get_n('ws_number1')*tax_rate_hold||'.'); 
exception
	when others then
		dbms_output.put_line
                ('*** ERROR: Could not process calculation for state named: ['||
			dh_ws.get_x('ws_varchar1')||'].');
end;
/

Procedure created.

Section 4 -- Sample invocation using user-supplied input, but not arguments in the procedure:

(Code executed from script named "tt_113b.sql")
set echo off
set serveroutput on
accept state_in prompt "Enter U.S. state abbrev. where the purchase occurred: "
accept amt_in prompt "What was the amount of the purchase?: "
exec dh_ws.ws_number1 := &amt_in
exec dh_ws.ws_varchar1 := upper('&state_in')
exec show_tax2

SQL> @tt_113b
Enter U.S. state abbrev. where the purchase occurred: ut
What was the amount of the purchase?: 10
Sales tax on a purchase of $10 in the state of UT = $.65.

Let us know if any of these concepts are helpful in resolving your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:36 (07Jul04) UTC (aka "GMT" and "Zulu"), 11:36 (07Jul04) Mountain Time)