Is it possible to get the table name from the user. [message #678724] |
Sun, 05 January 2020 02:48 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9996a32bf69a26d576c355127fae6a6d?s=64&d=mm&r=g) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/9996a32bf69a26d576c355127fae6a6d?s=64&d=mm&r=g) |
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||';') ;
COUNTER is always null.
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 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>
But, it won't prevent SQL injection:
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>
I guess you (or your users) won't be that malicious, especially not as you'll invoke the function from the form. However, note that there is a way to prevent it - use DBMS_ASSERT. Here's an example:
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>
Looks better, right?
|
|
|