how to pass table name as parameter...

[chinnaraj]

hi all,
can anyone give me some suggestion s HOW TO PASS TABLE NAME AS A PARAMETER to procedure...
i vil show some example .....

Create a procedure that takes table name and column names as parameter and displays data.

this is my question...
i tried something but am getting error...so anyone help me...my code is...

create or replace
PROCEDURE TO_CALL_TABLE
( TABLENAME IN varchar,E_NAME OUT EMP.ENAME%TYPE,E_NO IN EMP.EMPNO%TYPE) is
BEGIN
SELECT ENAME INTO E_NAME FROM tablename
where empno=e_no;
END;

[ComITSolutions]

study the syntax of Sp_executeSql System Stored Procedure If you are using sql server 2000 and Above

[chinnaraj]

And My Database Is Oracle.....

[davide++]

Hi chinnaraj

This ins't a easy work; you have to use SQL dynamic.
Take a look at this.

PROCEDURE TO_CALL_TABLE(pTableName IN VARCHAR2,     -- your TABLENAME
                        pTableField IN VARCHAR2,    -- you need the name of field, aren't you?
                        pE_NO IN EMP.EMPNO%TYPE,    -- your E_NO, the filter
                        pEName OUT EMP.ENAME%TYPE)   -- your E_NAME, the result 
IS
      vSelectString  VARCHAR2(4000) := NULL;
      vCursorId      INTEGER := 0;
      vDummy         INTEGER := 0;
      vResult        EMP.ENAME%TYPE;

BEGIN

    -- 1. Create the query
    vSelectString := 'SELECT ' || pTableField
                     ' FROM ' || pTableName ||
                     ' WHERE EMPNO = ' || TO_CHAR(pE_NO);
                     
    -- 2. Create the cursor
    vCursorId := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(vCursorId, vSelectString, DBMS_SQL.Native);
    
    -- 3. Set the column
    DBMS_SQL.DEFINE_COLUMN(vCursorId, 1, vResult);
    
    -- 4. Execute the query
    vDummy := DBMS_SQL.EXECUTE(vCursorId);
  
    -- 5. Fetch
    LOOP
        IF DBMS_SQL.FETCH_ROWS(vCursorId) = 0 THEN
            EXIT;
        END IF;
        DBMS_SQL.COLUMN_VALUE(vCursorId, 1, vResult);
    END LOOP;

    -- 6. Close the cursor
    DBMS_SQL.CLOSE_CURSOR(vCursorId);

    pEName := vResult;

--SELECT ENAME INTO E_NAME FROM tablename
--WHERE empno=e_no;
END;

I haven't tried, I hope this will work.
Are you doing an assignment?

[chinnaraj]

yeah am doing assignment

[davide++]

yeah am doing assignment

Well.

You should study the theory first, and then you should try to write the code yourself : if you only see the code written by others you won't learn a lot...
Moreover Oracle is an advanced topic, so I think you're attending some computer science course: in this case is better for you if you will be able to do what the teacher teaches, more than passing the exams only.