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.