Oracle Sequence Numbers in ADO.NET...


In TOAD I can do something like:

CREATE SEQUENCE seq_instance_id;
SET SERVEROUTPUT
ON
DECLARE x t_run_record.instance_id
%TYPE;
BEGIN
   
INSERT INTO
T_RUN_RECORD
   
(instance_id, ae2e_key_id, rec_status_id, run_id ,user_id, status_note, rec_exists_yn, case_id_number, ponder, echid, ltmgr, pididid, scididid, target, design, version_num
)
   
VALUES
   
(seq_instance_id.NEXTVAL, 11111, 3, 8, "TEST", "Test record inserted by me.", "Y", -99999, "BA1100NSTRING", "", "ID10T-FORM", "", "", "ADMIN", -99999, ""
)
   
RETURNING
instance_id
   
INTO x
;
   
dbms_output.put_line(x
);
END;

How do I convert this to a command with a return value in ADO.NET (Framework 1.1)?

TIA


Keith 'StarPilot' Barrows



Home SIte | The Terran Institute | ASPInsiders

Ad Astra Per Aspera - "To the stars thru difficulties"
0
StarPilot
2/7/2006 9:49:15 PM
asp.net.oracle 2269 articles. 0 followers. Follow

5 Replies
1431 Views

Similar Articles

[PageSpeed] 7

create or replace function get_sequence_nextval
  return PLS_INTEGER
  IS
     v_seq PLS_INTEGER;
begin
select seq_instance_id.nextval
  into v_seq
     from dual;
  RETURN v_seq;
end get_sequence_nextval;

Call the above function from your asp.net code using ExecuteScalar() and use an assignment statement to assign the returned sequence to a variable.

int sequence = Convert.ToInt32(mycommand.ExecuteScalar());

--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
2/7/2006 10:50:17 PM

Is there a way to do this without creating a function/package on Oracle?  In other words, can this be done purely from the ADO.NET side of things?  (The preference where I work is for inline coding for SQL and Oracle.)

Also, this needs to be part of an INSERT statement, not just a SELECT Sequence.

Thanks!


Keith 'StarPilot' Barrows



Home SIte | The Terran Institute | ASPInsiders

Ad Astra Per Aspera - "To the stars thru difficulties"
0
StarPilot
2/7/2006 11:40:03 PM
You will have to run the select statement that I posted from asp.net and assign it to a variable. 
Then you can use that variable in an INSERT statement.

You cannot do this:

INSERT into mytable VALUES(seq_id.nextval);

Oracle does not permit accessing a sequence file from an anonymous block.
Retrieving the next sequence number is probably the biggest use of the dual table in Oracle because of  this.

Hope this helps, happy coding.

--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
2/8/2006 12:13:23 AM

Huh?

I do exactly that all the time, for example I ran this just today:

insert into terms values (terms_seq.nextval, 'Myt-1 pS426 (MIL-9)', (select termtypeid from termtype where typename = 'Antigens') , 1);

And what is a sequence file? A sequence is an Oracle object, like a table or a function. Do a select from user_objects.

create sequence seq_id start with 1 increment by 1 nocache;

0
ASPDavid
2/21/2006 8:47:52 PM
My bad...you can access a sequence's nextval from SQL, but not from PL/SQL.
I'm used to writing procedures/functions in PL/SQL for all my database programming.
As you probably know inline SQL coding in not recommended.

Yes, sequence is an object. I just use the terms file/object interchangeably which in not a good idea.

--dweezil

SELECT * FROM users WHERE clue = 'yes';

Records found: 0
0
dweezil
2/22/2006 1:22:54 PM
Reply:

Similar Artilces:

urgent: .NET Provider for Oracle or Oracle Data Provider for .NET
If I want to access Oracle DB in ASP.NET application, and I am using .NET Provider for Oracle or Oracle Data Provider for .NET, should I have to install OracleClient on my .NET server? Is there other choice? Thank you very much.Johnson Johnson2007:should I have to install OracleClient on my .NET server?  You have to options1. install the ODP.NET on your web serverOR2. put the oracle assemblies in your BIN directory   Hi jimmy,Thank you for you reply. Because I could not find a server right now, I should test it first before I agree or disagree with you, as most articles I...

Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...

