Re: Need some Oracle help if possible - Database Discussion Boards

Where can I learn about this?!

Regards.

Mehdi Ghiasi

I heard about this website called google.com. Apparently it has been around for a while and is very easy to use, but you should check for yourself. That's www.google.com

Craigslist Troll: litaly@comcast.net

"I have a theory that the truth is never told during the nine-to-five hours. "
— Hunter S. Thompson

I'd have a read of this MSDN Article: Connection Pooling[^]

Also Connection pooling is turned on by default when using ADO.NET

Nagy Vilmos wrote:
And eat bacon. Bacon's real important for 'puters.

modified on Thursday, May 5, 2011 5:01 PM

Mehdi Ghiasi wrote:
almost 10 connections

I hope you mean one connection is opened and closed almost ten times. If so, you might do better to open it once and close it at the end of the method.

i think little bit issue will arise. If you instantiate an object for ten times or create ten object or some method of an object (like,- Open() and Close()) performance can be a issue . mostly its a bad programming practice.

I could use some help with our Oracle implementation.
We are trying to do an insert through a REST service using Oracle.DataAccess.dll.
When we execute UPDATE the process runs without problems. But an INSERT causes a type mismatch error. The frustrating part of this is that we have the database defined to take null for all fields but the fields rejected are null.

Edit:: I should add that we are sending the data as comma delimited strings to the function so there is no numeric fields being passed in the call.

Here is the function we are calling:

CREATE OR REPLACE FUNCTION CNRP.CNRP_S_15_109_STAKEHLD_I (
STAKEHOLDER_ID IN SIMS_STAKEHOLDER.STAKEHOLDER_ID%TYPE,
SITE_ID IN SIMS_STAKEHOLDER.SITE_ID%TYPE,
STAKEHOLDER_TYPE IN SIMS_STAKEHOLDER.STAKEHOLDER_TYPE%TYPE,
TITLE IN SIMS_STAKEHOLDER.TITLE%TYPE,
NAME_FULL IN SIMS_STAKEHOLDER.NAME_FULL%TYPE,
MAIL_ADDRESS IN SIMS_STAKEHOLDER.MAIL_ADDRESS%TYPE,
MAIL_CITY IN SIMS_STAKEHOLDER.MAIL_CITY%TYPE,
MAIL_STATE IN SIMS_STAKEHOLDER.MAIL_STATE%TYPE,
MAIL_ZIP IN SIMS_STAKEHOLDER.MAIL_ZIP%TYPE,
PHONE_HOME IN SIMS_STAKEHOLDER.PHONE_HOME%TYPE,
PHONE_CELL IN SIMS_STAKEHOLDER.PHONE_CELL%TYPE,
PHONE_WORK IN SIMS_STAKEHOLDER.PHONE_WORK%TYPE,
PREF_LANG_WRITTEN IN SIMS_STAKEHOLDER.PREF_LANG_WRITTEN%TYPE,
PREF_LANG_VERBAL IN SIMS_STAKEHOLDER.PREF_LANG_VERBAL%TYPE,
EMAIL IN SIMS_STAKEHOLDER.EMAIL%TYPE,
PHONE_FAX IN SIMS_STAKEHOLDER.PHONE_FAX%TYPE
)
RETURN BOOLEAN
AS
DONE BOOLEAN;

BEGIN
INSERT INTO SIMS_STAKEHOLDER

(SITE_ID,
STAKEHOLDER_TYPE,
TITLE,
NAME_FULL,
MAIL_ADDRESS,
MAIL_CITY,
MAIL_STATE,
MAIL_ZIP,
PHONE_HOME,
PHONE_CELL,
PHONE_WORK,
PREF_LANG_WRITTEN,
PREF_LANG_VERBAL,
EMAIL,
PHONE_FAX)

VALUES (CNRP_S_15_109_STAKEHLD_I.SITE_ID,
CNRP_S_15_109_STAKEHLD_I.STAKEHOLDER_TYPE,
CNRP_S_15_109_STAKEHLD_I.TITLE,
CNRP_S_15_109_STAKEHLD_I.NAME_FULL,
CNRP_S_15_109_STAKEHLD_I.MAIL_ADDRESS,
CNRP_S_15_109_STAKEHLD_I.MAIL_CITY,
CNRP_S_15_109_STAKEHLD_I.MAIL_STATE,
CNRP_S_15_109_STAKEHLD_I.MAIL_ZIP,
CNRP_S_15_109_STAKEHLD_I.PHONE_HOME,
CNRP_S_15_109_STAKEHLD_I.PHONE_CELL,
CNRP_S_15_109_STAKEHLD_I.PHONE_WORK,
CNRP_S_15_109_STAKEHLD_I.PREF_LANG_WRITTEN,
CNRP_S_15_109_STAKEHLD_I.PREF_LANG_VERBAL,
CNRP_S_15_109_STAKEHLD_I.EMAIL,
CNRP_S_15_109_STAKEHLD_I.PHONE_FAX);

RETURN TRUE;
EXCEPTION

WHEN OTHERS THEN RETURN FALSE;
END;
/

modified on Wednesday, May 4, 2011 4:43 PM

