This content has been marked as final. Show 14 replies
Without dynamic SQL, it cannot be done.
And saying that a procedure can use dynamic sql whereas a function cannot is utterly wrong.
1 create or replace function x(t in varchar2) return sys_refcursor as 2 a sys_refcursor; 3 begin 4 open a for 'select * from ' || t; 5 return a; 6* end; SQL> / Function created. SQL> var yy refcursor SQL> exec :yy := x('EMPLOYEES'); PL/SQL procedure successfully completed.
When i executed this, i got the error:
SQL> select table_name,
2 TO_NUMBER (
3 EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) X from ' || table_name)), '/ROWSET/ROW/X')
6 from dba_tables
7 where OWNER = 'SCOTT' and table_name = 'EMP';
EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) X from ' || table_name)), '/ROWSET/ROW/X')
ERROR at line 3:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
Dynamic SQL is a result of BAD Database Design. So do you have your table details stored in custom tables? A thoughtful Architect would have thought it would make an application more flexible. But what they end up with is a application that perform poorly and which becomes a maintenance night mare.
Basics of oracle is that the objects used in a SQL statement must be known to oracle when it parses the code. The second step of parsing semantic analysis requires it. So the only way to pass database object dynamically is using Dynamic SQL.
Below is a basic example using a function.
But the underlying problem in the above code is that i have use "*" in the column list of the select statement.
SQL> create or replace function print_table 2 ( 3 pTablename in varchar2 4 ) 5 return sys_refcursor 6 as 7 l_ref_cursor sys_refcursor; 8 begin 9 open l_ref_cursor for 'select * from ' || pTableName; 10 return l_ref_cursor; 11 end; 12 / Function created. SQL> var rc refcursor SQL> exec :rc := print_table('emp') PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COM DEPTNO ---------- ------ --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 02-APR-81 2975 0 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 0 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 0 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 0 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 0 20 7839 KING PRESIDENT 17-NOV-81 5000 0 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 0 20 11 rows selected.
So the client will be totally unaware of what he is going to get. So again think twice before getting into a mess with dynamic SQL.
1004937 wrote:Why do you? Don't you know the names of the tables on your database? That would seem like a poorly designed database or poorly designed application requirement, if you can't know at design time what the tables are called that you need to use. That results in having to use dynamically generated queries, which leads to more dyanmically generated code to process those queries, and then you'll probably be wanting a dynamically generated interface to adapt the output (and maybe input) for the user etc. and you end up with a whole load code that is unperformant, buggy, unscalable and liable to security issues.
I know this.
But i can't use scott.emp
I have to pass the table name at run time.
I am using this in function.Why do you think a function can't have dynamic sql...
But function don't use dynamic sql.
Dynamic sql is used in procedure but i can't use procedure.... but you think a procedure can. ?
There's nothing to stop you creating dynamic sql in either a function or procedure. The only differences between a function and a procedure is that a function returns a value whereas a procedure doesn't, and functions can be used in SQL statements (subject to some restrictions).