Solved

ORA-01036 Got this error when I called Oracle stored procedure from VB.NET

Posted on 2004-08-18
8
11,813 Views
Last Modified: 2011-08-18
Hi

I am Sandeep. Workking in Iflex solutions Bangalore.
I am trying to get rid of the error “ORA-01036” Illegal Parameter Name/Value.
While browsing the net for resolving this problem. I found that you also faced the same problem.
I am using VB.NET at front end and Oracle at  backend.
May I get a solution for this problem.
Thanks in Advance.

Regards,
Sandeep Shivathaya S.

Following is the body of stored procedure I am using this stored procedure is working fine with VB. But when I am trying to call from VB.NET I have the problem

PROCEDURE MODIFY_TABLE(REFER_NO IN VARCHAR2,
                    PROJECT_NAME IN      VARCHAR2,
                 OB_NAME IN VARCHAR2,
                ObCode IN LONG:=null,
                  ObConSeq IN VARCHAR2,
                  pSql IN OUT      VARCHAR2
)
IS
SINGLE_ROW             TB_DETAILS_TABLE%ROWTYPE;
CURSOR COLUMN_DATA IS       SELECT *
                        FROM TB_DETAILS_TABLE
                        WHERE REF_NO = REFER_NO AND PROJECTNAME = PROJECT_NAME
                        ORDER BY SERIAL_NO;
PRIMARY_ROW       TB_DETAILS_TABLE%ROWTYPE;
CURSOR PRIMARY_DATA IS SELECT *
                        FROM TB_DETAILS_TABLE
                        WHERE REF_NO = REFER_NO AND PROJECTNAME = PROJECT_NAME
                        AND PK_CONSTRAINT = 'Y'
                        ORDER BY PK_POSITION;
CURSOR PREVIOUS_PKEY(CONSNAME VARCHAR2) IS SELECT COLUMN_NAME
                                           FROM USER_CONS_COLUMNS
                                           WHERE CONSTRAINT_NAME = CONSNAME
                                           ORDER BY POSITION;