Passing boolean values to oracle function / procedure from C#.Net
Hi,  I dont know how to pass boolean values to oracle function / procedure arguments, could you please help me to know ?  Thank you, Sreedhar After spending lot of time on google, I understand C#.Net doest support PL/SQL boolean values. Thanks Sreedhar...

which is shou i use? Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NET
Microsoft .NET Framework 1.1 Data Provider for Oracle and the Oracle Data Provider for .NETI am confused?Which is better or which one should I use?BTW, I am going to use .net 2.0 with oracle 9. Can anyone tell me what shou I use for working with oracle database?Thanks very much in advance!Justin Oracle's data provider has more functionality than Microsoft's.If your doing transaction processing you can have SAVEPOINTs, which Microsoft's data provider does not provide.Also, Oracle has PL/SQL Associative Arrays which are very powerful.Oracle also allows you to bind parameters by name and pos...

Oracle Spatial support in Devart's ADO.NET Data Provider for Oracle [Edit]
dotConnect for oracle v. 7.3 http://www.devart.com/dotconnect/oracle/ , ADO.NET Provider from Devart, includes support for Oracle Spatial in the projects, using Entity Framework. Devart Team constantly implements all the available Entity Framework innovations, and shortly after the introduction of DbGeometry and DbGeography classes in Entity Framework 5 has implemented support for Oracle Spatial in the Entity Framework provider for Oracle. Oracle MDSYS.SDO_GEOMETRY object type can now be mapped to DbGeometry and DbGeography classes. Now dotConnect for Oracle includes new versions of Devar...

.NET Oracle Provider
Hi all,I am using .NET 2.0 Frameworks Oracle provider and it does not have a OracleDbTypes Type for me to use. I would like to create a parameter that is of the Oracle type NVARCHAR, but it seems I can only use DbTypes, which does not have this. If I used DbTypes.String, would this be ok, otherwise, how else can I be more specific about the Oracle Parameter data type?Thanks  can u explain which type of data u r going to storeThank uBaba Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. What's your version of ODP? You have OracleD...

Oracle ODP.net mulitple Oracle Client
To connect to the oracle database using ODP.net, the applicate must add the dll file called Oracle.DataAccess. There are a lot of version of Oracle.DataAccess. As I know, if the client use Oracle Client 10g Release 2. If the client use Oracle Client 10g Release 2, the application also have to use the Oracle.DataAccess the come from Oracle Client 10g Release 2.  My question is that there are any method to add more than one Oracle.DataAccess version in my product to support multiple OracleClient.   That should automatically happen for the most part.  Oracle's ODP.NET i...

Which .Net Provider for Oracle 9i Microsoft or Oracle
Can anyone provide me pros and cons of provider to access Oracle Database ? I am aware of Microsoft Provider and Oracle Provider. Microsoft http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/manprooracperf.asp Oracle http://otn.oracle.com/software/tech/windows/odpnet/utilsoft.html I guess you can use either. But as some post suggests, if both installed, then MS ODP will not work. I've been using DataDirect Technologies Connect for .Net and Oracle. It's not free, so it depends on your budget. The speed improvements were great for us, and it's ni...

OleDb.Net vs. ODBC.Net Connection to Oracle
I want to connect to an Oracle database, and I understand that I can use either the OleDB.Net data provider or the ODBC.Net data provider for my connection. Does it matter which one I use? What are the advantages and disadvantages of each? Would appreciate any input. ODBC is slower than OleDb. because ODBC has to talk to OleDbProvider then OleDbProvider will talk to Oracle Db. Instead if you are able to use OleDb straight to DB then it will be faster. If you only have THOSE choices, I would take OleDb over ODBC any day. If you are using Framework 1.1, you have the choice of using th...

How use unicode in Oracle with ADO.NET
I created one table in Oracle by statement : create table Test2 (id integer, Ten nvarchar2(50) ) and I write one application by C# to insert data into Oracle Database : OleDbConnection conn = new OleDbConnection("Provider=MSDAORA;Data Source=hbk;Persist Security Info=True;User ID=hbk;Password=hbk;Unicode=True;"); conn.Open(); OleDbCommand cmd = new OleDbCommand("insert into Test2(ID,Ten) values(10,N'Nguyễn thị hồng nhung')", conn); cmd.ExecuteNonQuery(); conn.Close(); I retrieved data that inserted by snip code: OleDbConnection conn = new OleDbConnection("Pro...

