ReturnValue in a Procedure...


... i'm working with Oracle for the 1st time (i'm used to SQL Server)! I've researched but found no answer: is there anyway to retrieve a value from a Oracle Procedure I know this is possible (and easy) to execute in SQL. I'm devolping a Windows App and the returnValue is kind of important to manage bugs and possible errors!
Posted On: Wednesday 21st of November 2012 01:37:42 AM Total Views:  316
View Complete with Replies

RELATED TOPICS OF Oracle PROGRAMMING LANGUAGE




Problem in stored procedure...

Hi all, I am creating the below Stored Procedure in that i m passing table name as a argument i have decalred @TableName also,but when i run it is giving me error message 'Must declare table variable @TableName. whre i m going wrong can u please tell me..or is there any other way. CREATE Procedure AddNewUser1 ( @Name nvarchar(25), @LoginID nvarchar(25), @Password nvarchar(16), @DOB nvarchar (8), @Gender nvarchar (8), @Country nvarchar(50), \t\t\t @TableName nvarchar(25), \t\t\t @UserID smallint OUTPUT ) AS Select * From Users Where LoginID=@LoginID If @@rowcount=0 Begin \t Insert Into @tblname (LoginID,Password,Name,DOB,Gender,Country) \tvalues(@LoginID,@Password,@Name,@DOB,@Gender,@Country) select @UserID=1 End else select @UserID=-1 GO
VIEWS ON THIS POST

218

Posted on:

Friday 2nd November 2012
View Replies!

Orcle issues for procedure.....

Dear, i have oracle procedure issue problem any idea help me ...as following step.....Step:-1 I have a Oracle procedure in that,i have pass two parameter.eg. 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 scott.EMP.EMPNO, scott.EMP.ENAME,scott.DEPT.DEPTNO,scott.DEPT.DNAME FROM scott.EMP, scott.DEPT WHERE scott.EMP.DEPTNO = scott.DEPT.DEPTNO AND scott.EMP.EMPNO = n_EMPNO; ELSE OPEN v_cursor FOR SELECT scott.EMP.EMPNO,scott.EMP.ENAME, scott.DEPT.DEPTNO,scott. DEPT.DNAME FROM scott.EMP,scott. DEPT WHERE scott.EMP.DEPTNO = scott.DEPT.DEPTNO; END IF; io_CURSOR := v_cursor; END open_join_cursor1; END curspkg_join;/Step:-2 On my VB.net application form code, i have write bellow.eg:- Imports System.Data Imports System.Data.OracleClient Public Class Form1 Dim builder As New OracleConnectionStringBuilder("Password=sysdba;User ID=system;Data Source=ORCL;Persist Security Info=True") Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataDispaly.Click Dim x As Exception Dim Ds As New DataSet() Dim con As New OracleConnection(builder.ConnectionString) con.Open() Dim myCMD As New OracleCommand myCMD.Connection = con myCMD.CommandText = "curspkg_join.open_join_cursor1" myCMD.CommandType = CommandType.StoredProcedure myCMD.Parameters.Add(New OracleParameter("n_EMPNO", OracleType.Numeric)).Value = 7839 myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output Dim MyDA As New OracleDataAdapter(myCMD) Try MyDA.Fill(Ds) Catch x MessageBox.Show(x.Message.ToString) End Try DataGridView1.DataSource = Ds.Tables(0) con.Close() End Sub [If excute well no problem]Step:-3 if in other oracle procedure, I have pass the cursor name of differnet name. it means that i have send cursor name from procedure.eg:- CREATE OR REPLACE PACKAGE BODY curspkg_join AS Procedure open_join_cursor1 (n_EMPNO IN NUMBER, REFCURSOR IN OUT t_cursor) Is v_cursor t_cursor; BEGIN IF n_EMPNO 0 THEN OPEN v_cursor FOR SELECT scott.EMP.EMPNO, scott.EMP.ENAME,scott.DEPT.DEPTNO,scott.DEPT.DNAME FROM scott.EMP, scott.DEPT WHERE scott.EMP.DEPTNO = scott.DEPT.DEPTNO AND scott.EMP.EMPNO = n_EMPNO; ELSE OPEN v_cursor FOR SELECT scott.EMP.EMPNO,scott.EMP.ENAME, scott.DEPT.DEPTNO,scott. DEPT.DNAME FROM scott.EMP,scott. DEPT WHERE scott.EMP.DEPTNO = scott.DEPT.DEPTNO; END IF; REFCURSOR := v_cursor; END open_join_cursor1; END curspkg_join;/And my application can't chage cursor name.example as bellow,eg:- Imports System.Data Imports System.Data.OracleClient Public Class Form1 Dim builder As New OracleConnectionStringBuilder("Password=sysdba;User ID=system;Data Source=ORCL;Persist Security Info=True") Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DataDispaly.Click Dim x As Exception Dim Ds As New DataSet() Dim con As New OracleConnection(builder.ConnectionString) con.Open() Dim myCMD As New OracleCommand myCMD.Connection = con myCMD.CommandText = "curspkg_join.open_join_cursor1" myCMD.CommandType = CommandType.StoredProcedure myCMD.Parameters.Add(New OracleParameter("n_EMPNO", OracleType.Numeric)).Value = 7839 myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output Dim MyDA As New OracleDataAdapter(myCMD) Try MyDA.Fill(Ds) Catch x MessageBox.Show(x.Message.ToString) End Try DataGridView1.DataSource = Ds.Tables(0) con.Close() End Sub .net application not change cursor name.that time i have got error message as "wrong number or type of arguments in call to 'OPEN_JOIN_Cuursor1' ora-06550;line 1,column 7:".... Solution will be appricated, plz help me.....i am really stuch in that issue,,,thanks and regardsamar patil.Note:- if procedure aggument cursor name dyanamically change in oracle application .
VIEWS ON THIS POST

138

Posted on:

Tuesday 20th November 2012
View Replies!