TEMP_VAR                   LONG;
TEMP1_VAR                   LONG;
--added by Praveen
UPD_VAR                             VARCHAR2(2000);
--End
TEMP2_VAR           LONG;
CodeStr                  LONG;
P_KEY                   VARCHAR2(2000);
FIRST_TIME                   NUMBER :=0;
NO_OF_ROWS                   NUMBER;
LAST_ROW                   NUMBER :=0;
FLAG                         NUMBER :=0;
SINGLE_COL                   VARCHAR2(2000);
CONS_NAME                   VARCHAR2(30);
COLNAME                   VARCHAR2(30);
PRE_PKEY                   VARCHAR2(2000);
CONS_VAR                   VARCHAR(2000);
TABLE_IN_SYS             NUMBER;
TABLE_IN_TBMAS             NUMBER;
PromptStr                  VARCHAR2(100) := '';
--file            utl_file.file_type;
BEGIN
--      file := utl_file.fopen('/tmp/' , 'err','w');
      SELECT COUNT(*) INTO NO_OF_ROWS
      FROM TB_DETAILS_TABLE
      WHERE REF_NO = REFER_NO AND PROJECTNAME = PROJECT_NAME;
      SELECT COUNT(*) INTO TABLE_IN_TBMAS
      FROM TB_MASTER
      WHERE TB_MASTER.OBJECT_NAME = OB_NAME AND TB_MASTER.OBJECT_TYPE = 'TABLE'
      AND TB_MASTER.CHANGE_TYPE = 'N' AND TB_MASTER.PROJECTNAME = PROJECT_NAME;
      IF ObCode IS NOT NULL THEN
         CodeStr := ObCode || CHR(10);
      END IF;
      OPEN PRIMARY_DATA;
      LOOP
            FETCH PRIMARY_DATA INTO PRIMARY_ROW;
            EXIT WHEN PRIMARY_DATA%NOTFOUND;
            FIRST_TIME := FIRST_TIME+1;
            IF PRIMARY_ROW.PK_CONSTRAINT = 'Y' THEN
                        P_KEY := P_KEY||','||PRIMARY_ROW.COL_NAME;
            END IF;
      END LOOP;
      CLOSE PRIMARY_DATA;
      OPEN COLUMN_DATA;
      TEMP_VAR := TEMP_VAR||'ALTER TABLE '||OB_NAME||CHR(10);
      TEMP1_VAR := TEMP_VAR||'MODIFY (';
      TEMP_VAR := TEMP_VAR||'ADD (';
      LOOP
            FETCH COLUMN_DATA INTO SINGLE_ROW;
            EXIT WHEN COLUMN_DATA%NOTFOUND;
            LAST_ROW := LAST_ROW+1;
        IF SINGLE_ROW.CHANGE_TYPE = 'D' THEN
                  TEMP2_VAR := TEMP2_VAR||'ALTER TABLE '||OB_NAME||CHR(10)||'DROP COLUMN '||SINGLE_ROW.COL_NAME||CHR(10)||'/'||CHR(10);
            FLAG := 2;
        END IF;
        IF SINGLE_ROW.CHANGE_TYPE = 'N' THEN
                  SINGLE_COL := TABLE_MODIFY_NEW(SINGLE_ROW,NO_OF_ROWS,LAST_ROW);
                  TEMP_VAR := TEMP_VAR||SINGLE_COL;
            END IF;
            IF SINGLE_ROW.CHANGE_TYPE = 'M' THEN
                  SINGLE_COL := TABLE_MODIFY_NEW(SINGLE_ROW,NO_OF_ROWS,LAST_ROW);
                  TEMP1_VAR := TEMP1_VAR||SINGLE_COL;
                  FLAG := 1;
            END IF;
            IF SINGLE_ROW.UPD_COL = 'Y' THEN
                     UPD_VAR := UPD_VAR || 'UPDATE ' || OB_NAME || ' SET '|| SINGLE_ROW.COL_NAME || ' = ' || SINGLE_ROW.DEFAULT_VALUE || ' WHERE ' || SINGLE_ROW.COL_NAME || ' IS NULL' || CHR(10) || '/' || CHR(10);
               END IF;

      END LOOP;
      CLOSE COLUMN_DATA;
      TEMP_VAR := TEMP_VAR||')'||CHR(10)||'/'||CHR(10);
      TEMP1_VAR := TEMP1_VAR||')'||CHR(10)||'/'||CHR(10);


      TEMP_VAR := TEMP_VAR || UPD_VAR;
      TEMP1_VAR := TEMP1_VAR || UPD_VAR;
      
      PromptStr := 'PROMPT ALTERING TABLE ' || OB_NAME||CHR(10);
    pSql := pSql || PromptStr;
      IF FLAG = 1 THEN
            IF ObConSeq = 'B' THEN
                  pSql := pSql || CodeStr;
        END IF;
                  pSql := pSql || TEMP1_VAR;
            IF ObConSeq = 'A' THEN
                  pSql := pSql || CodeStr;
        END IF;
    ELSIF FLAG = 2 THEN
            IF ObConSeq = 'B' THEN
                  pSql := pSql || CodeStr;
        END IF;
                  pSql := pSql || TEMP2_VAR;
            IF ObConSeq = 'A' THEN
                  pSql := pSql || CodeStr;
        END IF;
      ELSE
            IF ObConSeq = 'B' THEN
                  pSql := pSql || CodeStr;
            END IF;
                  pSql := pSql ||TEMP_VAR;
            IF ObConSeq = 'A' THEN
                  pSql := pSql || CodeStr;
        END IF;
      END IF;
      IF FIRST_TIME >0 THEN
            SELECT CONSTRAINT_NAME INTO CONS_NAME
            FROM USER_CONSTRAINTS
            WHERE TABLE_NAME = OB_NAME AND CONSTRAINT_TYPE = 'P';
            OPEN PREVIOUS_PKEY(CONS_NAME);
            LOOP
                  FETCH PREVIOUS_PKEY INTO COLNAME;
                  EXIT WHEN PREVIOUS_PKEY%NOTFOUND;
                  IF PRE_PKEY IS NULL THEN
                        PRE_PKEY := PRE_PKEY||COLNAME;
                  ELSE
                        PRE_PKEY := PRE_PKEY||','||COLNAME;
                  END IF;
            END LOOP;
            CLOSE PREVIOUS_PKEY;
            PRE_PKEY := PRE_PKEY||P_KEY;
            CONS_VAR := CONS_VAR||'ALTER TABLE '||OB_NAME||' DROP CONSTRAINT '||CONS_NAME||CHR(10)||'/'||CHR(10);
            CONS_VAR := CONS_VAR||'ALTER TABLE '||OB_NAME||' ADD CONSTRAINT '||CONS_NAME||' PRIMARY KEY ('||PRE_PKEY|| ')'||CHR(10)||'/'||CHR(10);
            pSql := CONS_VAR;
      ELSE
            NULL;
      END IF;
EXCEPTION
      WHEN OTHERS THEN
            IF PRIMARY_DATA%ISOPEN THEN
                  CLOSE PRIMARY_DATA;
            END IF;
            IF COLUMN_DATA%ISOPEN THEN
                  CLOSE COLUMN_DATA;
            END IF;
            pSql := 'Error Occurred for table OB_NAME ' || SQLERRM;
            --utl_file.put_line(file,pSql);
            dbms_output.put_line(psql);
