Oracle

Header to Excel File

Baranboz — Fri, 20 Nov 2009 13:04:50 GMT

Hey There;

I have a little problem about adding a header to my excel file on a procedure

Here is my procedure:

SELECT 'TYPE;PLANT;OBJECT_CODE;WORK_CENTER;UTE;SIGN;ACTUAL_CYCLE_TIME'

AS LINE

FROM DUAL

UNION ALL

SELECT LINE

FROM (

SELECT DISTINCT

'ACTCT' ||';' ||'129' ||';' ||TO_CHAR('T' ||LPAD(ps.TRADEMARK,2,'0')

||LPAD (ps.ALLOCATION_PLANT,2,'0')

LPAD(ps.MODEL,3,'0')

LPAD(ps.VERSION,3,'0')

AS LINE

FROM PSTTR ps WHERE ps.SID = 1161

AND ps.RESPCODE =141);

WiTH this code, I take some columns from my database table,put one after the other with lpad function. Here is working but I also added a header with this part:

SELECT 'TYPE;PLANT;OBJECT_CODE;WORK_CENTER;UTE;SIGN;ACTUAL_CYCLE_TIME'

but I get this one:

LINE

TYPE;PLANT;OBJECT_CODE;WORK_CENTER;UTE;SIGN;ACTUAL_CYCLE_TIME

ACTCT;129;T700118465

ACTCT;129;T700118495

For me, I should not see "LINE" at the top and I should see like:

TYPE;PLANT;OBJECT_CODE;WORK_CENTER;UTE;SIGN;ACTUAL_CYCLE_TIME

ACTCT;129;T700118465

ACTCT;129;T700118495

but I couldn't understand why I get "LINE" at the top.

Please can you help me?

Thanks

Baran Bozoglu

Problem with Calling Oracle Stored Procedure from asp.net (VB)

aatif.iqbal — Thu, 26 Nov 2009 05:43:53 GMT

Dear all,

I'm calling oracle stored procedure which i have tested directly form oracle and it works fine but when I tried to call it from asp code it gives me wrong arguments error but i'm passing the same no of arguments that is required. Please anyone help me as i dont know where i'm wrong in my code. Thanks

Here is the code.

'---------------------------------------------------------------Starts From HERE-------------------------------------------------------------------------

        Dim connectionInfo As String = "Data Source=IPRD;Persist Security Info=True;User ID=XXXXX;Password=XXXX;Unicode=True"
        Dim myConnection As New OracleConnection
        Dim myCommand As New OracleCommand
        Dim chkFlag As Boolean

        Try
            myConnection.ConnectionString = connectionInfo
            myCommand.Connection = myConnection
            myCommand.CommandText = "aris.update_clasif_from_epubs"
            myCommand.CommandType = CommandType.StoredProcedure

            myCommand.Parameters.Add("PARAM_REF_NO", OracleType.VarChar, 10).Value = IIf(REF_NO <> "", REF_NO, vbNull)
            myCommand.Parameters.Add("PARAM_F1", OracleType.VarChar, 10).Value = IIf(F1 <> "", F1, vbNull)
            myCommand.Parameters.Add("PARAM_F2", OracleType.VarChar, 10).Value = IIf(F2 <> "", F2, vbNull)
            myCommand.Parameters.Add("PARAM_F3", OracleType.VarChar, 10).Value = IIf(F3 <> "", F3, vbNull)
            myCommand.Parameters.Add("ChkFlag", OracleType.Number).Direction = ParameterDirection.ReturnValue

            myConnection.Open()
            myCommand.ExecuteNonQuery(asp.net)              <===== ERROR on this Line.
            chkFlag = myCommand.Parameters("ChkFlag").Value
            myConnection.Close()

        Catch ex As Exception
            chkFlag = False
        End Try

Return chkFlag

but gives me ERROR: wrong number or types of arguments in call ORA-06550: line 1, column 7: PL/SQL: Statement ignored

This code has already wasted to much time for me and i can't find the exact problem to rectify it.

Regards & TIA,

Atif

Oracle ORA-12154 TNS Could Not Resolve Service Name

ddelella — Thu, 19 Nov 2009 17:15:52 GMT

I have an application that works great locally. When we moved it to our test server (running Windows 2003, .NET 3.5, Oracle 9i Client 9.2.0.7.0) it returns the stack trace below. I have used TNSPING to test the connection string and it is working fine. I can connect to the database using TOAD for Oracle Freeware version. Oracle 9i Client is the only version of the Oracle software installed. I have tried restarting the server. Any one able to provide some possible solutions?

Unknown Error

System.Data.OracleClient.OracleException: ORA-12154: TNS:could not resolve service name

at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc)

