Solved

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

Posted on 2004-08-22
1
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Regular Expression Calculator Tester 2 96
Recover unsaved Tab contents (and settings/preferences) from Notepad++ 3 143
Adding a countdown to HTA 12 111
learn programming 8 92
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
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 …

737 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