Error: ORA-30683: failure establishing connection to debugger or ORA-06512: at "SYS.DBMS_DEBUG_JDWP" In Oracle SQL Developer Tool If you are getting following error while debug your stored procedure then you can try following steps
|
Friday, March 27, 2009
Error: ORA-30683: failure establishing connection to debugger
Thursday, March 12, 2009
Execute Oracle Stored Procedure/Query FROM SQL Server
Execute Oracle Stored Procedure/Query FROM SQL Server
Most of the time your project requirement to work with two databases, In some scenarios you want to execute one database stored procedure from another database. Here I am giving the example to execute Oracle stored procedure from SQL server database. To achieve this first you have to set up link from SQL server to Oracle
How to link Oracle Database from SQL server http://blogs.techrepublic.com.com/datacenter/?p=133 http://support.microsoft.com/kb/280106 http://msdn.microsoft.com/en-us/library/aa259589(SQL.80).aspx To execute an Oracle Stored Procedure from SQL Server you can try the following: You can make a sample package named Project_Package with the procedure named TestProcedure:
Package Header: PROCEDURE TestProcedure Package Body: PROCEDURE TestProcedure Then we create a SQL Server Stored Procedure to Wrapp the Oracle Store Procedure Invocation, the T-SQL inside the procedure for this sample is the following: DECLARE @l_i_parameter1 INTEGER DECLARE @l_i_parameter2 INTEGER DECLARE @l_o_parameter1 INTEGER DECLARE @l_o_parameter2 INTEGER SET @l_i_parameter1 = 5 SET @l_i_parameter2 = 10 SET @l_o_parameter1 = 0 SET @l_o_parameter2 = 0 EXECUTE ( 'begin Project_Package.TestProcedure(?,?,?,?); end;', @l_i_parameter1, @l_i_parameter2, @l_o_parameter1 OUTPUT, @l_o_parameter2 OUTPUT) AT DBLINK_NAME; SELECT @l_o_parameter1, @l_o_parameter2 I Hope this sample illustrate the concept. Also I want to mention that this sample only works with simple output parameters, if you are trying to query for result sets I recommend trying the OPENQUERY statement instead. OR
EXEC mySchema.myPackage.myProcedure@myRemoteDB( 'someParameter' ); Using OPENQUERY If exists (select * from master..sysservers where USING SP_EXECUTEESQL DECLARE @rc INT DECLARE @v_sql NVARCHAR(4000) DECLARE @vc_servername VARCHAR(100) DECLARE c_myservers CURSOR FOR SELECT servername FROM listservers WHERE active=1 FOR READ ONLY OPEN c_myservers FETCH NEXT FROM c_myservers INTO @vc_servername WHILE (@@FETCHSTATUS = 0 ) BEGIN SET @v_sql = N'SELECT name, id FROM ['+@vc_servername + '].dbname.joe.titles' EXEC @rc = sp_executesql @v_sql -- if @rc <> 0 --- etc... FETCH NEXT FROM c_myservers INTO @vc_servername END CLOSE c_myservers DEALLOCATE c_myservers COMPARISION Rather your choice is between: SELECT @sql = 'SELECT .... FROM ' + @server + 'catalog.schema.tbl' and SELECT @sql = 'SELECT ... FROM OPENQUERY(' + @server + ', ' + '''SELECT ... FROM catalog.schema.tbl'')' That is, accessing the table in four-part notation, or running a pass-through query. |
Thursday, March 05, 2009
How to send Array as an input parameter to Oracle SP using VB.NET
How to send Array as an input parameter to Oracle SP using .NET
Most of the project require to send huge parameter to oracle stored procedure that will be append in IN Clause in the SQL query. Basically you can send dynamically created inclause from the .NET code to oracle stored procedure.
There are multiple way to do this, you can send Array to SP and inside array, you can create concatated string and pass it to your SQL statement. BUT here if your inclause is going to be huge more then 4000 char then this approach wouldn't work, and string concatenation may hit the performance.
To over come with above two issues following approach will be the better approach, From the Array you can insert datainto the temp table and then fetch the data from temp table and create your complete SQL statement with inclause.
Please follow the steps given below for Demo
Following solution tested with
- Visual Studio 2005 with VB.NET
- Oracle 10g
- Microsoft Enterprise Library 3.0
It should work with higher and couple of lower version too.
Oracle 10g CODE
1) Create one global type (string_table) as table type (this will be your temparory table)
CREATE OR REPLACE TYPE string_table AS TABLE OF varchar2(100)
2) Create one global package (GLOBAL_PACKAGE) to declare array Type for SP
CREATE OR REPLACE PACKAGE GLOBAL_PACKAGE AS
TYPE REF_CUR IS REF CURSOR;
TYPE CHARARRAY IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
END GLOBAL_PACKAGE;
33) 3) Create one stored procedure (Data_Fetch)that will take input paremeter as Array and Out parameter as Ref Cursor.
CREATE OR REPLACE PROCEDURE Data_Fetch
(
prmInClause IN Global_package.Chararray,
prmOUTCursor OUT Global_package.REF_CUR
)
IS
BEGIN
Declare
tempTable string_table := string_table(NULL);
BEGIN
FOR i IN 1..prmInClause.count LOOP
tempTable(i) := prminclause(i);
tempTable.extend;
END LOOP;
Open prmOUTCursor for
SELECT * FROM Employee where id in (select column_value from table(tempTable));
END Data_Fetch;
END;
VB.NET Code
Give the reference to Microsoft Enterprosies Library, if you are using otherwise use simple Oracle command or other object.
Include NameSpace
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports Oracle.DataAccess.Client
Methods
''' <summary>
''' This function will send String Array to the database stored procedure
''' to create huge Inclause for sql query
''' </summary>
Private Function ReturnDataSet() As DataSet
'Define database connection string in the app.config and write the name below
Dim connectDB As Database = DatabaseFactory.CreateDatabase("DATABASE")
Dim ds As DataSet
Dim command As DbCommand
command = connectDB.GetStoredProcCommand("SP_NAME")
Dim objParam As OracleParameter() = New OracleParameter(1) {}
Dim arrInClause() As String = {"345345DSGFDG345", "SAFDS456546J"}
'Input parameter in stored procedure type as table of varchar2(50)
objParam(0) = New OracleParameter("prmInClause", OracleDbType.Varchar2)
objParam(0).Direction = System.Data.ParameterDirection.Input
objParam(0).CollectionType = OracleCollectionType.PLSQLAssociativeArray
objParam(0).Value = arrInClause
'Output parameter in stored procedure type as Ref Cursor
objParam(1) = New OracleParameter("prmCustomerOptions", OracleDbType.RefCursor)
objParam(1).Direction = System.Data.ParameterDirection.Output
objParam(1).CollectionType = OracleCollectionType.None
command.Parameters.AddRange(objParam)
ds = connectDB.ExecuteDataSet(command)
Return ds
End Function
C# Code
Include NameSpace
using Microsoft.Practices.EnterpriseLibrary.Data
using Oracle.DataAccess.Client
Method
/// <summary>
/// This function will send String Array to the database stored procedure
/// to create huge Inclause for sql query
/// </summary>
private DataSet ReturnDataSet()
{
//Define database connection string in the app.config and write the name below
Database connectDB = DatabaseFactory.CreateDatabase("DATABASE");
DataSet ds = default(DataSet);
DbCommand command = default(DbCommand);
command = connectDB.GetStoredProcCommand("SP_NAME");
OracleParameter[] objParam = new OracleParameter[2];
string[] arrInClause = { "345345DSGFDG345", "SAFDS456546J" };
//Input parameter in stored procedure type as table of varchar2(50)
objParam(0) = new OracleParameter("prmInClause", OracleDbType.Varchar2);
objParam(0).Direction = System.Data.ParameterDirection.Input;
objParam(0).CollectionType = OracleCollectionType.PLSQLAssociativeArray;
objParam(0).Value = arrInClause;
//Output parameter in stored procedure type as Ref Cursor
objParam(1) = new OracleParameter("prmCustomerOptions", OracleDbType.RefCursor);
objParam(1).Direction = System.Data.ParameterDirection.Output;
objParam(1).CollectionType = OracleCollectionType.None;
command.Parameters.AddRange(objParam);
ds = connectDB.ExecuteDataSet(command);
return ds;
}