at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName)

at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions)

at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.OracleClient.OracleConnection.Open()

at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() in C:\TrialBase\Baseline\ThirdParty\EnterpriseLibrary\Microsoft.Practices.EnterpriseLibrary.Data\Database.cs:line 1163

at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) in C:\TrialBase\Baseline\ThirdParty\EnterpriseLibrary\Microsoft.Practices.EnterpriseLibrary.Data\Database.cs:line 1196

at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection() in C:\TrialBase\Baseline\ThirdParty\EnterpriseLibrary\Microsoft.Practices.EnterpriseLibrary.Data\Database.cs:line 1177

at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command) in C:\TrialBase\Baseline\ThirdParty\EnterpriseLibrary\Microsoft.Practices.EnterpriseLibrary.Data\Database.cs:line 973

machine.config and web config?

Mareczekkk — Wed, 03 Jun 2009 16:14:39 GMT

Can you show me good configuration file: machine.config and web config. I use vvwd 2008 ee and oralce 11g (11.1.0.6.20) and I have problem when I select ASP.NET Configuration -> Seciurity -> CreateUser. Allways there is "try agian". why? Help me please

I use vwd 2008 ee and oracle 11g. I have problem with add membership

Mareczekkk — Wed, 03 Jun 2009 12:12:33 GMT

May be someone know appropriate congiguration?

This is my part of machine.config:

And this is my web.config:

<configuration>
    <configSections>
        <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
            <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
                    <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="Everywhere"/>
                    <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                    <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                    <section name="roleService" type="System.Web.Configuration.ScriptingRoleServiceSection, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" allowDefinition="MachineToApplication"/>
                </sectionGroup>
            </sectionGroup>
        </sectionGroup>
    </configSections>
    <appSettings/>
    <connectionStrings>

        <add name="ConnectionString" connectionString="Data Source=orcl;User ID=mario;Password=praca;Unicode=True" providerName="System.Data.OracleClient"/>
    </connectionStrings>
    <system.web>
        
<roleManager defaultProvider="OracleRoleProvider" />
<membership defaultProvider="OracleMembershipProvider" />
<compilation debug="true">
            <assemblies>
                <add assembly="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add assembly="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Data.Entity, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Data.Services, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="System.Data.Services.Client, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
                <add assembly="Oracle.DataAccess, Version=2.111.7.10, Culture=neutral, PublicKeyToken=89B483F429C47342"/></assemblies>
        </compilation>
        
        <authentication mode="Forms" />
        
        <pages>
            <controls>
                <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
                <add tagPrefix="asp" namespace="System.Web.UI.WebControls" assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            </controls>
        </pages>
        <httpHandlers>
            <remove verb="*" path="*.asmx"/>
            <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" validate="false"/>
        </httpHandlers>
        <httpModules>
            <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </httpModules>
    </system.web>
    <system.codedom>
        <compilers>
            <compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
            <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" warningLevel="4" type="Microsoft.VisualBasic.VBCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                <providerOption name="CompilerVersion" value="v3.5"/>
                <providerOption name="OptionInfer" value="true"/>
                <providerOption name="WarnAsError" value="false"/>
            </compiler>
        </compilers>
    </system.codedom>
    
    <system.webServer>
        <validation validateIntegratedModeConfiguration="false"/>
        <modules>
            <remove name="ScriptModule"/>
            <add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </modules>
        <handlers>
            <remove name="WebServiceHandlerFactory-Integrated"/>
            <remove name="ScriptHandlerFactory"/>
            <remove name="ScriptHandlerFactoryAppServices"/>
            <remove name="ScriptResource"/>
            <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
            <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
        </handlers>
    </system.webServer>
    <runtime>
        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
            </dependentAssembly>
            <dependentAssembly>
                <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
                <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
            </dependentAssembly>
        </assemblyBinding>
    </runtime>
    <system.serviceModel>
        <serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>
    </system.serviceModel>
