khall16
asked on
PL/SQL Procedures and obtaining user input.
Please explain why a procedure cannot obtain user input. I've read about PROMPT and ACCEPT, as well as a little on DBMS_PIPE, but am not understanding very well. I'm a beginner to PL/SQL. If I have two stored procedures and the first asks for user input and the second displays a menu. I call the stored procedures, but all I get is the menu. What are the workarounds for this?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The ACCEPT way uses SQL*Plus approach (pure Oracle):
Invoke SQL*Plus program and run this file:
File sample.sql:
accept myv number default 10 prompt 'Enter a number: '
execute first_procedure(myv);
Now run the batch:
SQL> @ c:\some_where\sample
It will answer
Enter a number: _
Invoke SQL*Plus program and run this file:
File sample.sql:
accept myv number default 10 prompt 'Enter a number: '
execute first_procedure(myv);
Now run the batch:
SQL> @ c:\some_where\sample
It will answer
Enter a number: _
usin Thin JDBC driver
**************************
* Simple example
**************************
class hello
{
public static void main (String args [])
{
System.out.println("hello World!");
}
}
**************************
* Reading from scot/tiger table
**************************
import java.sql.*;
import oracle.jdbc.pool.OracleDat
class first {
public static void main (String args []) throws SQLException {
// Create DataSource and connect to the local database
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:th
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();
// Query the employee names
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp ORDER BY ename");
// Print the name out
while (rset.next ())
System.out.println (rset.getString (1));
//close the result set, statement, and the connection
rset.close();
stmt.close();
conn.close();
}
}