java - 如何在oracle数据库11g中添加sql文件？

Question

我已经使用java swing创建了java寄存器:

这是registration.java代码:

   package student;
import javax.swing.*;

import java.awt.*;

import java.awt.event.*;

import java.sql.*;

public class Registration extends JFrame implements ActionListener
{ 

  JLabel l1, l2, l3, l4, l5, l6, l7, l8;

  JTextField tf1, tf2, tf5, tf6, tf7;

  JButton btn1, btn2;

  JPasswordField p1, p2;



  Registration()
   {

      setVisible(true);

      setSize(700, 700);

      setLayout(null);

      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

      setTitle("Registration Form in Java");



      l1 = new JLabel("Registration Form in Windows Form:");

      l1.setForeground(Color.blue);

      l1.setFont(new Font("Serif", Font.BOLD, 20));
      l2 = new JLabel("Name:");

      l3 = new JLabel("Email-ID:");

      l4 = new JLabel("Create Passowrd:");

      l5 = new JLabel("Confirm Password:");

      l6 = new JLabel("Country:");

      l7 = new JLabel("State:");

      l8 = new JLabel("Phone No:"); 

      tf1 = new JTextField();

      tf2 = new JTextField();

      p1 = new JPasswordField();

      p2 = new JPasswordField();

      tf5 = new JTextField();

      tf6 = new JTextField();

      tf7 = new JTextField();



      btn1 = new JButton("Submit");

      btn2 = new JButton("Clear");



      btn1.addActionListener(this);

      btn2.addActionListener(this);



      l1.setBounds(100, 30, 400, 30);

      l2.setBounds(80, 70, 200, 30);

      l3.setBounds(80, 110, 200, 30);

      l4.setBounds(80, 150, 200, 30);

      l5.setBounds(80, 190, 200, 30);

      l6.setBounds(80, 230, 200, 30);

      l7.setBounds(80, 270, 200, 30);

      l8.setBounds(80, 310, 200, 30);

      tf1.setBounds(300, 70, 200, 30);
      tf2.setBounds(300, 110, 200, 30);

      p1.setBounds(300, 150, 200, 30);

      p2.setBounds(300, 190, 200, 30);

      tf5.setBounds(300, 230, 200, 30);

      tf6.setBounds(300, 270, 200, 30);

      tf7.setBounds(300, 310, 200, 30);

      btn1.setBounds(50, 350, 100, 30);

      btn2.setBounds(170, 350, 100, 30);
      add(l1);

      add(l2);

      add(tf1);

      add(l3);

      add(tf2);

      add(l4);

      add(p1);

      add(l5);

      add(p2);

      add(l6);

      add(tf5);

      add(l7);

      add(tf6);

      add(l8);

      add(tf7);

      add(btn1);

      add(btn2);

  }
  public void actionPerformed(ActionEvent e)
  {

     if (e.getSource() == btn1)
      {

         int x = 0;

         String s1 = tf1.getText();

         String s2 = tf2.getText();



         char[] s3 = p1.getPassword();

         char[] s4 = p2.getPassword(); 

         String s8 = new String(s3);

         String s9 = new String(s4);



         String s5 = tf5.getText();

         String s6 = tf6.getText();

         String s7 = tf7.getText();

         if (s8.equals(s9))
        {

             try
            {

                 Class.forName("oracle.jdbc.driver.OracleDriver");

                 Connection con = DriverManager.getConnection("jdbc:oracle:thin:rajesh/local@localhost");
                 PreparedStatement ps = con.prepareStatement("insert into emp values(?,?,?,?,?,?)");

                 ps.setString(1, s1);

                 ps.setString(2, s2);

                 ps.setString(3, s8);

                 ps.setString(4, s5);

                 ps.setString(5, s6);

                 ps.setString(6, s7);

                 ResultSet rs = ps.executeQuery();

                 x++;

                 if (x > 0)
                 {

                     JOptionPane.showMessageDialog(btn1, "Data Saved Successfully");

                 }

             }
       catch (Exception ex)
             {

                 System.out.println(ex);

             }

         }
       else
        {

             JOptionPane.showMessageDialog(btn1, "Password Does Not Match");

         } 

     } else
     {

         tf1.setText("");

         tf2.setText("");

         p1.setText("");

         p2.setText("");

         tf5.setText("");

         tf6.setText("");

         tf7.setText("");

     }

 } 

 public static void main(String args[])
{

     new Registration();

 }
}

当我运行这个时，它显示

"ORA-00942: table or view does not exist".

但是我已经在oracle 11g中创建了数据库用户和表。

这些步骤是我在 sql 命令行中执行的:

connect
username:
password:
"connected"
create user rani identifed by pcs;
grant connect, resource to rani;
"granted"

之后，为了创建表，我有一个名为 reg.sql 的文件:

create table emp
 (
    name varchar2(30), email varchar2(40),
    pass varchar2(30), count varchar2(30),
    state varchar2(30), phone number(15)
 );

所以我必须导入此文件来存储学生详细信息。

我可以知道该怎么做吗？提前致谢。

Answer 1

connect
username:
password:
"connected"
create user rani identifed by pcs;
grant connect, resource to rani;
"granted"
create table reg(pid integer primary key,name varchar2(50));
"table created"

您创建的表不在RANI 架构中。您需要首先连接到 RANI 用户，然后发出CREATE TABLE语句。截至目前，您的表 REG 驻留在您最初连接到的 USER 中。

更新基于OP关于执行脚本的请求。

您可以将其作为脚本执行。只需在脚本本身中添加所有内容并调用脚本即可。

例如，您的 reg.sql 如下所示:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE emp';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;
/
create table emp
 (
    name varchar2(30), email varchar2(40),
    pass varchar2(30), count varchar2(30),
    state varchar2(30), phone number(15)
 );

您只需使用本地放置的完整目录来执行脚本即可。假设 reg.sql 位于 C:\REG

sqlplus -s rani/pcs@sid << EOF
@C:\REG\reg.sql;
EXIT;
EOF