Taking Excel data and inserting it into Oracle DB - OO4O and Wizards(Archived)

I have an Excel spreadsheet with loads of data I need to put into an Oracle DB. Can anyone help me out? This is extremely urgent. Thanks, Jeremy 

I don't think any of the examples show going from Excel into Oracle, only the other way 'round. You would need to write some VBA code to pull the data from the spreadsheet cells and do the insert. I'll see if I have any examples of that. 

This is my sample appication source :
================================
OraDatabase.ExecuteSQL ("truncate table krlee")
OraDatabase.AutoCommit = False
OraDatabase.ExecuteSQL ("alter session set cursor_sharing = 'FORCE'")
Worksheets("KRP_TIME_LIST").Activate
For i = InputBox("Beginning row please...") To Worksheets("KRP_TIME_LIST").Cells.SpecialCells(xlLastCell).Row Step 1
krlee = "insert into krlee values ('" + _
CStr(Cells(i, 1).VALUE) + "','" + _
CStr(Cells(i, 2).VALUE) + "','" + _
CStr(Cells(i, 3).VALUE) + "','" + _
CStr(Cells(i, 4).VALUE) + "','" + _
CStr(Cells(i, 5).VALUE) + "','" + _
CStr(Cells(i, 6).VALUE) + "','" + _
CStr(Cells(i, 7).VALUE) + "','" + _
CStr(Cells(i, 8).VALUE) + "','" + _
CStr(Cells(i, 9).VALUE) + "','" + _
CStr(Cells(i, 10).VALUE) + "','" + _
CStr(Cells(i, 11).VALUE) + "')"
OraDatabase.ExecuteSQL (krlee)
Next i
OraDatabase.ExecuteSQL ("commit")
OraDatabase.ExecuteSQL ("alter session set cursor_sharing = 'EXACT'")
MsgBox ("Loading done successfully.")
===================================
like above.

Related

No Returning Row on Table Using Bind Variable

HI all,
I'm new in .NET and Oracle.
I'm creating .NET webservice without bind variable so far is working fine, but for performance reason , I have to change the statement to use Bind Variable on it. But no row return and no error occurs also
here is my code
<WebMethod(Description:="Get Order Detail")> _
Function GetOrderDetail2(ByVal sOrderNumber As String, ByVal sOrgId As String) _
As Data.DataSet
strSQL = "select " & _
"ol.line_id, " & _
"oh.order_number, " & _
"ol.line_number, " & _
"ol.user_item_description, " & _
"ol.ordered_item, " & _
"iv.description, " & _
"ol.ordered_quantity, " & _
"ol.cancelled_quantity, " & _
"round(ol.unit_list_price,1) unit_list_price, " & _
"ol.unit_selling_price, " & _
"100-(round(ol.unit_selling_price/ol.unit_list_price,2)*100) disc, " & _
"ol.ordered_quantity*ol.unit_selling_price extended_price, " & _
"ol.ordered_quantity*ol.unit_list_price gross, " & _
"ol.shipped_quantity, " & _
"ol.flow_status_code, " & _
" '' temp, '' flag_status, '' s_prn, '' s_epm, '' d_prn, '' d_epm, '' e_prn, '' e_epm " & _
"from oe_order_headers_all oh,oe_order_lines_all ol,mtl_system_items_b iv " & _
"where oh.header_id=ol.header_id " & _
"and oh.org_id=ol.org_id " & _
"and ol.org_id=iv.organization_id " & _
"and ol.ordered_item=iv.segment1 " & _
"and oh.org_id=:sOrgId " & _
"and oh.order_number=:sOrderNumber " & _
"order by ol.line_number "
GetOracle.OpenOracleConnection()
'GetOrderDetail = GetOracle.CreateDataSet(strSQL)
' Dim oraCmd As New OracleCommand
Dim oraCmd As New OracleCommand(strSQL, oraConn)
oraCmd.CommandTimeout = 600
With oraCmd
.Connection = oraConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
Dim prmOrderNumber As New OracleParameter("SORDERNUMBER", OracleDbType.Varchar2)
'prmOrderNumber.OracleDbType = OracleDbType.Varchar2
prmOrderNumber.Value = sOrderNumber.Trim
prmOrderNumber.Direction = ParameterDirection.Input
oraCmd.Parameters.Add(prmOrderNumber)
Dim prmOrgID As New OracleParameter("SORGID", OracleDbType.Varchar2)
'prmOrgID.OracleDbType = OracleDbType.Varchar2
prmOrgID.Value = sOrgId.Trim
prmOrderNumber.Direction = ParameterDirection.Input
oraCmd.Parameters.Add(prmOrgID)
Dim oraDA As New OracleDataAdapter(oraCmd)
' Create and Fill a new DataSet.
Dim oraDS As New Data.DataSet
oraDA.Fill(oraDS, "0")
GetOrderDetail2 = oraDS
Return GetOrderDetail2
GetOracle.CloseOracleConnection()
I need your help
Regards
Anton 
I thnik you're adding parameters in wrong order. By default ODP.NET uses bind by position. If you want the parameter to bind by name, you must explicitly set oraCmd.BindByName = true
NH
Message was edited by:
nurhidayat 
Thanks Pak Dayat
Now it works
Jabat erat...
ABN

