如何使用Win Forms创建Oracle存储过程 [英] How to create Oracle Stored Procedure using Win Forms

问题描述

我有多个商店，我需要在每个商店创建一个新的存储过程

i已经在一个文件夹中创建了11个文件并逐个执行它们9和10是存储过程..一切都工作正常，但存储过程创建错误



错误

  PROCEDURE   01951  .PROCESS_SCANNED_BARCODE_GDN 
  On 行： 1  
 PLS-00103：遇到符号  当期望 以下时：
 
（;     authid  as  cluster compress  order  使用编译
w敲击外部确定性parallel_enable流水线

当我在Toad中运行相同而没有一次更改时，它的编译没有任何错误



这是我的代码

  foreach （DataRow行  ds.Tables [ 0 ]。行）
 {
 尝试 
 {
 user_id = row [  。STORE]的ToString（）; 
  //   goto skip;  
 password = Convert.ToInt32（row [< span class =code-string>  STORE]。ToString（））+  7 ; 
 ftp_ip = row [  FTP_IP]。ToString（）。Trim（）; 
 constr =  数据源= + ftp_ip +   / orcl;用户ID = + user_id +  ; Password = + password +  ; 
 store_con =  new  OracleConnection（constr）; 
 store_con.Open（）; 
  for （ int  i =  1 ; i <   12 ; i ++）
 {
 使用（StreamReader reader =  new  StreamReader（  C：\\DTS_Package \\HHT \\ + i.ToString（）+   .txt））
 {
  try  
 {
 str = reader.ReadToEnd（）; 
 store_cmd =  new  OracleCommand（str，store_con）; 
 store_cmd.ExecuteScalar（）; 
} 
  catch （例外情况）
 {
 
} 
 
 } 
} 
 str =  插入STORES_NOT_CONNECTED VALUES（' + user_id +  '，' + ftp_ip +  '，'更新'，SYSDATE，'HHT'）; 
 cmd =  new  OracleCommand（str，con）; 
 cmd.ExecuteNonQuery（）; 
 store_con.Close（）; 
} 
  catch （例外情况）
 {
 str =    INSERT INTO STORES_NOT_CONNECTED VALUES（' + user_id +  '，' + ftp_ip +  '，' + ex。消息+  '，SYSDATE，'HHT'）; 
 cmd =  new  OracleCommand（str，con）; 
 cmd.ExecuteNonQuery（）; 
} 
 
} 
 

解决方案

而不是连接你的SQL语句你在这里做，你应该使用Sql-Parameters（或这里的OracleParameter）。它不仅可以解决您在这里遇到的问题（因为引用相互干扰），还可以使您的SQL语句安全地抵御SQL注入攻击，并使您的代码更具可读性，因此更易于维护。



请看这里：

https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.110%29。 aspx [ ^ ]

Gotcha＃1161：在Oracle ODP.NET中使用命名参数 [ ^ ]

I have multiple stores and i need to create a new Stored Procedure in every store
i have created 11 files in a folder and executing them one by one the 9 and 10 is the stored procedure..every thing is working fine but Stored Procedure is creating with errors

error

PROCEDURE 01951.PROCESS_SCANNED_BARCODE_GDN
On line:  1
PLS-00103: Encountered the symbol "" when expecting one of the following:

   ( ; is with authid as cluster compress order using compiled
   wrapped external deterministic parallel_enable pipelined

and when i am running the same in Toad without a single change it's compiling without any error

here is my code

foreach (DataRow row in ds.Tables[0].Rows)
      {
          try
          {
              user_id = row["STORE"].ToString();
              //goto skip;
              password = Convert.ToInt32(row["STORE"].ToString()) + 7;
              ftp_ip = row["FTP_IP"].ToString().Trim();
              constr = "Data Source=" + ftp_ip + "/orcl;User ID=" + user_id + ";Password=" + password + "";
              store_con = new OracleConnection(constr);
              store_con.Open();
              for (int i = 1; i < 12; i++)
              {
                  using (StreamReader reader = new StreamReader("C:\\DTS_Package\\HHT\\" + i.ToString() + ".txt"))
                  {
                      try
                      {
                          str = reader.ReadToEnd();
                          store_cmd = new OracleCommand(str, store_con);
                          store_cmd.ExecuteScalar();
                      }
                      catch (Exception ex)
                      {

                      }

                  }
              }
              str = "INSERT INTO STORES_NOT_CONNECTED VALUES ('" + user_id + "','" + ftp_ip + "','Updated',SYSDATE,'HHT')";
              cmd = new OracleCommand(str, con);
              cmd.ExecuteNonQuery();
              store_con.Close();
          }
          catch (Exception ex)
          {
              str = "INSERT INTO STORES_NOT_CONNECTED VALUES ('" + user_id + "','" + ftp_ip + "','" + ex.Message + "',SYSDATE,'HHT')";
              cmd = new OracleCommand(str, con);
              cmd.ExecuteNonQuery();
          }

      }

解决方案

Instead of concatenating your SQL-statements like you do here, you should use Sql-Parameters (or here "OracleParameter"). It will not only resolve the issue you're having here (because of quotes interfering with each other) but also make your SQL-statements safe against SQL-injection-attacks and make your code more readable and therefore more maintainable.

Please see here:
https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.110%29.aspx[^]
Gotcha #1161: Using Named Parameters with Oracle ODP.NET[^]

这篇关于如何使用Win Forms创建Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！