error when execution an Oracle stored procedure

Question

I have a small project where I try to insert some values into Oracle via store procedure from a vb.net interface

Here are the codes:

Oracle codes

CREATE SEQUENCE my_doc_seq INCREMENT BY 1 START WITH 1 MAXVALUE 999999 MINVALUE 1;
/
CREATE TABLE MY_DOC 
   (ID NUMBER PRIMARY KEY, 
    FILE_NAME VARCHAR2(255 BYTE), 
    UPLOAD_DATE VARCHAR2(10 BYTE), 
    FILESIZE VARCHAR2(20 BYTE), 
    FILETYPE VARCHAR2(5 BYTE), 
    CONTENT BFILE, 
    CREATION_DATE DATE DEFAULT SYSDATE NOT NULL ENABLE, 
    MODIFICATION_DATE DATE, 
    ACCESSED_DATE DATE)
/
CREATE INDEX MY_DOC_IDX ON MY_DOC (CONTENT) INDEXTYPE IS CTXSYS.CONTEXT ;
/
CREATE OR REPLACE DIRECTORY DOCUMENTS AS 'D:\docs';
/
create or replace PROCEDURE put_file
 (
  p_file_name         IN my_doc.file_name%TYPE,
  p_upload_date       IN my_doc.upload_date%TYPE,
  p_filesize          IN my_doc.filesize%TYPE,
  p_filetype          IN my_doc.filetype%TYPE,
  p_creation_date     IN my_doc.creation_date%TYPE, 
  p_modification_date IN my_doc.modification_date%TYPE, 
  p_accessed_date     IN my_doc.accessed_date%TYPE
) AS
 BEGIN
   INSERT INTO my_doc (id, file_name, upload_date, filesize, filetype, content, creation_date, modification_date, accessed_date)
   VALUES (my_doc_seq.NEXTVAL, p_file_name, p_upload_date, p_filesize, p_filetype, BFILENAME('DOCUMENTS',p_file_name), p_creation_date, p_modification_date, p_accessed_date);
   COMMIT;

END;

VB.NET codes

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Try
        Dim strFolder As String = "D:\docs\"
        Dim connectionString As String = "Data Source=orcl;Persist Security Info=True;User ID=my_user;Password=my_password"
        Dim connection As New OracleConnection(connectionString)
        Dim fso As New Scripting.FileSystemObject()
        Dim oFolder As Scripting.Folder = fso.GetFolder(strFolder)

        connection.Open()

        For Each oFile As String In Directory.GetFiles(strFolder)
        Dim strCmd As String = "licenta.put_file('" & New FileInfo(oFile).Name & "', " & _
        "SYSDATE, '" & _
        New FileInfo(oFile).Length & "', '" & _
        New FileInfo(oFile).Extension & "', '" & _
        New FileInfo(oFile).CreationTime.ToString("dd-MMM-yyyy") & "', '" & _
        New FileInfo(oFile).LastWriteTime.ToString("dd-MMM-yyyy") & "', '" & _
        New FileInfo(oFile).LastAccessTime.ToString("dd-MMM-yyyy") & "')"

        Dim cmd As New OracleCommand
        cmd.Connection = connection

        cmd.CommandText = strCmd
        cmd.CommandType = CommandType.Text

        Dim temp As Integer = cmd.ExecuteNonQuery()

        If temp > 0 Then
            MessageBox.Show("File name " & New FileInfo(oFile).Name & " inserted!")
        Else
            MessageBox.Show("An error occurred, file name " & New FileInfo(oFile).Name & " not inserted!")
        End If

        MessageBox.Show("strCmd= " & strCmd)
        Next

        connection.Close()
        strFolder = Nothing
        Catch

        End Try
    End Sub

When I am executing the VB code,I receive the following error on CATCH but the string I am generating in "strCmd" works just fine when I put it directly in Oracle.

ORA-00900: invalid SQL statement

Can anyone help me to debug this, please?

Many thanks, Mikcutu.

