oracle procedure call
sqlplus user@tnsname
SQL> create or replace procedure count_emp_by_dept( pout_count out number)
2 is
3 begin
4 select count(*) into pout_count
5 from all_tables;
6 end count_emp_by_dept;
7 /
//select text from user_source where name = 'USPWORKLOADTYPECPU' -- 전부 대문자....-_-;;;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
using System.Data.OracleClient;
namespace OraTest
{
class Program
{
static void Main(string[] args)
{
SqlNetTest sqlNetTest = new SqlNetTest();
for (int i = 0; i < 5; i++)
{
sqlNetTest.execute();
}
}
}
class SqlNetTest
{
int countOut = 0;
private string ConnectionString
{
get
{
//return "Data Source=xxxxxx;Initial Catalog=xxxxx;User ID=xxxxxx;Password =xxxxx";
return "Data Source=xxx;User Id=xxx;Password=xxx;Integrated Security=no;";
}
}
public void execute()
{
this.countOut = usp_netTest(this.ConnectionString);
Console.WriteLine("all_tables counts : {0} ", this.countOut);
}
//D:\app\NAVER\product\11.2.0\client_1
private int usp_netTest(string ConnectionString)
{
int resultCnt = 0;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "count_emp_by_dept";
cmd.CommandType = CommandType.StoredProcedure;
//objCmd.Parameters.Add("pin_deptno", OracleType.Number).Value = 20;
cmd.Parameters.Add("pout_count", OracleType.Number).Direction = ParameterDirection.Output;
try
{
conn.Open();
cmd.ExecuteNonQuery();
resultCnt = int.Parse(string.Format("{0}",cmd.Parameters["pout_count"].Value));
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}",ex.ToString());
}
conn.Close();
}
return resultCnt;
}
}
}
-- odac
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
//using System.Data.OracleClient;
using Oracle.DataAccess.Client;
namespace OraTest
{
class Program
{
static void Main(string[] args)
{
SqlNetTest sqlNetTest = new SqlNetTest();
for (int i = 0; i < 5; i++)
{
sqlNetTest.execute();
}
Console.ReadKey();
}
}
class SqlNetTest
{
string result = string.Empty;
private string ConnectionString
{
get
{
//return "Data Source=test;User Id=test;Password=test";
// without tnsname
return "Data Source = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 1.666.6.6)(PORT = 140)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = test))); User Id = test; Password = test; Min Pool Size=500; Max Pool Size=500; Connection Timeout=60;";
}
}
public void execute()
{
this.result = usp_netTest(this.ConnectionString);
Console.WriteLine("all_tables counts : {0} ", this.result);
}
//D:\app\NAVER\product\11.2.0\client_1
//D:\app\client\NAVER\product\12.1.0\client_1
//Oracle 네트워크 구성 파일(tnsnames.ora 및/또는 sqlnet.ora)로, 다음 위치에 상주합니다.
//D:\app\NAVER\product\11.2.0\client_1\network\admin
//다음에 새로 설치된 Oracle 홈으로 복사되었습니다.
//D:\app\client\NAVER\product\12.1.0\client_1\network\admin
private string usp_netTest(string ConnectionString)
{
string result = string.Empty;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandText = "uspWorkLoadTypeCPU";
cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add("v_variable1", OracleType.Number).Value = 1;
//cmd.Parameters.Add(new OracleParameter("v_variable1", 1));
cmd.Parameters.Add("v_variable1", OracleDbType.Int32).Value = 1;
cmd.Parameters.Add("v_dbOutput", OracleDbType.NVarchar2, 100).Direction = ParameterDirection.Output;
try
{
cmd.ExecuteNonQuery();
result = string.Format("{0}", cmd.Parameters["v_dbOutput"].Value);
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}", ex.ToString());
Console.ReadKey();
}
}
conn.Close();
}
return result;
}
}
}