Solved

Stored Procedure is Executed But Returns Nothing when executed from .NET enviornment

Posted on 2004-08-22
1
236 Views
Last Modified: 2011-09-20
I am executing a Oracle stored procedure DDLGEN.NEWTABLE
Code is given below
PROCEDURE NEW_TABLE (REFER_NO IN VARCHAR2,
                      PROJECT_NAME IN      VARCHAR2,
                    OB_NAME IN VARCHAR2,
                    OBCode  IN LONG,
                    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 ROWID;
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;
TEMP_VAR             LONG;
P_KEY             VARCHAR2(2000);
FIRST_TIME             NUMBER :=0;
NO_OF_ROWS             NUMBER;
LAST_ROW             NUMBER :=0;
PromptStr            VARCHAR2(100);
--file            utl_file.file_type;
BEGIN
--      file := utl_file.fopen('/tmp/' , 'err','w');
      IF ObConSeq = 'B' THEN
         IF OBCode IS NOT NULL THEN
            Temp_Var := TEMP_VAR || OBCode || CHR(10);
         END IF;
    END IF;
      SELECT COUNT(*) INTO NO_OF_ROWS
      FROM TB_DETAILS_TABLE
      WHERE REF_NO = REFER_NO AND PROJECTNAME = PROJECT_NAME;
      OPEN PRIMARY_DATA;
      P_KEY := P_KEY||'PRIMARY KEY(';
      LOOP
            FETCH PRIMARY_DATA INTO PRIMARY_ROW;
            EXIT WHEN PRIMARY_DATA%NOTFOUND;
            FIRST_TIME := FIRST_TIME+1;
            IF PRIMARY_ROW.PK_CONSTRAINT = 'Y' THEN
                  IF FIRST_TIME = 1 THEN
                        P_KEY := P_KEY||PRIMARY_ROW.COL_NAME;
                  ELSE
                        P_KEY := P_KEY||','||PRIMARY_ROW.COL_NAME;
                  END IF;
            END IF;
      END LOOP;
      P_KEY := P_KEY||')';
      CLOSE PRIMARY_DATA;
      OPEN COLUMN_DATA;
      TEMP_VAR := TEMP_VAR||'CREATE TABLE '||OB_NAME||'('||CHR(10);
      LOOP
            FETCH COLUMN_DATA INTO SINGLE_ROW;
            EXIT WHEN COLUMN_DATA%NOTFOUND;
            TEMP_VAR := TEMP_VAR||SINGLE_ROW.COL_NAME||' '||SINGLE_ROW.COL_TYPE;
            LAST_ROW := LAST_ROW+1;
            IF SINGLE_ROW.LENGTH IS NULL THEN
                  NULL;
            ELSE
                  IF SINGLE_ROW.SCALE IS NULL THEN
                        TEMP_VAR := TEMP_VAR||'('||SINGLE_ROW.LENGTH||')';
                  ELSE
                        TEMP_VAR :=TEMP_VAR||'('||SINGLE_ROW.LENGTH||','||NVL(SINGLE_ROW.SCALE,0)||')';
                  END IF;
            END IF;
            IF SINGLE_ROW.DF_CONSTRAINT = 'Y' THEN
                  IF SINGLE_ROW.COL_TYPE = 'DATE' THEN
                     IF UPPER(SINGLE_ROW.DEFAULT_VALUE)='SYSDATE' THEN
                              TEMP_VAR := TEMP_VAR||' DEFAULT '||SINGLE_ROW.DEFAULT_VALUE;
                     ELSE
                              TEMP_VAR := TEMP_VAR||' DEFAULT '||'TO_DATE('||CHR(39)||SINGLE_ROW.DEFAULT_VALUE||CHR(39)||')';
                     END IF;
                  ELSIF      SINGLE_ROW.COL_TYPE = 'NUMBER' THEN
                        IF NVL(SINGLE_ROW.DEFAULT_VALUE,'0') <> NULL THEN
                              TEMP_VAR := TEMP_VAR||' DEFAULT '||TO_NUMBER(SINGLE_ROW.DEFAULT_VALUE);
                        ELSE
                              TEMP_VAR := TEMP_VAR||' DEFAULT 0 ';
                        END IF;
                  ELSE
                     TEMP_VAR := TEMP_VAR||' DEFAULT '||SINGLE_ROW.DEFAULT_VALUE;
                  END IF;
            END IF;
            IF SINGLE_ROW.CHK_CONSTRAINT = 'Y' THEN
                  TEMP_VAR := TEMP_VAR||' CHECK '||SINGLE_ROW.CHK_VALUE;
            END IF;
            IF SINGLE_ROW.FK_CONSTRAINT = 'Y' THEN
                  TEMP_VAR := TEMP_VAR||' REFERENCES '||SINGLE_ROW.FK_TABLE||'('||SINGLE_ROW.FK_COLUMN||')';
            END IF;
            IF SINGLE_ROW.NN_CONSTRAINT = 'Y' THEN
                  TEMP_VAR := TEMP_VAR||' NOT NULL ';
            END IF;
            IF SINGLE_ROW.UK_CONSTRAINT = 'Y' THEN
                  TEMP_VAR :=TEMP_VAR||' UNIQUE ';
            END IF;
            IF LAST_ROW = NO_OF_ROWS AND FIRST_TIME = 0 THEN
                  NULL;
            ELSE
                  TEMP_VAR := TEMP_VAR||','||CHR(10);
            END IF;
      END LOOP;
      CLOSE COLUMN_DATA;
      IF FIRST_TIME >0 THEN
            TEMP_VAR := TEMP_VAR||P_KEY||')'|| CHR(10) || '/' || CHR(10);
      ELSE
            TEMP_VAR := TEMP_VAR||')'||CHR(10) || '/' || CHR(10);
      END IF;
      IF ObConSeq = 'A' THEN
         IF OBCode IS NOT NULL THEN
            Temp_Var := TEMP_VAR || OBCode || CHR(10);
         END IF;
    END IF;
            PromptStr := 'PROMPT CREATING TABLE ' || OB_NAME || CHR(10);
        TEMP_VAR := PromptStr || TEMP_VAR;
            pSql := temp_var;
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 NEW_TABLE;
This stored procedure when executed with PLSQL Developer or SQL Plus gives the results properly.
But when I  execute the same stored procedure for the same parameters which I use to execute this stored procedure in PLSQL Developer or SQL Plus. It gives a results empty string.
Following  is the code I am using to execute it from .NET enviornment.
  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, l_path As String
            Dim DsSP As New DataSet()
                      Comm.Connection = Connect
                                Comm.CommandType = CommandType.StoredProcedure
                                Comm.CommandText = "DDLGEN.NEW_TABLE"
                                param1 = New OracleParameter()
                                Comm.Parameters.Add(param1)
                                param1.OracleDbType = OracleDbType.VarChar
                                param1.ParameterName = "REF_NO"
                                param1.Direction = ParameterDirection.Input
                                param1.Size = 16
                                param1.Value = l_refno
                                Param2 = New OracleParameter()
                                Comm.Parameters.Add(Param2)
                                Param2.OracleDbType = OracleDbType.VarChar
                                Param2.ParameterName = "PROJECT_NAME"
                                Param2.Direction = ParameterDirection.Input
                                Param2.Size = 25
                                Param2.Value = p_projname
                                Param3 = New OracleParameter()
                                Comm.Parameters.Add(Param3)
                                Param3.OracleDbType = OracleDbType.VarChar
                                Param3.ParameterName = "OB_NAME"
                                Param3.Direction = ParameterDirection.Input
                                Param3.Size = 40
                                Param3.Value = l_objname
                                Param4 = New OracleParameter()
                                Comm.Parameters.Add(Param4)
                                Param4.OracleDbType = OracleDbType.VarChar
                                'Param4.OracleDbType = OracleDbType.Long
                                Param4.ParameterName = "OBCode"
                                Param4.Direction = ParameterDirection.Input
                                Param4.Size = 32767
                                If l_code = "" Then
                                    'Param4.Value = System.DBNull.Value
                                    Param4.Value = ""
                                Else
                                    Param4.Value = l_code
                                End If

                                Param5 = New OracleParameter()
                                Comm.Parameters.Add(Param5)
                                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 = System.DBNull.Value
                                End If
                                Param6 = New OracleParameter()
                                Comm.Parameters.Add(Param6)
                                'Param6.DbType = DbType.Object
                                Param6.OracleDbType = OracleDbType.VarChar
                                Param6.ParameterName = "pSql"
                                Param6.Direction = ParameterDirection.ReturnValue
                                Param6.Size = 32676
                                Comm.Prepare()
                                'Comm.ExecuteNonQuery()
                                Dr = Comm.ExecuteReader
                                l_result = Convert.ToString(Comm.ExecuteScalar())
                                If Dr.Read() Then
                                    l_result = Dr.Item("l_retval")
                                End If
                                Dr.Close()
Using this code I am getting empty string as the result.          
How can I resolve this problem. Any suggestions?
Thanks in Advance

Regards,
Sandeep

0
Comment
Question by:sandeepshivathaya
1 Comment
 
LVL 4

Accepted Solution

by:
chipple earned 250 total points
ID: 11867877
If I understand right, you want to retrieve the value that the procedure sets to the pSql parameter, right?
Then try the following.

Remove:
  Dr = Comm.ExecuteReader
  l_result = Convert.ToString(Comm.ExecuteScalar())
  If Dr.Read() Then
    l_result = Dr.Item("l_retval")
  End If
  Dr.Close()

Replace by:
  Comm.ExecuteNonQuery()
  l_result = Comm.Parameters("pSql").Value

Good luck!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mapBully challenge 6 113
Path of Workbook 3 59
Help to convert powershell script into a gui 9 135
c++ reading data from file into two dimensional array 3 93
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

919 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

19 Experts available now in Live!

Get 1:1 Help Now