Home » Developer & Programmer » Forms » Is it possible to get the table name from the user. (6i)
Is it possible to get the table name from the user. [message #678724] Sun, 05 January 2020 02:48 Go to next message
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 #678725 is a reply to message #678724] Sun, 05 January 2020 04:59 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
During compilation, pl/sql checks all the objects referenced by the code. So if, at compile time, you do not know the objects your code will be addressing you would need to construct the statement dynamically and run it with EXECUTE IMMEDIATE.
Re: Is it possible to get the table name from the user. [message #678729 is a reply to message #678725] Sun, 05 January 2020 12:34 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; however, as it is about Forms, run it with FORMS_DDL
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 messageGo to next message
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 #678732 is a reply to message #678730] Sun, 05 January 2020 12:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Workaround that usually helps is to move the procedure into the database (i.e. so that it becomes a stored procedure). It would have one IN parameter - table name (which is contained in the form item) and one OUT parameter (which would return the value into your COUNTER variable).
Re: Is it possible to get the table name from the user. [message #678733 is a reply to message #678732] Sun, 05 January 2020 23:34 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Then, I will create a dump table to insert values in, then retrieve the date using a Select statement into counter.
Thank You.
Re: Is it possible to get the table name from the user. [message #678739 is a reply to message #678733] Mon, 06 January 2020 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why bother with an extra table?
Just use execute immediate and return the value as either an out parameter or return from a function.
Re: Is it possible to get the table name from the user. [message #678740 is a reply to message #678739] Mon, 06 January 2020 04:04 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Is it possible to use EXECUTE IMMEDIATE in forms 6i?
Also, I can't retrieve data from .
forms_ddl();
How can I set an out parameter from function if I want the referring table to be passed from the user.
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 messageGo to next message
cookiemonster
Messages: 13917
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 #678750 is a reply to message #678742] Mon, 06 January 2020 23:46 Go to previous messageGo to next message
amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia
Senior Member
Thank you, cookiemonster. That is exactly what I will do.
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
Littlefoot
Messages: 21806
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?
Previous Topic: cannot understand what is the problem
Next Topic: Sum of Amount with Items of Two different Groups
Goto Forum:
  


Current Time: Thu Mar 28 07:19:53 CDT 2024