Solved

PL/SQL Procedures and obtaining user input.

Posted on 2007-11-13
4
1,610 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
  • 3
4 Comments
 
LVL 11

Assisted Solution

by:mohammadzahid
mohammadzahid earned 20 total points
Comment Utility
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 47

Accepted Solution

by:
schwertner earned 30 total points
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now