| Dynamic Table Problem (not yet solved ) (please Urgent) [message #78560] |
Sun, 03 March 2002 05:07  |
Imran
Messages: 56
Registered: November 2001
|
Member |
|
|
The dynamic table problem is still not yet solved through dynamci sql.
summary is I want to use this statement in forms.
select count(*) into a
from :display_item;
i want to provide table name from display item but it generates errors at compilation time. Please help urgently
|
|
|
|
| Re: Dynamic Table Problem (not yet solved ) (please Urgent) [message #78566 is a reply to message #78560] |
Sun, 03 March 2002 22:17  |
Remash
Messages: 52
Registered: November 2000
|
Member |
|
|
Hi,
Create a function say GETCOUNT in your database as shown below. In your form, add a statement like this
A := GETCOUNT(:display_item); If the returned value is -1 then there is an error, may be the table does not exist.
CREATE OR REPLACE FUNCTION GETCOUNT(
p_Table in Varchar2) Return Number IS
v_CursorID INTEGER;
v_BlockStr VARCHAR2(500);
v_Count NUMBER;
v_Dummy INTEGER;
BEGIN
v_CursorID := DBMS_SQL.OPEN_CURSOR;
v_BlockStr :=
'Begin
SELECT COUNT(1) INTO :Count from '||p_Table||';
End;';
DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_CursorID,':Count',v_Count);
v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
DBMS_SQL.VARIABLE_VALUE(v_CursorID,':Count',v_Count);
v_Count := NVL(v_Count,0);
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
Return v_Count;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
Return -1;
RAISE;
END;
/
Regards
Remash
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
