oracle & .net

[amer shammout]

hello i used to work with c# and sql server but now i am working with oracle now I want to return data table from procedure i used to create sql proceduer "select * from tablename" and then i use datatable with dataadapter to return the rows of table but it does not work with oracle what can i do???????????????!!!!!!!!!!!!!

[i j russell]

What do you mean by 'it does not work'; do you get an error or no data or what?

[amer shammout]

the procedure did not created (syntax error) sum frien told me to use function but it did not work

[Don Burton]

Since I'm not quite sure how you're approaching the problem - try reviewing this Oracle article and see if it helps: http://www.oracle.com/technology/pub/articles/cook-vs08.html[^]

[amer shammout]

thanks very much

[slam Iqbal]

You have to use Package insted of procedure.
Search in oracle or MSDN to get help about package. here is an example of package (Collected from MSDN):

Drop table Emp;
drop table DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);

INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');

CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
HIREDATE DATE NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);

INSERT INTO Emp VALUES(123,'Bob','Sales',555,'28-JAN-79',35000,12,33);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,'12-MAY-83',42000,12,11);
INSERT INTO Emp VALUES(234,'Mary','Account',555,'14-AUG-82',33000,12,22);

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;

Convert the following VB Code into C# to execute that package:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim x As Exception
Dim Ds As New DataSet()
Dim Oraclecon As New OracleConnection("Server=giit;Uid=scott;Pwd=tiger")

    Oraclecon.Open()


    Dim myCMD As New OracleCommand()
    myCMD.Connection = Oraclecon
    myCMD.CommandText = "curspkg\_join.open\_join\_cursor1"
    myCMD.Co

[amer shammout]

thanks that is exactly what i want