END MODIFY_TABLE;

And the code I am using to call this procedure
' conn Connection string. not a connection object
            Dim Connect As New OleDbConnection()
            Dim Comm As New OleDbCommand()
            Dim DAdapter As New OleDbDataAdapter()
            Dim RowCounter As Integer = 0
            Dim param1, Param2, Param3, Param4, Param5, Param6 As OleDbParameter
           
 Comm.Connection = Connect
                                Comm.CommandType = CommandType.StoredProcedure
                                Comm.CommandText = "DDLGEN.NEW_TABLE"
                                param1 = New OleDbParameter()
                                Comm.Parameters.Add(param1)
                                param1.DbType = DbType.String
                                param1.ParameterName = "l_refno"
                                param1.Direction = ParameterDirection.Input
                                param1.Size = 16
                                param1.Value = l_refno
                                Param2 = New OleDbParameter()
                                Comm.Parameters.Add(Param2)
                                Param2.DbType = DbType.String
                                Param2.ParameterName = "p_projname"
                                Param2.Direction = ParameterDirection.Input
                                Param2.Size = 25
                                Param2.Value = p_projname
                                Param3 = New OleDbParameter()
                                Comm.Parameters.Add(Param3)
                                Param3.DbType = DbType.String
                                Param3.ParameterName = "l_objname"
                                Param3.Direction = ParameterDirection.Input
                                Param3.Size = 40
                                Param3.Value = l_objname
                                Param4 = New OleDbParameter()
                                Comm.Parameters.Add(Param4)
                                Param4.DbType = DbType.Object
                                Param4.ParameterName = "l_code"
                                Param4.Direction = ParameterDirection.Input
                                Param4.Size = 32767
                                If l_code = "" Then
                                    Param4.Value = System.DBNull.Value
                                Else
                                    Param4.Value = l_code
                                End If

                                Param5 = New OleDbParameter()
                                Comm.Parameters.Add(Param5)
                                Param5.DbType = DbType.String
                                Param5.ParameterName = "l_conseq"
                                Param5.Direction = ParameterDirection.Input
                                Param5.Size = 1
                                If l_conseq <> "" Then
                                    Param5.Value = l_conseq
                                Else
                                    Param5.Value = System.DBNull.Value
                                End If
                                Param6 = New OleDbParameter()
                                Comm.Parameters.Add(Param6)
                                Param6.DbType = DbType.Object
                                Param6.ParameterName = "l_retval"
                                Param6.Direction = ParameterDirection.Output
                                Param6.Size = 32676
                                Comm.Prepare()
                                l_result = Convert.ToString(Comm.ExecuteScalar())
                               
It seems to be there is problem in VB.NET code. Thanks in Advance

Sandeep Shivathaya S
0
Comment
Question by:sandeepshivathaya
8 Comments
 
LVL 8

Expert Comment

by:annamalai77
ID: 11829141
my dear friend

the problem is with the way u have initialized the variable

ObCode IN LONG := null

u cannot do that way.
initialize the value after or before declaring the cursor that will solve ur problem.

regards
annamalai

0
 
LVL 8

Expert Comment

by:annamalai77
ID: 11829151
hi

PROCEDURE MODIFY_TABLE(REFER_NO IN VARCHAR2,
                 PROJECT_NAME IN     VARCHAR2,
               OB_NAME IN VARCHAR2,
              ObCode IN LONG,
               ObConSeq IN VARCHAR2,
               pSql IN OUT     VARCHAR2
)
IS

i think the above should solve ur problem. just remove the := null.

regards
annamalai

0
 

Author Comment

by:sandeepshivathaya
ID: 11829184
Hi Mr. Anamali,

Thanks.
I changed the code according to your suggestion but problem still persists. This stored procedure works fine when I call it from VB. Or when I call from PLSQL Developer and SQL Plus and gives the desired results. But when called from .NET I am getting the problem. I saw somewhere in the internet that this problem does not occur when called from Oracle Native driver. But occures when called from ODBC, OLE DB, JDBC and ADO.NET. Can you suggest something?

Regards,
Sandeep
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:Ajay_Gupta
ID: 11829643
Hi Sandeep

VB .Net uses reference by mode to execute procedures so try using ref cursors in your procedure and it should work fine. You will have to return the ref cursor to pass back the values.

Here is an example from microsoft site.