simple pop-up from portlet?

Im relatively new to plumtree, and seem to have encountered a simple problem.
We are trying to create a simple portlet that will contain a number of links to other customized pages. Our difficulty is that how within plumtree can we create a page that will pop-up?
Or are we forced to create outside .htm/.html/.asp files that will be linked to and will have to be customized to match the look and feel of the portal?
Thanks for your help.
You can do it on a Web Control (including Datagrids via Template Columns). I always registerClientScriptBlocks to keep it in the Code Behind
Code Behind: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here
'Force creation of "_doPostBack" - Plumtree Requirement. Page.GetPostBackEventReference(Me.Page)
'Register Client Side Scripting Blocks RegisterClientScriptBlocks() End Sub
Private Sub RegisterClientScriptBlocks()
RegisterClientScriptBlock(Guid.NewGuid().ToString(), _ "<script language=""JavaScript"">" & vbCrLf & _ "function _openClassAdd()" & vbCrLf & _ "{" & vbCrLf & _ "var w, h;" & vbCrLf & _ "if (document.all || document.layers)" & vbCrLf & _ "{" & vbCrLf & _ "w = screen.availWidth;" & vbCrLf & _ "h = screen.availHeight;" & vbCrLf & _ "}" & vbCrLf & _ "var popW = 595, popH = 350;" & vbCrLf & _ "var leftPos = (w-popW)/2, topPos = (h-popH)/2;" & vbCrLf & _ "window.open('ClassAdd.aspx','newin','menubar=no,toolbar=no,resizable=yes,scrollbars=yes,location=no,status=no,titlebar=no,directories=no,width=' + popW + ',height=' + popH + ',top=' + topPos + ',left=' + leftPos);" & vbCrLf & _ "}" & vbCrLf & _ "</script>")
End SubFrom the .aspx Page<TD> <asp:ImageButton id="ibtnNewClass" runat="server" CssClass="customAppText" ToolTip="Add a New Class" ImageAlign="AbsMiddle"></asp:ImageButton> <asp:LinkButton id="lnkNewClass" runat="server" CssClass="customAppText"> Add New Class</asp:LinkButton></TD>
Paul

Anyone see a SQL syntax error here?

Hey all, I am getting an error due to a SQL syntax error. I thought this looked good...but maybe a couple more sets of eyes looking at it will see what I did... here it is:stmt.executeUpdate("UPDATE member SET (Age = '" + Agetxf.getText() + "' Location = '" + Locationtxf.getText() + "' College ='" + Collegetxf.getText() + "' Major ='" + Majortxf.getText() + "' Profession='" + Professiontxf.getText() + "') WHERE UserID ='" + CreateAccount.getUserID() + "'");Thanks in advance... Atreides
Hi,
Each field should be seperated with comma as below.
stmt.executeUpdate("UPDATE member SET Age = '" + Agetxf.getText() + "', Location = '" + Locationtxf.getText() + "', College ='" + Collegetxf.getText() + "', Major ='" + Majortxf.getText() + "', Profession='" + Professiontxf.getText() + "' WHERE UserID ='" + CreateAccount.getUserID() + "'");Regards,
Ram 
Thank you very much, I'm not sure how i missed that! I'm full of careless errors tonight!
Thanks again!
Atreides 
This cries out to be a converted to use a PreparedStatement. 
sabre150 wrote:
This cries out to be a converted to use a PreparedStatement.And the rest of us just cry when we see it. ;-)

Automation Error

