Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Unanswered: Calling stored procedure in SQL statement

    Hello there.
    I created a stored procedure which always return '1=0' or '1=1'.
    I am trying to call this stored procedure in SQL, but it seems Oracle always treats it like a table variable and give me an error message.

    For example:
    I created a procedure as pro_name, which returns '1=0' or '1=1' based on the inputs.
    I am trying to call it in SQL
    as
    select * from table_name
    where table.name = 'name'
    and pro_name

    But Oracle always give me an error message.
    Is there any way I can call a procedure in SQL?

    thanks a lot.
    P.S the stored procedure is working correctly.

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    I think u can not call stored procedure from sql statement. U can only call function from sql stat.

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  3. #3
    Join Date
    Mar 2004
    Posts
    9
    well, I just changed it to functions, but got some strange problem.
    I created function as return either 1 or 0.
    and have SQL statement as following:

    select * from table_name
    where table.name = 'name'
    and function_name=1;

    it works, except I always got "no rows selected". Somehow the "function_name=1" statement is not working at all.

    I also tried to write query as
    select * from table_name
    where table.name = 'name'
    and function_name;
    but it always give me an error message.

    thanks.

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by loug52
    well, I just changed it to functions, but got some strange problem.
    I created function as return either 1 or 0.
    and have SQL statement as following:

    select * from table_name
    where table.name = 'name'
    and function_name=1;

    it works, except I always got "no rows selected". Somehow the "function_name=1" statement is not working at all.

    I also tried to write query as
    select * from table_name
    where table.name = 'name'
    and function_name;
    but it always give me an error message.

    thanks.
    You cannot do that in SQL - you must write a PL/SQL block to handle that. It would be nice if you could just say - and function_name but that is where your errors are coming into play.

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    I am really confused about ur actual requirment: can please deifne ur problem clearly

    Thanks,
    Pagnint
    (No need to search web before posting new question)

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    you need to do a block like this:
    Code:
    Declare
    v_function number;
    
    cursor c_test is
    select * from table_name
    where table.name = 'name'
    and v_function = 1;
    
    BEGIN
    v_function := your_function(any_parameters_you_have);
    
    for r_test in c_test
    loop
    dbms_output.put_line(col1, col2);
    end loop;
    
    END;

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I'm not sure what your called function contains, but this might offer some insight...

    PHP Code:
    create table test_table col1 varchar2(1) )
    insert into test_table values ('A');
    insert into test_table values ('B');
    insert into test_table values ('C');

    CREATE OR REPLACE FUNCTION TESTFUNinVal TEST_TABLE.COL1%TYPE ) RETURN VARCHAR2 IS
    BEGIN
       
    IF inVal 'A' THEN
          
    RETURN 'Is A';
       ELSE 
          RETURN 
    'Is Not';
       
    END IF;
    END TESTFUN;
    /
    select col1testfuncol1 
    from   test_table
    where testfun
    col1 ) = 'Is A'

    COL1 TESTFUN(COL1)
    ==== =============
    A    Is A 
    Generally though, a function to be called within SQL is better placed within a package so as to allow for a PRAGMA RESTRICT_REFERENCES directive.

    Hth
    Bill

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  8. #8
    Join Date
    Mar 2004
    Posts
    9
    Thanks for the replies.
    Here is the function I wrote

    --**********************************
    create or replace package body role_compare
    as
    test_pred number ;
    role_num number;

    function compare_function(st1 in varchar2, st2 in varchar2) return varchar2
    is
    BEGIN

    IF(st1 = st2)
    THEN
    test_pred :=1;

    ELSE
    select count(*) into role_num
    from role_txt
    where role in (select role
    from role_txt
    start with superrole =st1
    connect by prior role = superrole)
    and role =st2;


    IF (role_num=0)
    THEN
    test_pred :=0;


    ELSE
    test_pred:= 1;

    END IF;

    END IF;
    return test_pred;

    end;
    end;
    /
    --******************************
    the function is working.

    I am trying to use this function as a boolean function
    and have SQL statement is following:

    select name
    from table
    where role_compare.compare_function(table.role, 'EMP');

    If the table role is EMP, the name will be returned,
    otherwise, no thing will returned.

  9. #9
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Your final SQL statement should be...

    PHP Code:
    select name
    from table
    where role_compare
    .compare_function(table.role'EMP')='1'
    You function returns a varchar2, but you are assigning a number to test_pred before returning it. I would suggest you change the function return type to a number and your where clause to where role_compare.compare_function(table.role, 'EMP')=1; (without the quotes).

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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