fucntion query
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: fucntion query

  1. #1
    Join Date
    Jun 2001
    Posts
    316
    hi,
    i creatt this function where in i pass table name as parameter to the it..
    How do i use this parameter in a select query?
    eg here its "name1"

    CREATE OR REPLACE FUNCTION Testing2 (name1 in varchar2)........

    ...

    FOR x IN (SELECT DISTINCT id FROM name1 GROUP BY id) LOOP
    ...........

    Thanx a lot
    Sam

    [Edited by helpme on 11-28-2001 at 07:48 AM]

  2. #2
    Join Date
    Jun 2001
    Posts
    316
    a reminder

  3. #3
    Join Date
    Sep 2001
    Posts
    112
    Is that query in the function ?

    or are you writing it totally unrelated to the function?

  4. #4
    Join Date
    Jun 2001
    Posts
    316
    ya its in the function


    CREATE OR REPLACE FUNCTION Testing(a in varchar2) RETURN number IS
    BEGIN

    FOR x IN (SELECT DISTINCT id FROM a GROUP BY id) LOOP
    blah;
    blah;
    end loop;
    return 1;
    end;


    I need to replace the "a" in te query from the input parameter...

    Thanx
    Sam

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    In this case you must use dynamic SQL:
    ...
    stmt := 'select distinct id from '||tbl_name';

    GROUP BY id - not needed in this sql.


  6. #6
    Join Date
    Jun 2001
    Posts
    316
    i tried this...
    it dint work though


    CREATE OR REPLACE FUNCTION Testing2(a3 in varchar2) RETURN myTableType2 IS
    a2 VARCHAR2(30):='#';


    lAnswer myTableType2:=myTableType2();
    i NUMBER:=1;
    cursor c1(tabname varchar2) is
    SELECT DISTINCT id FROM tabname ;
    cursor c2(tabname varchar2,id1 number) is
    SELECT name FROM tabname WHERE id=id1;

    BEGIN
    FOR x IN c1('&a3') LOOP
    lAnswer.extend;

    FOR y IN c2('&a3',x.id) LOOP
    IF a2 ='#' THEN
    a2 := x.id||','||y.name;
    ELSE
    a2 := a2||','||y.name;
    END IF;
    END LOOP;
    lAnswer(i):=myRecordType2(a2,a2);
    i:=i+1;
    a2:='#';
    END LOOP;
    RETURN lAnswer;
    END;



    Whee do i go wrong ..or is there a possible simpler way to go about this?
    Thanx
    Sam

  7. #7
    Join Date
    Sep 2001
    Posts
    112
    I would suggest you use a cursor and pass the parameter into the cursor

    CREATE OR REPLACE FUNCTION Testing(p_table_name in varchar2) RETURN number IS

    DECLARE

    cursor my_cursor(c_table_name in varchar2) is
    SELECT DISTINCT id FROM c_table_name;

    my_cursor_rec mycursor%ROWTYPE;

    BEGIN

    open cursor my_cursor(p_table_name);
    fetch my_cursor into my_cursor_rec;

    while my_cursor%FOUND loop
    STATEMENTS
    end loop;

    END

    I may have got some syntax wrong but if alot of that makes no sense then ignore it. You may want to try and teach yourself some PL/SQL. It can be extremely useful to you.

    The function can only return one value and it looks like your query returns more than one. You could use a procedure to list all the values but the theory behind passing a parameter as a table to use in a query is all in that example.

    Hope it might help a bit

  8. #8
    Join Date
    Sep 2001
    Posts
    112
    I'm pretty sure you don't use those ampersands that you have.

    Good luck with it

  9. #9
    Join Date
    Jun 2001
    Posts
    316
    this is the error i get

    PLS-00201: identifier 'TABNAME' must be declared...

  10. #10
    Join Date
    Jun 2001
    Posts
    316
    i get error while doing this

    cursor c1(tabname1 varchar2) is
    SELECT DISTINCT id FROM tabname1;
    begin
    FOR x IN c1('a3') LOOP.........



    error is

    PLS-00201: identifier 'TABNAME1' must be declared
    ne ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width