Oracle and .Net
I realize this is an open ended question.  I've written several applications that use Access databases as input.  All of my reading, writing, deleting and updating to these databases were always through Oledbconnections; then creating Sql parameter queries or just creating queries in code to manage data.  I may be getting involved with an application whose data is kept in an Oracle database.  How much different is it from obtaining data from Access databases?  Does the same theories apply:  Create a connection, open the connection, r...

vb.net function and Oracle procedure to get an Oracle Dataset??
I have similar code (a procedure and function) working perfectly with a sql server data source. I;'m trying to do the same things with Oracle, but have run into the problems.    I'm not an oracle expert, so I'm not sure if producing a cursor is my only and best  option. create or replace package body PK_RETAILER isPROCEDURE sp_Get_Order_History(v_ani IN number,output_cursor out PK_CURSORES.generic_cursor) ASBEGINopen output_cursor forselect o.account_number, p.phone, o.amount, o.name, o.init_datefrom orders ojoin users_phones p on p.account_number...

How To call Oracle function from ADO.NET?
How To call Oracle function from ADO.NET? The simple function takes 2 integer arguments and returns string. I want to call this function from my C# code. Thanks the sql would be select function(param1,param2) as result from dual; where function is your function, an param* are your two arguements. The result will come back as recordset("result"). Best not use result because it could quite easily be reserved word. Hi, Or you can use ExecuteNonQuery and access the return param directly. Example follows. Greg /* create or replace function myf...

ADO.NET Oracle help needed!
I'm just starting with ADO.NET and ASP.NET. I have been working with ADO for a while now, but I seem to be stuck on ADO.NET. I don't understand why the following code brings back an error message saying "Value cannot be null. Parameter name: Dataset." and highlights the DA.Fill(DS,"grade") line, saying that is where the problem is. Can anyone help me figure this out? Most of my readings on ADO.NET talk about SQLserver...not exactly much help there. Thanks! Dim DS As DataSet Dim MyConnection As OleDBConnection Dim DA As New OleDBDataAdapter ...

Web resources about - Oracle Sequence Numbers in ADO.NET... - asp.net.oracle

Sequence - Wikipedia, the free encyclopedia
"Sequential" redirects here. For the manual transmission, see Sequential manual transmission . For other uses, see Sequence (disambiguation) ...

Sequence - Wikipedia, the free encyclopedia
"Sequential" redirects here. For the manual transmission, see Sequential manual transmission . For other uses, see Sequence (disambiguation) ...

TV title sequences that are as unmissable as the shows that follow
TV credit sequences were once a mere formality, but not any more.

'Batman v. Superman: Dawn of Justice': Lego set may have revealed major battle sequence; Connection to ...
Battle scene involving Lex Luthor, Batman, Superman and Wonder Woman reportedly teased in Lego toy set.

Join digits for big points in Sequence – Connecting Numbers
Sequence - Connecting Numbers 13 Thieves Threes! Two Dots The Mesh Join digits for big points in Sequence – Connecting Numbers is a story ...

Guru denied copyright protection for Bikram yoga sequence of postures
Bikram Choudhury atop a student. (credit: tiarescott ) Sure, DC Comics can copyright the Batmobile . However, Yoga guru Bikram Choudhury, infamous ...

The director of last night's episode of 'The Leftovers' explains that bizarre opening sequence
Warning: Spoilers ahead HBO's "The Leftovers" kicked off its second season Sunday night and didn't hold back on continuing to test its audience ...

"What In The World Just Happened There!?" Maryland & Northwestern Combine For Wild Sequence
Northwestern is sporting its best men’s basketball team in years—maybe ever, given that the Wildcats have never, ever made the NCAA tournament—and ...

'American Idol's' Final Season Opening Sequence Is Pure Nostalgia
The farewell season of 'Idol' is around the corner, and it's time to take a walk down memory lane.

Court: Hot Yoga's Sequence of Poses Can't Be Copyrighted
The founder of a popular form of yoga that is performed in a room heated to more than 100 degrees lost a court appeal Thursday to copyright a ...

Resources last updated: 1/4/2016 10:41:31 PM