469,333 Members | 4,459 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,333 developers. It's quick & easy.

the name of a table inside a function

Hello :)
I have this query :
Expand|Select|Wrap|Line Numbers
  1. select MyFunction(MyColumnName)
  2. from MyTableName
How can I get the name of the table MyTableName inside the function MyFunction ?
Also, is this correct under Oracle 7 ?
Expand|Select|Wrap|Line Numbers
  1. create or replace function MyFunction(N out number)return number
  2. is
  3.  buffer varchar2(512);
  4. Begin
  5.  buffer := 'select count(*) from Client_Table';
  6.  execute immediate buffer into N; --Especially this
  7.  return N;
  8. End;
  9. /
  10.  
Thank you for answering :)
May 2 '07 #1
2 957
Dave44
153 100+
Hello :)
I have this query :
Expand|Select|Wrap|Line Numbers
  1. select MyFunction(MyColumnName)
  2. from MyTableName
How can I get the name of the table MyTableName inside the function MyFunction ?
Also, is this correct under Oracle 7 ?
Expand|Select|Wrap|Line Numbers
  1. create or replace function MyFunction(N out number)return number
  2. is
  3.  buffer varchar2(512);
  4. Begin
  5.  buffer := 'select count(*) from Client_Table';
  6.  execute immediate buffer into N; --Especially this
  7.  return N;
  8. End;
  9. /
  10.  
Expand|Select|Wrap|Line Numbers
  1. If you know what MyTableName is why not just code it into the function.  or do you not know it until run time.  If that is the case then you need some dynamic sql.
  2.  
  3. for the second part of your question in 10G it is:
  4.     Expand|Select|Wrap|Line Numbers
  •  
  •     
  •  
  • [152]dave@ORADB> DECLARE
  •   2       var      VARCHAR2(2000) := 'select count(*) from dual';
  •   3       var2     VARCHAR2(4000);
  •   4  BEGIN
  •   5       EXECUTE IMMEDIATE var
  •   6       INTO              var2;
  •   7  
  •   8       DBMS_OUTPUT.put_line(var2);
  •   9  END;
  •  10  /
  • 1
  •  
  • PL/SQL procedure successfully completed.
  •  
  • Elapsed: 00:00:00.00
  • [152]dave@ORADB> 
  •  
  •  
  •     
  •  
  •  
  • May 3 '07 #2
    thank you for answering,
    what i want is a pointer like "this" in C++.
    May 3 '07 #3

    Post your reply

    Sign in to post your reply or Sign up for a free account.

    Similar topics

    17 posts views Thread by warteschlange | last post: by
    6 posts views Thread by dharmadam | last post: by
    6 posts views Thread by Maguila007 | last post: by
    1 post views Thread by CARIGAR | last post: by
    1 post views Thread by Marylou17 | last post: by
    By using this site, you agree to our Privacy Policy and Terms of Use.