Home » Infrastructure » Windows » Any Easier Way? (Oracle 11.2g Express)
Any Easier Way? [message #559281] Sun, 01 July 2012 14:12 Go to next message
jbm417
Messages: 9
Registered: June 2012
Junior Member
Below is what I used to create a user defined type and associated table.
All this works just fine.

CREATE type person_type AS object (pname varchar2(30), address varchar2(60), age
 number(3)); 

CREATE TABLE person_table 
  ( 
     id     NUMBER(5), 
     person PERSON_TYPE 
  ); 

INSERT INTO person_table 
            VALUES ('123', Person_type ('ABC', 'One Street', '68'));  


When I access the table I use the code below which works just fine meaning that
objPersonBO contains all the data of Person_Type.

"SELECT * FROM person_table;
..... usual open, cmd stuff ....
OracleDataReader objReader = objCmd.ExecuteReader();
while (objReader.Read())
                {
                objPersonBO = (PersonBO)objReader["Person"];
                ...
                }       


Now the question --

I would like to use a different INSERT statement to have a more consistent syntax.
For example,

INSERT INTO person_table VALUES ('123', objPersonBO);

where objPersonBO has previously been given the data to be stored.

Can anyone show me a way? TIA, Joe
Re: Any Easier Way? [message #559282 is a reply to message #559281] Sun, 01 July 2012 14:26 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
>where objPersonBO has previously been given the data to be stored.
what datatype is OBJPERSONBO?

do you realize that SQL is different from PL/SQL & what may be valid in PL/SQL may not be valid in SQL?
INSERT is pure/plain SQL.
Re: Any Easier Way? [message #559283 is a reply to message #559282] Sun, 01 July 2012 15:00 Go to previous messageGo to next message
jbm417
Messages: 9
Registered: June 2012
Junior Member
The class definition of PersonBO contains

[OracleCustomTypeMappingAttribute("PERSON_TYPE")]

which I expect will make the type of objPersonBO be
compatible with PERSON_TYPE.

Yes, I do understand PL/SQL has more capabilities than plain SQL.

OracleDataReader knows how to map the results of SELECT * ....
into objPersonBO. So I wondered if there was a comparable mapping
that was done by commnad.ExecuteNonQuery with INSERT.
Re: Any Easier Way? [message #559287 is a reply to message #559283] Mon, 02 July 2012 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My advice: do NOT use object features.
Oracle is FIRST a relational database and not an object one. Using object feature just means hard to write, read and maintain code and poor performances.

Regards
Michel
Re: Any Easier Way? [message #559299 is a reply to message #559283] Mon, 02 July 2012 01:54 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
Just curious: how are you "mapping" basic data types (e.g. INTEGER)?
You do not hardcode them to the INSERT statement string, do you?
If so, just read this article: http://www.akadia.com/services/ora_bind_variables.html
Explore the ability of binding in your Command class (whichever language/environment you use).
Re: Any Easier Way? [message #559323 is a reply to message #559299] Mon, 02 July 2012 10:19 Go to previous messageGo to next message
jbm417
Messages: 9
Registered: June 2012
Junior Member
Thanks to Michel & Flyboy for your responses.

The only reason I was exploring Oracle objects is because some databases I encounter already use objects. I have no plans to actually design a new db using objects. Although there is some likelyhood I may run across a situation where conversion from/to Oracle may require the use of objects.

As for mapping basic data types such as Integer - No. The only mapping is done to deal with user defined types. I will read the link to learn about binding - Thanks.

As suggested by BlackSwan, I have posted this question on an Oracle .Net forum which may be more appropriate.

Tnx, Joe
Re: Any Easier Way? [message #559355 is a reply to message #559323] Mon, 02 July 2012 14:26 Go to previous messageGo to next message
flyboy
Messages: 1776
Registered: November 2006
Senior Member
Regarding "mapping" basic data types - what does '123' in your example represent? Is it still the same constant or content of some other character variable? Yes, it would be hardcoding in the second case.

Regarding binding in .NET framework: maybe it would be useful to primarily consult its documentation. The example in this article seems to clearly show it: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare
Re: Any Easier Way? [message #559483 is a reply to message #559355] Tue, 03 July 2012 09:30 Go to previous messageGo to next message
jbm417
Messages: 9
Registered: June 2012
Junior Member
I studied the link info & this appears to be exactly what I need. However I cannot make it work in Oracle. I created the example on SqlServer & it works fine.

Here is what I have with Oracle that causes the ORA-00936 missing expression error when attempting the cmd.ExecuteNonQuery. The problem seems to be the @id is not getting the value assigned because if I replace @id in the INSERT with a number, execution is fine.

string sql = "Insert into Person_Table (ID) Values(@id)";

OracleConnection objCon = new OracleConnection("user id=HR;password=HR;data source=Hummer/XE");
objCon.Open(); 
OracleCommand cmd = new OracleCommand(null, objCon);
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;            
OracleParameter idParam = new OracleParameter("@id", OracleDbType.Int32, 5);
idParam.Direction = ParameterDirection.Input;
idParam.Value = 222;
cmd.Parameters.Add(idParam);

cmd.Prepare();
cmd.ExecuteNonQuery();

Any help will be very much appreciated. TIA, Joe
Re: Any Easier Way? [message #559513 is a reply to message #559483] Tue, 03 July 2012 12:29 Go to previous messageGo to next message
Flyby
Messages: 146
Registered: March 2011
Location: Belgium
Senior Member
aren't bindparameters defined with : instead of @
also check the .bindbyname=true on the command

[Updated on: Tue, 03 July 2012 12:31]

Report message to a moderator

Re: Any Easier Way? [message #559540 is a reply to message #559513] Tue, 03 July 2012 20:53 Go to previous message
jbm417
Messages: 9
Registered: June 2012
Junior Member
Thanks flyby! The use of : completely fixed the problem. Code now works as expected. Because I am just learning about bindparameters, I had no clue.

I really appreciate your help.
Previous Topic: Oracle 11g in windows 7
Next Topic: How to pass null values in OracleCommand as a parameters
Goto Forum:
  


Current Time: Sat Oct 25 10:37:37 CDT 2014

Total time taken to generate the page: 0.11153 seconds