Solved

PL/SQL Procedures and obtaining user input.

Posted on 2007-11-13
4
1,621 Views
Last Modified: 2013-12-07
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?
0
Comment
Question by:khall16
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 11

Assisted Solution

by:mohammadzahid
mohammadzahid earned 20 total points
ID: 20277143
No need to use PROMPT and ACCEPT. I recommend to send parameters to a procedure using IN.

Here is an example:

CREATE OR REPLACE Procedure UpdateCourse( name_in IN varchar2 )
IS
    cnumber number;

    cursor c1 is
    select course_number
      from courses_tbl
      where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
     cnumber := 9999;
end if;

insert into student_courses
( course_name,
  course_number)
values ( name_in,
                cnumber );

commit;

close c1;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

0
 
LVL 48

Accepted Solution

by:
schwertner earned 30 total points
ID: 20279250
PL/SQL is designed to work silently on servers and applications
like Oracle Forms and Oracle Reports.
To accept user input you have to use tools and languages that
are designed to do this.
Java approach to call stored procedures:

CREATE OR REPLACE PROCEDURE p_highest_paid_emp
       (ip_deptno NUMBER,
       op_empno OUT NUMBER,
       op_ename OUT VARCHAR2,
       op_sal OUT NUMBER)
IS
 v_empno NUMBER;
 v_ename VARCHAR2(20);
 v_sal NUMBER;
BEGIN
 SELECT empno, ename, sal
 INTO v_empno, v_ename, v_sal
 FROM emp e1
 WHERE sal = (SELECT MAX(e2.sal)
     FROM emp e2
     WHERE e2.deptno = e1.deptno
     AND e2.deptno = ip_deptno)
  AND deptno = ip_deptno;
 op_empno := v_empno;
 op_ename := v_ename;
 op_sal := v_sal;
END;
/



import java.sql.*;

public class StProcExample {
 public static void main(String[] args)
 throws SQLException {
int ret_code;
Connection conn = null;
 try {
  //Load and register Oracle driver
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 //Establish a connection

 conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:
 Oracle", "oratest", "oratest");
 int i_deptno = 10;
 CallableStatement pstmt = conn.prepareCall("{call p_highest_
 paid_emp(?,?,?,?)}");
 pstmt.setInt(1, i_deptno);
 pstmt.registerOutParameter(2, Types.INTEGER);
 pstmt.registerOutParameter(3, Types.VARCHAR);
 pstmt.registerOutParameter(4, Types.FLOAT);
 pstmt.executeUpdate();

 int o_empno = pstmt.getInt(2);
 String o_ename = pstmt.getString(3);
 float o_sal = pstmt.getFloat(4);
 System.out.print("The highest paid employee in dept "
 +i_deptno+" is: "+o_empno+" "+o_ename+" "+o_sal);
 pstmt.close();
 conn.close();
  } catch (SQLException e) {ret_code = e.getErrorCode();
   System.err.println(ret_code + e.getMessage()); conn.close();}
 }
}

0
 
LVL 48

Expert Comment

by:schwertner
ID: 20279257
These are java examples thar run using Jdeveloper
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.OracleDataSource;

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:thin:@//oralin3:1521/phr7b");
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();
}
}


0
 
LVL 48

Expert Comment

by:schwertner
ID: 20279305
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: _
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question