Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: Executing a function and accessing its output... Help please...

    Hi,

    This probably sounds like a newbie question, but I've searched quite a few sites (including this one) and am struggling to firm-up exactly how I code what I'm trying do.

    Using 'sqlplus' I want to execute an Oracle Function that needs 5 input parameters and produces 1 output parameter, which is a single digit integer (essentially a return code).

    I'm running all this from within a Unix Shell Script, accessing the Oracle DB via an SQL client.

    The input parameters are being generated within the Shell Script and I want to be able to access the output parameter and use it in the Shell Script.

    Assuming:-
    The name of the Oracle Function that has been created is called 'my_ora_func'

    The input parameters, within the Function are called:-
    V_Iparam1,
    V_Iparam2,
    V_Iparam3,
    V_Iparam4,
    V_Iparam5,

    and the output parameter is called:-
    V_Oparam

    The input parameters, generated within my script for the Oracle Function are:-
    S_param1,
    S_param2,
    S_param3,
    S_param4,
    S_param5,

    and the output parameter is called:-
    S_Outparam

    I'm coding the 'sqlplus' commands within a Unix Shell Script, creating a 'here' document like below.

    S_Outparam=`sqlplus user/pass@dbprod << EOF
    exec my_ora_func('$S_param1','$S_param2','$S_param3','$ S_param4','$S_param5')
    EOF`

    But this poses two questions.

    Firstly, Is this the best/correct way to get the output parameter from the Oracle Function into the Shell Output Parameter called "S_Outparam"?
    Secondly, I'd like to explicitly assign the Shell Input Parameters to the correct Oracle Function Input Parameters in the 'exec' line.
    How do I go about that? Something like this???

    exec my_ora_func('V_Iparam1'='$S_param1','V_Iparam2'='$ S_param2','V_Iparam3'='$S_param3','V_Iparam4'='$S_ param4','V_Iparam5'='$S_param5')

    Hope someone could answer my hopefully easy questions.

    Kind regards
    Steve.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Getting hot...

    You are on the right track:
    Code:
    S_Outparam=`sqlplus -s user/pass@dbprod << EOF
    set echo off pages 0 feed off ver off
    select my_ora_func('$S_param1','$S_param2','$S_param3','$ S_param4','$S_param5') from dual;
    EOF`
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2008
    Posts
    3
    Thanks 'LKBrwn_DBA'.

    I can see why I might need the '-s', but why the:-

    'select funcname('p1','p2') from dual;'

    command, instead of an 'exec' or 'execute' command? I read up about the 'dummy' dual table.

    Also, any idea how I can explicity state which shell var I want to be assigned to which func var? I would rather not rely on knowing the order of the function vars, just incase someone comes along and changes the function.

    Kind regards
    Steve.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Exec or not exec..that is the question.

    You cannot "exec" a function unless you use the following syntax:

    Code:
    sqlplus ..etc
    var result number;
    exec :result:=my_ora_func('$S_param1','$S_param2','$S_param3','$ S_param4','$S_param5');
    print :result
    exit
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Also, any idea how I can explicity state which shell var I want to be assigned to which func var?
    Use named notation for procedure parameters instead of the positional one, something like
    Code:
    my_ora_func( V_Iparam1=>'$S_param1', V_Iparam1=>'$S_param2' )
    . For more information, have a look into PL/SQL User's Guide and Reference. It is part of Oracle documentation, available e.g. online at http://tahiti.oracle.com/.

  6. #6
    Join Date
    Dec 2008
    Posts
    3
    Thanks to both 'flyboy' & ''LKBrwn_DBA'.
    I'll take your advice and see how I get on.

    Kind regards
    Steve.

Posting Permissions

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