With what you have provided it is difficult to recognize what type mis-match is occurring. Even if the database fields are allowed to be null, the type mismatch could be occurring on the function call. Since you indicate that the call to the function is supplying comma delimited string data, then you should verify that each of the data types for all of the input parameters will accept character data. Just a guess, but possibly the parameter SITE_ID is actually expecting to receive a binary numeric value instead of a string. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Thanks Chris,

Since I normally work with Sql Server I'm out of water in this problem. I can pass any additional info you need to help me. Our code is a central service that gets passed a function and makes the call to the database:

[ServiceBehavior(IncludeExceptionDetailInFaults = true,
                    InstanceContextMode = InstanceContextMode.Single,
                    ConcurrencyMode = ConcurrencyMode.Single)]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class DataService : SingletonDataServiceBase<String>, ISingletonDataServiceBase<String>
{
    protected override String OnFunctionRequest(string function)
    {
        try
        {
            OracleConnection oracleCon = new OracleConnection(ConfigurationManager.ConnectionStrings["CNRPConnectionString"].ConnectionString);
            OracleCommand sqlCom = new OracleCommand(
               string.Format( "begin   :REF_CUR := {0} ;end;", function), oracleCon);

            sqlCom.Parameters.Add("REF_CUR", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

            OracleDataAdapter dataAdapter = new OracleDataAdapter();
            sqlCom.Connection.Open();
            sqlCom.ExecuteNonQuery();

            //Populate the DataSet and close the connection
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet, "REF_CUR", (OracleRefCursor)(sqlCom.Parameters["REF_CUR"].Value));

            // Release rare resources immediately to prevent memory leaks and database locks
            sqlCom.Dispose();
            oracleCon.Close();
            oracleCon.Dispose();

            return this.Serialize(dataSet.Tables[0]);
        }
        catch (Exception Exp)
        {
            throw Exp;
        }
    }

When this method is call our insert function is passed as follows to Oracle:

CNRP_S_15_108_STRUCINF_I(7,null,'PWS Repository','Storage',0,null,null,null,null,0,null,null,null,null,null,-1,0,1995,0,0,null,null,0,-1,'16-May-11',-1,'13-Apr-11')

Hope this provides more information to you.

At a minimum you need to get your code postings in sync.

Your posted Oracle function doesn't match the string that you posted.

Of course maybe that is the source of your problem - you are mixing up which function it is failing on.

Well I'm working on something like 4 things at once with 4 hours sleep. I mixed up a few things.
No need for sarcasm. Smile | :)

I wasn't being sarcastic.

To help with diagnosing a code question one needs to post the code that is the source of the problem.

It looks as though you are assigning the result of the function call to a variable and the types of the two do not match. The function is going to return a BOOL type and the variable is of type OracleRefCursor.

Not sure if this will work, but instead of ExecuteNonQuery(), would you be able to ExecuteQuery(string) instead and then check the value of the parameter. The parameter should be defined as type BOOL as well.

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Thanks Chris!

will doing the ExecuteNonQuery with begin : boolean := 'insert statement' also work?

Michael

As long as the parameter :boolean is defined as type BOOL, then I think it might. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

I should add that we are sending the data as comma delimited strings

Using comma separated strings data can be problematic because some times the data itself may contain a commas so the data string gets parsed incorrectly. Strings such as "NY, NEW YORK, STATE OF" will be parsed incorrectly. I suggest you use pipes (|) to separate data elements for example "NY|NEW YORK, STATE OF".

If you can get the data in Excel, you can quickly create a CSV that uses a pipe delimiter(sounds like an Oxymoron..). To change Excel such that it uses a pipe instead of a comma in CSV files, go to Control Panel, Regional and Language Options,Click Customize, then change the list separator value to | (from a ,). So when you save files in Excel as CSV, the data elements will actually be separated by '|' instead of ','. Also, change your parser-whatever that is, to use '|'

I've been searching the net for this but I couldn't find concrete answer what to use to recover identity ID from INSERT stored procedure.
What is the correct way to get the ID as output in stored procedure (should I use @@IDENTITY, SCOPE_IDENTITY() or something else).

CREATE TABLE [dbo].[tbl_Table](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL
)

Thanks,

http://www.davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx[^]

Answer contained within

Dave
Find Me On: Web|Facebook|Twitter|LinkedIn

Folding Stats: Team CodeProject

ty about the article. It really helped.

No, don't use identities, they're too limiting.

Huh?

If you start on about guids again I'll scream.

Never underestimate the power of human stupidity
RAH

I'll need a heads up before you scream. Just to plug my ears. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

I figured that might be that persons point.

One problem with guids is that they have no implicit ordering. And some times ordering for implementation needs rather than business needs is a good idea. Not to mention size and convenience.

And of course guids are likely unique but not guaranteed to be so.

From my personal experience guids may affect the performance significantly if they are used for primary key and are randomly generated. To solve this i've read somewhere that is recommended to use NEWSEQUENTIALID() as default, instead NEWID() to generate keys and with clustered indexes the performances will be much better. However if I use these keys as foreign key in other table this will not help as they will not be ordered, so I choose to use integers for primary key. The idea is MSSQL to generate new id and to return it to object in code, but also I've read there might be some problems with using @@IDENTITY or SCOPE_IDENTITY()

Igor Jas wrote:
I've read there might be some problems with using @@IDENTITY or
SCOPE_IDENTITY()

Reference required, a shot like that requires a reference so we can investigate the veracity of the statement.

Never underestimate the power of human stupidity
RAH