Solved

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

Posted on 2004-08-22
1
235 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

RIA (Rich Internet Application) tools are interactive internet applications which have many of the characteristics of desktop applications. The RIA tools typically deliver output either by the way of a site-specific browser or via browser plug-in. T…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
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 …

743 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

16 Experts available now in Live!

Get 1:1 Help Now