NOTE: I also tried with parameterised query, code below:

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Try
        Dim strFolder As String = "D:\docs\"
        Dim connectionString As String = "Data Source=orcl;Persist Security Info=True;User ID=my_user;Password=my_password"
        Dim connection As New OracleConnection(connectionString)
        Dim fso As New Scripting.FileSystemObject()
        Dim oFolder As Scripting.Folder = fso.GetFolder(strFolder)

        connection.Open()

        For Each oFile As String In Directory.GetFiles(strFolder)

            Dim cmd As New OracleCommand
            cmd.Connection = connection

            cmd.Parameters.Add(New FileInfo(oFile).Name, OracleDbType.Varchar2, 255).Direction = ParameterDirection.Input
            cmd.Parameters.Add(Date.Now().ToString("dd-MMM-yyyy"), OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters.Add(ToString(New FileInfo(oFile).Length), OracleDbType.Int64).Direction = ParameterDirection.Input
            cmd.Parameters.Add(New FileInfo(oFile).Extension, OracleDbType.Varchar2, 5).Direction = ParameterDirection.Input
            cmd.Parameters.Add(New FileInfo(oFile).CreationTime.ToString("dd-MMM-yyyy"), OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters.Add(New FileInfo(oFile).LastWriteTime.ToString("dd-MMM-yyyy"), OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters.Add(New FileInfo(oFile).LastAccessTime.ToString("dd-MMM-yyyy"), OracleDbType.Date).Direction = ParameterDirection.Input

            cmd.CommandText = "put_file"
            cmd.CommandType = CommandType.StoredProcedure
            Dim temp As Integer = cmd.ExecuteNonQuery()

            If temp > 0 Then
                MessageBox.Show("File name " & New FileInfo(oFile).Name & " inserted!")
            Else
                MessageBox.Show("An error occurred, file name " & New FileInfo(oFile).Name & " not inserted!")
            End If
          connection.Close()
            strFolder = Nothing
    Catch
End Try
End Sub

What error and where is it occurring - and I don't just mean in your `Try Catch`? — Ally, Mar 01 '17 at 10:32
In addition to Alex M's request for more info, can the database server see the D:\docs directory? (I.e. you're not trying to access a directory on your client machine that the database server doesn't have mapped) — Boneist, Mar 01 '17 at 10:44
[Use parameterised queries.](http://stackoverflow.com/q/811481/1509264) — MT0, Mar 01 '17 at 11:03
@Boneist, Yes, the databse can see the D:\doc directory, as the "strCmd" string can be successfully executed in database — mikcutu, Mar 01 '17 at 13:30
@MT0, with parameterised query, I have other error: "ORA-01400: cannot insert NULL into ("MY_DOC"."CREATION_DATE") ORA-06512: at "PUT_FILE", line 12 ORA-06512: at line 1" — mikcutu, Mar 01 '17 at 13:47

score 0 · Accepted Answer · answered Mar 02 '17 at 08:32

In the end, I found the solution. Even if I tried the parameterised query, I was not using it correctly (not providing values for the parameters. I just defined them). Here is the solution:

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Try
        Dim strFolder As String = "D:\docs\"
        Dim connectionString As String = "Data Source=orcl;Persist Security Info=True;User ID=my_user;Password=my_password"
        Dim connection As New OracleConnection(connectionString)
        Dim fso As New Scripting.FileSystemObject()
        Dim oFolder As Scripting.Folder = fso.GetFolder(strFolder)

        connection.Open()

        For Each oFile As String In Directory.GetFiles(strFolder)

            Dim cmd As New OracleCommand
            cmd.Connection = connection

            cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, 255).Direction = ParameterDirection.Input
            cmd.Parameters("p_file_name").Value = New FileInfo(oFile).Name

            cmd.Parameters.Add("p_upload_date", OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters("p_upload_date").Value = Date.Now()

            cmd.Parameters.Add("p_filesize", OracleDbType.Int64).Direction = ParameterDirection.Input
            cmd.Parameters("p_filesize").Value = New FileInfo(oFile).Length

            cmd.Parameters.Add("p_filetype", OracleDbType.Varchar2, 5).Direction = ParameterDirection.Input
            cmd.Parameters("p_filetype").Value = New FileInfo(oFile).Extension

            cmd.Parameters.Add("p_creation_date", OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters("p_creation_date").Value = New FileInfo(oFile).CreationTime

            cmd.Parameters.Add("p_modification_date", OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters("p_modification_date").Value = New FileInfo(oFile).LastWriteTime

            cmd.Parameters.Add("p_accessed_date", OracleDbType.Date).Direction = ParameterDirection.Input
            cmd.Parameters("p_accessed_date").Value = New FileInfo(oFile).LastAccessTime

            cmd.CommandText = "put_file"
            cmd.CommandType = CommandType.StoredProcedure
            Dim temp As Integer = cmd.ExecuteNonQuery()

            If temp > 0 Then
                MessageBox.Show("File name " & New FileInfo(oFile).Name & " inserted!")
            Else
                MessageBox.Show("An error occurred, file name " & New FileInfo(oFile).Name & " not inserted!")
            End If

            connection.Close()
            strFolder = Nothing
        Next
    Catch
    End Try
End Sub

error when execution an Oracle stored procedure

1 Answers1