</configuration>

Retrieving information from my database

Richard Maletta — Fri, 29 May 2009 16:34:49 GMT

I am using and Oracle database

No problem with getting data into databound controls but If I just want to return

information directly from the database I can not work it out

I have spent 2 days searching and find several posting's but they are for older versions of asp or vb so the code does not translate

2 part question

Part 1) If I have a simple textbox on a form let's call it item_number

when the user makes an entry I want to do a autopostback and

1) retrieve the description and price into two label on the form

2) if the item# entered is invalid I want to tell the user and clear the entry and let them re-enter it

Part 2 the same thing but the Item_Number is going to be inside either a detailsview or formview control How could

I create a postback on the field since I can not acces the properties of controls inside a formview or detailsview

how to work with datatype oracletype.rowid

netNewBee — Wed, 27 May 2009 17:06:59 GMT

Hello,

Could anyone please tell me how to pass in a C# string into an oracle store procedure as type oracletype.rowid and when retrieving this rowid from the store procedure in C# code?

I keep getting an error message stating invalid rowid.

My C# code to retrive the rowid :

using ( OracleCommand cmd = new OracleCommand() ) {
                cmd.Parameters.Add("out_rowid", OracleType.RowId).Direction = System.Data.ParameterDirection.Output;
  }

row_id = (cmd.Parameters["out_rowid"] == null) ? string.Empty : cmd.Parameters["out_rowid"].Value.ToString();


In the store procedure rowid is declared as: out_rowid                  OUT rowid,

Any help or suggestions is greatly appreciated.

Time table

Ozo — Fri, 20 Nov 2009 07:58:29 GMT

Can ı create a time table using ORACLE SQL, I can not use PL/SQL

09:00 10:00 11:00

Monday

Tuesday

............

BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess'

george.lee44 — Thu, 19 Nov 2009 22:35:57 GMT

I have an application build from Visual Studio 8 that is using the System.Oracle.DataAccess library to talk to an Oracle DB. This dll is version 2.111.6.20 x86 version.

When I run the application from Visual Studio, it connects, queries and displays the results correctly. This app is running on an Intel Xeon xw8600 processor using XP 2002.

But when I deploy this app on an IIS web server that is running on an AMD processor using Windows 2003 server x64, I get the following error:

BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.] System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, Visual Studio is running as a 32 bit application. I also installed ODAC Client 11.1.0.6.21 and win32_11gR1_client.zip for the Oracle Developer's Tools and Client.

I also tried installing the same Oracle Client and Developer's Tools along with VStudio onto the my Windows 2003 server and my app ran fine running under the Visual Studio environment. But when I deployed the app onto the IIS server, I got the same type of error using Oracle.DataAccess version2.111.6.20 x86 version. I then copied over a 64bit version of this dll (version 2.111.6.0 AMD64) and rebuilt /redeployed the app got a different kind of error.

So all I'm trying to do is to get my app working in an IIS environment. It works when I run it under the VStudio environment on both my client box (x86) and server (x64).

Can anyone help me on this?

Thanks in advance....

BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess'

