| Is it possible to get the table name from the user. [message #678724] |
Sun, 05 January 2020 02:48  |
 |
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
|
Senior Member |
|
|
I am writing a Procedure. In the procedure the table name should be given from the user.
Is it possible to assign the table name this way?
EX.
PROCEDURE GE IS
COUNTER NUMBER ;
TABLE_N VARCHAR2(15);
BEGIN
TABLE_N = :A.ITEM_NAME;
SELECT COUNT(*) INTO COUNTER
FROM
TABLE_N ;
Can I make the user assign the table name from his end ?
|
|
|
|
|
|
|
|
| Re: Is it possible to get the table name from the user. [message #678730 is a reply to message #678725] |
Sun, 05 January 2020 12:36  |
|
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
|
Senior Member |
|
|
EXECUTE IMMEDIATE doesn't work in forms 6i.
I have tried to use forms_ddl as the followings:
forms_ddl('SELECT
COUNT(*) INTO '||COUNTER||' from '||myTable||';') ;
I have to note that I need to use the returned value to use it for further actions. also, forms_ddl does not return any values. Is there any other way to do it?
[Updated on: Sun, 05 January 2020 12:41] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Is it possible to get the table name from the user. [message #678742 is a reply to message #678740] |
Mon, 06 January 2020 04:52 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As already stated - no you can't use execute immediate in forms.
You can use it in a procedure in the database and call that from the form.
as simple as this:
CREATE OR REPLACE FUNCTION table_count (p_table_name IN VARCHAR2)
RETURN NUMBER AS
l_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select count(*) from '||p_table_name INTO l_count;
RETURN l_count;
END table_count;
|
|
|
|
|
|
| Re: Is it possible to get the table name from the user. [message #678784 is a reply to message #678750] |
Sat, 11 January 2020 13:16 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Yes, that's the simplest function that does the job:
SQL> create or replace function table_count (p_table_name in varchar2)
2 return number
3 as
4 l_count number;
5 begin
6 execute immediate 'select count(*) from '||p_table_name into l_count;
7
8 return l_count;
9 end table_count;
10 /
Function created.
SQL> select table_count('emp') result from dual;
RESULT
----------
14
SQL>
SQL> select table_count('emp e join dept d on e.deptno = d.deptno where d.deptno = 10') result
2 from dual;
RESULT
----------
3
SQL>
SQL> create or replace function table_count (p_table_name in varchar2)
2 return number
3 as
4 l_count number;
5 begin
6 execute immediate 'select count(*) from '||
7 dbms_assert.sql_object_name(p_table_name) into l_count;
8
9 return l_count;
10 end table_count;
11 /
Function created.
SQL> select table_count('emp e join dept d on e.deptno = d.deptno where d.deptno = 10') result
2 from dual;
select table_count('emp e join dept d on e.deptno = d.deptno where d.deptno = 10') result
*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SCOTT.TABLE_COUNT", line 6
SQL> select table_count('emp e') result from dual;
select table_count('emp e') result from dual
*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SCOTT.TABLE_COUNT", line 6
SQL> select table_count('emp') result from dual;
RESULT
----------
14
SQL> |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