CREATE OR REPLACE PACKAGE curspkg_join AS
         TYPE t_cursor IS REF CURSOR ;
         Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);
   END curspkg_join;
   /
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
   Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
   IS
         v_cursor t_cursor;
   BEGIN
         IF n_EMPNO <> 0
         THEN
               OPEN v_cursor FOR
               SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
                     FROM EMP, DEPT
                     WHERE EMP.DEPTNO = DEPT.DEPTNO
                     AND EMP.EMPNO = n_EMPNO;

         ELSE
               OPEN v_cursor FOR
               SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
                     FROM EMP, DEPT
                     WHERE EMP.DEPTNO = DEPT.DEPTNO;

         END IF;
         io_cursor := v_cursor;
   END open_join_cursor1;
   END curspkg_join;
   /


Thanx
Ajay
0
 

Author Comment

by:sandeepshivathaya
ID: 11838616
Hi,

I tried to resolve this problem. One of my friend suggested that there should be a problem with the Driver. I changed the OleDb driver into OracleClient and used OracleDBType to specify the type of parameter instead of just DbType and found that storage procedure is executed successfully.
According to Anamali's suggestion I removed the :=null as default value for the parameter. When I used default value I got an error saying that parameter mismatch when I removed it worked fine. Thanks to Anamali.

Here is the modified code using which I am executing stored procedure.

Dim Connect As New OracleConnection()
            Dim Comm As New OracleCommand()
            Dim DAdapter As New OracleDataAdapter()
            Dim RowCounter As Integer = 0
            Dim param1, Param2, Param3, Param4, Param5, Param6 As OracleParameter
            Dim i As Object
            Dim Dr As OracleDataReader
            Dim Rs As New DataSet()
            Dim Rs1 As New DataSet()
            Dim RsIndex, RsIndex1 As Integer
            Dim l_code, l_type, l_objname, l_result, l_refno, l_conseq As String

Comm.Connection = Connect
                                Comm.CommandType = CommandType.StoredProcedure
                                Comm.CommandText = "DDLGEN.MODIFY_TABLE"
                                param1 = New OracleParameter()
                                Comm.Parameters.Clear()
                                Comm.Parameters.Add(param1)
                                param1.OracleDbType = OracleDbType.VarChar
                                param1.ParameterName = "REFER_NO"
                                param1.Direction = ParameterDirection.Input
                                param1.Size = 16
                                param1.Value = l_refno
                                Param2 = New OracleParameter()
                                Param2.OracleDbType = OracleDbType.VarChar
                                Param2.ParameterName = "PROJECT_NAME"
                                Param2.Direction = ParameterDirection.Input
                                Param2.Size = 25
                                Param2.Value = p_projname
                                Comm.Parameters.Add(Param2)
                                Param3 = New OracleParameter()
                                Param3.DbType = DbType.String
                                Param3.OracleDbType = OracleDbType.VarChar
                                Param3.ParameterName = "OB_NAME"
                                Param3.Direction = ParameterDirection.Input
                                Param3.Size = 40
                                Param3.Value = l_objname
                                Comm.Parameters.Add(Param3)
                                Param4 = New OracleParameter()
                                Param4.OracleDbType = OracleDbType.Long
                                Param4.ParameterName = "ObCode"
                                Param4.Direction = ParameterDirection.Input
                                Param4.Size = 32767
                                If l_code = "" Then
                                    Param4.Value = ""
                                Else
                                    Param4.Value = l_code
                                End If
                                Comm.Parameters.Add(Param4)
                                Param5 = New OracleParameter()
                                Param5.OracleDbType = OracleDbType.VarChar
                                Param5.ParameterName = "ObConSeq"
                                Param5.Direction = ParameterDirection.Input
                                Param5.Size = 1
                                If l_conseq <> "" Then
                                    Param5.Value = l_conseq
                                Else
                                    Param5.Value = ""
                                    'Param5.Value = System.DBNull.Value
                                End If
                                Comm.Parameters.Add(Param5)
                                Param6 = New OracleParameter()
                                Param6.OracleDbType = OracleDbType.VarChar
                                'Param6.OdbcType = OdbcType.VarChar
                                Param6.ParameterName = "pSql"
                                Param6.Direction = ParameterDirection.ReturnValue
                                Param6.Size = 32676
                                Comm.Parameters.Add(Param6)
                                Comm.Prepare()
                                l_result = Convert.ToString(Comm.ExecuteScalar())

I thank Mr. Ajay also for his Kind suggestion.
I suggest those who are using Oracle with .NET to use OracleClient driver.

Regards,
Sandeep Shivathaya
           
0
 

Author Comment

by:sandeepshivathaya
ID: 11877930
Please close this question
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 11920609
Closed, 500 points refunded.
CetusMOD
Community Support Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle query 15 63
report returning null 21 52
Difference in number of minutes between 2 timestamps 16 26
history tablespace temp usage 2 17
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now