george.lee44 — Thu, 19 Nov 2009 22:31:15 GMT

I have an application build from Visual Studio 8 that is using the System.Oracle.DataAccess library to talk to an Oracle DB. This dll is version 2.111.6.20 x86 version.

When I run the application from Visual Studio, it connects, queries and displays the results correctly. This app is running on an Intel Xeon xw8600 processor using XP 2002.

But when I deploy this app on an IIS web server that is running on an AMD processor using Windows 2003 server x64, I get the following error:

BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.] System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0 System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +416 System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +166 System.Reflection.Assembly.Load(String assemblyString) +35 System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +190

Visual Studio is running as a 32 bit application. I also installed ODAC Client 11.1.0.6.21 and win32_11gR1_client.zip for the Oracle Developer's Tools and Client.

[Exception: OCIEnvCreate failed with return code -1 but error message text was not available.] System.Data.OracleClient.OciHandle..ctor(OciHandle parentHandle, HTYPE handleType, MODE ocimode, HANDLEFLAG handleflags) +736 System.Data.OracleClient.OciEnvironmentHandle..ctor(MODE environmentMode, Boolean unicode) +41 System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) +214 System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) +157 System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +58 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +54 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +423736 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection

So all I'm trying to do is to get my app working in an IIS environment. It works when I run it under the VStudio environment on both my client box (x86) and server (x64).

Can anyone help me on this?

Thanks in advance....

How to synchronize data of different oracle database ?

anup_phansa — Tue, 17 Nov 2009 08:52:30 GMT

Hi all

I am using oracle database,

Database db1

Table: tlbdb1_City1

Database db2

Table: Tlbdb2_City2

I want to synchronize data of tlbdb1_City1 to Tlbdb2_City2

How it is possible, change of tlbdb1_City1 is automatically update at Tlbdb2_City2

Regards

Anup

Number-type output parameters - need to use ToString() first

thermalnoise — Wed, 18 Nov 2009 14:41:55 GMT

We recently upgraded our Oracle Client. I started getting this error:

Unable to cast object of type 'Oracle.DataAccess.Types.OracleDecimal' to type 'System.IConvertible'.