Here is my code to get items from an Excel Spreadsheet to Oracle:
Sub Insert_Data()
Dim OraSession As Object
Dim OraDatabase As Object
Dim ACellVar As String
Dim R As Long
' *** Change starting address to suit ***
Set ACellRange = Range("A2")
R = 1
Do
If IsEmpty(ACellRange.Cells(R, 1)) Then Exit Do
'ACellVar = ACellRange.Cells(R, 1).Text
PSIMKEY = Val(ACellRange.Cells(R, 1).Text)
PSCPKEY = Val(ACellRange.Cells(R, 2).Text)
PSPIECENO = Val(ACellRange.Cells(R, 3).Text)
PSCODE = Val(ACellRange.Cells(R, 4).Text)
PSCRDATE = Format$(Now, "dd-mmm-yyyy")
PSMDDATE = Format$(Now, "dd-mmm-yyyy")
PSOPNUM = Val(ACellRange.Cells(R, 7).Text)
PSDIM1 = Val(ACellRange.Cells(R, 8).Text)
PSDIM2 = Val(ACellRange.Cells(R, 9).Text)
PSDIM3 = Val(ACellRange.Cells(R, 10).Text)
PSQTYP = Val(ACellRange.Cells(R, 11).Text)
PSQTYL = Val(ACellRange.Cells(R, 12).Text)
PSSRATE = Val(ACellRange.Cells(R, 13).Text)
PSOFFSET = Val(ACellRange.Cells(R, 14).Text)
PSESTCOST = Val(ACellRange.Cells(R, 15).Text)
PSEDATE = Format$(Now, "dd-mmm-yyyy")
PSDDATE = Format$(Now, "dd-mmm-yyyy")
PSREV = Val(ACellRange.Cells(R, 18).Text)
PSTYPE = Val(ACellRange.Cells(R, 19).Text)
PSECODE = Val(ACellRange.Cells(R, 20).Text)
PSDESCR = Val(ACellRange.Cells(R, 21).Text)
PSCERT1 = Val(ACellRange.Cells(R, 22).Text)
PSCERT2 = Val(ACellRange.Cells(R, 23).Text)
PSCERT3 = Val(ACellRange.Cells(R, 24).Text)
PSCERT4 = Val(ACellRange.Cells(R, 25).Text)
R = R + 1
'Insert Rows
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("QSDB", "QSDB_USER/plethora", 0&)
OraDatabase.ExecuteSQL ("INSERT INTO PS (PS_IMKEY,PS_CPKEY,PS_PIECE_NO,PS_CODE" & _
"PS_CRDATE,PS_MDDATE,PS_OP_NUM,PS_DIM_1,PS_DIM_2,PS_DIM_3,PS_QTY_P,PS_QTY_L" & _
"PS_S_RATE,PS_OFFSET,PS_EST_COST,PS_E_DATE,PS_D_DATE,PS_REV,PS_TYPE,PS_E_CODE" & _
"PS_DESCR,PS_CERT1,PS_CERT2,PS_CERT3,PS_CERT4) VALUES ('" & PSIMKEY & "','" & _
PSCPKEY & "','" & PSPIECENO & "','" & PSCODE & "','" & PSCRDATE & "','" & _
PSMDDATE & "','" & PSOPNUM & "','" & PSDIM1 & "','" & PSDIM2 & "','" & PSDIM3 & _
"','" & PSQTYP & "','" & PSQTYL & "','" & PSSRATE & "','" & PSOFFSET & "','" & _
PSESTCOST & "','" & PSEDATE & "','" & PSDDATE & "','" & PSREV & "','" & PSTYPE & _
"','" & PSECODE & "','" & PSCERT1 & "','" & PSCERT2 & "','" & PSCERT3 & "','" & _
PSCERT4 & "')")
Loop
End Sub
I keep getting the "Automation Error" error and it doesn't tell my why or where. Can someone help? Thanks, Jeremy 
I have fixed the coding error where I'm missing a comma between column names in the insert statement. This didn't resolve my problem with the "Automation Error". Thanks, Jeremy

OracleParameter Problems

Hello,
I'm having problems when I use OracleParameter's in the WHERE clausule from a SELECT. The problem is that the SELECT doesn't return any row, but if I perform the SELECT without parameters it returns rows. Example code in VB.NET :
command.Connection = con
command.CommandText = "SELECT " & _
"STD_RECIPE " & _
"FROM " & _
"JOB_STAGE " & _
"WHERE " & _
"COMPANY_CODE = :pCOMPANY_CODE AND " & _
"FACTORY = :pFACTORY AND " & _
"JOB_NUMBER = :pJOB_NUMBER AND " & _
"PROCESS_STAGE = :pPROCESS_STAGE "
command.Parameters.Add(New OracleParameter("pCOMPANY_CODE", "S"))
command.Parameters.Add(New OracleParameter("pFACTORY", "F2"))
command.Parameters.Add(New OracleParameter("pJOB_NUMBER", "114"))
command.Parameters.Add(New OracleParameter("pPROCESS_STAGE", "FUSION"))
strRecipeCode = command.ExecuteScalar()
I get strRecipeCode equals to Nothing
But if i don't use parameters this works fine :
command.Connection = con
command.CommandText = "SELECT " & _
"STD_RECIPE " & _
"FROM " & _
"JOB_STAGE " & _
"WHERE " & _
"COMPANY_CODE = 'S' AND " & _
"FACTORY = 'F2' AND " & _
"JOB_NUMBER = '114' AND " & _
"PROCESS_STAGE = 'FUSION' "
strRecipeCode = command.ExecuteScalar()
strRecipeCode gets the correct value.
I had got this problem developing with Java, but there was an option FIXEDCHAR for solve this problem, but VB.NET has not this option.
How can solve this problem ???
[snip]
I had got this problem developing with Java, but
there was an option FIXEDCHAR for solve this problem,
but VB.NET has not this option.
How can solve this problem ??? You solve it basically the same way.
This is SQL miscompare comparing a Varchar2 to a Char.
All of your OracleParameters are Varchar2 parameters, since this is the default mapping for String's.
When creating OracleParameter object to compare to a Char column, you must explicitly set the OracleType to Char, and set the length. Then set the value to the string values.
David
Thank you very much, this work fine

Categories

Resources