Problem In Connecting Oracle9i SP with CR11.0

Hi!

I made an Stored Proc. in oracle 9i, in which i inserted data in a temporary table. The SP got compiled successfully. But when i tried to add that SP in the CR 11.0, through DSN, i got an error msg.

Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
ORA-06512: at "SCOTT.TEST_INSERTDATA1", line 6
ORA-06512: at line 1

If data is not inserted in stored proc, then no error comes while connecting Crystal report to stored proc of oracle.

This problem is specifically for those stored procs, where data is inserted in some table inside stored proc and that is used in CR.

Code of Oracle Stored Proc is as follows.
----------------

create or replace procedure Test_InsertData1(curmain in out CommonCursor_Pkg.abc,arg1 varchar2,arg2 varchar2)
as
lcsqry varchar2(500);
begin
lcsqry:='delete from reptable1';
execute immediate(lcsqry);

for i in 1..10 loop
lcsqry:='insert into reptable1(sfld1,nfld1) values(''aa'','||i||')';
execute immediate(lcsqry);
end loop;
commit;

lcsqry:='select sfld1,nfld1 from reptable1';
open curmain for (lcsqry);

end;
/
--------------------
-- Here CommonCursor_Pkg.abc denotes a ref cursor type named 'abc' declared inside CommonCursor_Pkg package. Code for this is :

create package CommonCursor_Pkg
as
type abc is ref cursor;

end;
/
-------------------

-- 'reptable1' is a global temporary table.

--CR 11.0 is connected to oracle in following manner.

Goto
Database Expert-> create new connection ->ODBC(RDO)
Then selecting the Oracle DSN from the list.

Or

Goto
Database Expert-> Create new Connection-> OLE DB(ADO)

please see how to fix this problem.

Thanks and regards

Vineet kumarThe procedure should have select statement at the end. As you are using using dynamic sql, scope is over if you run that proceudre. Try using global temporary tablesql

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help

Problem creating a function from an assembly

I have compiled a dll with the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes

Public Class SICTrans

Public Shared Function TransSIC(ByVal inpSIC As String) As String
'Dim conn As SqlConnection = New SqlConnection("context connection=true")
Dim NewSIC, TempSIC, tempFSIC As String

If Len(NZ(inpSIC)) > 0 Then
TempSIC = NZ(inpSIC)

If Len(TempSIC) < 5 Then TempSIC = Left("00000", 5 - Len(TempSIC))

tempFSIC = Left(TempSIC, 2) + "." + Mid(TempSIC, 3, 2)

If Val(Right(TempSIC, 1)) > 0 Then

tempFSIC = tempFSIC + "/" + Right(TempSIC, 1)
End If
End If

NewSIC = tempFSIC

TransSIC = NewSIC

End Function
Public Shared Function NZ(ByVal input As String) As String
If Not (input Is Nothing) Then
Return input
Exit Function
End If
Return String.Empty

End Function
End Class

Which compiles fine...

i then use the following code to create the assembly in SQL which is fine:

USE NARD
GO
CREATE ASSEMBLY SICCodeTrans
FROM 'c:\SICCodeTrans.dll'
WITH PERMISSION_SET = SAFE
GO

but when i goto create the function with the following code it wont have it!

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.SICTrans.TransSIC
GO

It gives me the following error message

Msg 6505, Level 16, State 1, Procedure TransSICCode, Line 1
Could not find Type 'SICTrans' in assembly 'SICCodeTrans'.

Any ideas?

Thanks

Marek Kluczynski

I think what's going on here is that there is a root namespace on the project.

IIRC, in a C# project VS will simply insert the root namespace delcaration into your code when you use certain templates. In VB, on the other hand, the compiler decides to insert the namespace definition without having it show up in the code.

When you go to create the function in SQL, it can't find it because there is no SICTrans class in the default namespace, there is a SICTrans class in whatever root namespace was set on the project.

You can right-click on the project in VS and pull up the propertiesunder the "Application" tab you'll see this.

Assuming your namespace is NAMESPACE, you'd think you could do the following:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.NAMESPACE.SICTrans.TransSIC
GO

This won't workit's a (rather annoying) syntax error. You have to write it like this:

CREATE FUNCTION TransSICCode(@.inpSIC varchar)
RETURNS varchar
AS EXTERNAL NAME
SICCodeTrans.[NAMESPACE.SICTrans].TransSIC
GO

Hope this works for you. Let us know. :)

Cheers
-Isaac

|||

yes this seems to have resolved the namespace error.

I now have the following error:

Msg 6551, Level 16, State 2, Procedure TransSICCode, Line 1

CREATE FUNCTION for "TransSICCode" failed because T-SQL and CLR types for return value do not match.

Many thanks

Marek

|||I suspect two things. First, you should map the string to an nvarchar, not a varcharCLR strings are Unicode. Second, you need to give a parameter to the nvarchar, e.g., nvarchar(4000), nvarchar(max), etc.

Cheers,
-Isaac|||Many thanks for all your help