I noticed on this thread (http://forums.asp.net/t/1291787.aspx) that the solution is to do a Value.ToString() in the Convert.To___() method. Is there any chance of losing precision if ToString() is used?

Financial Years in a Query Result

Alekhya.Muvva — Thu, 12 Nov 2009 07:26:54 GMT

Hi all,

I need to get a result of financial years(...2006-07, 2007-08, 2008-09) starting from the date given by me to till year in oracle.

I have no idea of how to do this. I don't even know whether it is possible or not.

Any kind of help would be Appreciated.

Thanking you in advance,

Alekhya.

Tree in SQL

Ozo — Tue, 17 Nov 2009 15:52:50 GMT

How can do it in ORACLE SQL. I can not use PL/SQL

http://forums.asp.net/p/1493909/3517882.aspx#3517882

Save PDF file in oracle

hulkwsu — Mon, 16 Nov 2009 22:19:54 GMT

Hi,

Any suggestions to upload and download PDF files from ORACLE database ,in ASP.NET web application.

Thanks

“ORA-01008: not all variables bound" error message

anderson — Fri, 13 Nov 2009 18:16:19 GMT

Hi Guys,

I have a problem with filling my data table and I cannot figure out for the life of me why it is happening, I know it's something simple I just can't remember how to fix it.

       oComm.CommandText = "SELECT URLNAME, DESCRIPTION FROM DOCINFO WHERE DOCUMENT = :CustomerId";
                oComm.CommandType = CommandType.Text;
                oComm.Connection = oConn;

                

                OracleParameter oParam = new OracleParameter();

                oParam.ParameterName = ":CustomerId";

                oParam.Value = "5356070";

 

                oComm.Parameters.Add(oParam);

 

                oConn.Open();

 

                OracleDataAdapter oDa = new OracleDataAdapter(oComm.CommandText, oConn);

                oDa.Fill(dt);<--this is where the error occurs

However I keep getting Oracle “ORA-01008: not all variables bound" error message, I also tried removing the : from ParameterName.

Appreciate any help guys.

Cheers

Andy

Time problem

Ozo — Sun, 15 Nov 2009 18:04:25 GMT

in the dable the date is insert; 03/04/2009 03:00:00 PM. I want to call the time 15:00 not 03:00 PM. How can do it ?

Thanks .

Datasource query column name parameter

durulc — Sat, 14 Nov 2009 15:21:54 GMT

hi all,

can i set parameters column name in datasource select query for Oracle ?

Select id, name, sname from person p where :pColumnName = 1234

pColumnName id or islemNo, KartNo

thanks

Uploading .CSV file to Oracle Tables

Baranboz — Wed, 11 Nov 2009 09:56:24 GMT

Hey There;

In my application, I'll have a menu item for uploading and in this menu, I will upload .csv file and columns in that file will be written to the database tables in Oracle database(Oracle 11g). I haven't done this kind of application before and I need some helps from you guys please.

Lets say that I will have an upload part that I can select the path of my .csv file and I will have a button to upload the .csv file and also write the data to database tables.

How can I get the data from .csv file and write them into my Oracle database?

I hope some helpful guys can help me...

Thanks

P.S:While I was checking this on the internet, I saw some recommendation about SQL Loader but I don't know how to do it. Or can I use it for my task? If yes, how can I use it in code behind inside the upload button's click event?

Baran Bozoglu

ODBC Role Providers in .NET

ganaparthi — Tue, 10 Nov 2009 12:26:31 GMT

hi all,

iam working with asp.net and oracle database,i need to maintain different roles
in my application.

please let me know any role providers and membership providers with oracle.
like with sqlserver.

i have gone trough this artical

http://www.oracle.com/technology/obe/11gr2_db_prod/appdev/dotnet/aspprovider/aspprovider_otn.htm

here they are saying download ODAC with Oracle Developer Tools for Visual Studio
then we can use oracle providers,,

but i need to use only ODBC Driver here..

hope iam clear..please help its very urgent...

regards
ganaparthi

TNS:could not resolve the connect identifier specified

Jinaga — Fri, 27 Feb 2009 10:18:10 GMT

Hi !

I am able to connect to Oracle 10g database server through OLEDB and ODBC connection object . But I am unable to connect to through OracleClient Object . Could any one can answer on this.

Exception Inforamtion:

An unhandled exception of type 'System.Data.OracleClient.OracleException' occurred in system.data.oracleclient.dll
Additional information: ORA-12154: TNS:could not resolve the connect identifier specified

Application Software: ASP.NET & .Net Framework 1.1 , Oracle 10g

-Thank you

Interop.OracleInProcServer sdo_geometry problem

johnson340 — Tue, 10 Nov 2009 12:12:41 GMT

Hi,

I use COM library OracleInProcServer but I can not get sdo element info:

string sql = "SELECT * FROM GEOMTABLE WHERE ROWNUM <=5";
object obj = new object();
object index = new object();
int dType = 0;
bool err = false;
string value = "";
int size = 0;

OracleInProcServer.OraSession oraDBSession = (OracleInProcServer.OraSession)new OracleInProcServer.OraSessionClassClass();
OracleInProcServer.OraDatabase oraDB = (OracleInProcServer.OraDatabase)oraDBSession.get_OpenDatabase(this.dbName, this.userInfo, 0);
OracleInProcServer.OraDynaset dynSet = (OracleInProcServer.OraDynaset)oraDB.get_CreateDynaset(sql, 0, ref obj);
OracleInProcServer.OraObject oraObj;
OracleInProcServer.OraFields oraFs;
OracleInProcServer.OraField oraF;

while (!dynSet.EOF)
{
oraFs = (OracleInProcServer.OraFields)dynSet.Fields;
oraF = (OracleInProcServer.OraField)oraFs["GEOM_COLUMN"];
oraObj = (OracleInProcServer.OraObject)oraF.Value;
...

dynSet.MoveNext();
}

I could get all data types (numeric, varchar ...) however I could not get sdo_geometry type(in the example: GEOM_COLUMN).
How can I get sdo element info from oraObj?

Lots of Greetings!
Johnson

dealing with Oracle store procedures

Amanda09 — Sun, 08 Nov 2009 10:29:39 GMT

Dear all,

I'm trying to call stored procedure, but I'm having error and I don't know why it's happening…I'm new at this

Here is my code

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim cn As New OracleClient.OracleConnection
        cn.ConnectionString = "Data source=orcl; user id= jor; password= jor; Unicode=True"
        Dim cmd As New OracleCommand("Gender", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@ayear", OracleType.Number, 10).Value = DropDownList1.SelectedValue
        cmd.Parameters.Add("@asem", OracleType.Number, 10).Value = DropDownList2.SelectedValue

        Dim da As New OracleDataAdapter(cmd)
        Dim ds As New Data.DataSet

        cn.Open()
        da.Fill(ds)
        cn.Close()

        GridView1.DataSource = ds
        GridView1.DataBind()
    End Sub

And here is the procedure

REATE OR REPLACE PROCEDURE Gender (ayear IN Number, asem In Number, totm out number, totf out number)
IS
BEGIN 
   select Tot_male, tot_female
   into totm, totf
   from NEWLY_ADM_V_TOT 
   where adm_year = ayear and adm_sem= asem ;
END Gender;

And I'm getting error

ORA-01036: illegal variable name/number

I appreciate all the help you can give me

Invalid Number

Ozo — Mon, 09 Nov 2009 17:36:00 GMT

I can take "Invalid Number" error in this sql query. Whats wrong :S. One eek ago, the code it work, buk today not :( Thanks for your helping

SELECT 'The result is : ' || TO_CHAR(sp.bas_tarih,'dd.MM.yyyy') as result
FROM plan sp
INNER JOIN salon sal ON sal.salon_id=sp.salon_id
WHERE (TO_CHAR('16.09.2009 12:00','hh24:mi') < TO_CHAR(sp.finis_time,'hh24:mi') AND TO_CHAR('16.09.2009 15:00','hh24:mi') >= TO_CHAR(sp.start_time,'hh24:mi'))
AND TO_CHAR(sp.start_date,'dd.MM.yyyy') = TO_CHAR('06.10.2009','dd.MM.yyyy');

appending data from the oracle to grid using storedprocedure

ganaparthi — Sat, 07 Nov 2009 08:42:26 GMT

Hi

iam using asp.net and c# and oracle,

in my app i created one stored procedure

CREATE OR REPLACE
PROCEDURE GetCustDetails(p_recordset1 OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset1 FOR
SELECT *
    FROM CUST_INFO_TABLE;
END GetCustDetails;

now i call this stored procedure in my app like this..

OdbcCommand cmd = new OdbcCommand("GetCustDetails", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new OdbcParameter
        ("reccount", OdbcType.Cursor)).Direction = ParameterDirection.Output;

here actually i need to give curser type,but i didn't have that datatype.

but if i go for Oracle name space its available for me..

                objCmd.Parameters.Add("GetCustDetails", OracleType.Cursor).Direction = ParameterDirection.Output;

i haven't find curser type in odbc,

        OdbcDataAdapter da = new OdbcDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "cust");
        GridView1.DataSource = ds.Tables["cust"];
        GridView1.DataBind();

now which way i can solve my problem,mainly my intenstion is getting all the custmersdetails from the table through odbc,i know normal method like to sending query through odbcdataadapter,but i need to use stored procedure,

please give me any samples available..

regards
ganaparthi