Home » Developer & Programmer » Forms » Dynamic Table Problem (not yet solved ) (please Urgent)
Dynamic Table Problem (not yet solved ) (please Urgent) [message #78560] Sun, 03 March 2002 05:07 Go to next message
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 Go to previous message
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
Previous Topic: Forms Open API and ActiveX
Next Topic: How to change user password thru forms4.5
Goto Forum:
  


Current Time: Fri Aug 14 09:32:40 CDT 2020