Calling a PL/SQL procedure from UNIX
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Calling a PL/SQL procedure from UNIX

Hybrid View

  1. #1
    Join Date
    Jun 2001
    Posts
    31
    Hi ...

    Help needed urgently.

    Could someone provide a small example of:
    Calling a PL/SQL stored procedure and its parameters from UNIX

    and/or

    Calling a PL/SQL package and its stored procedure and parameters from UNIX.

    Thanks,
    AED

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    #!/bin/ksh
    PARAM1=ABC
    sqlplus / << EOF
       execute YourPackage.YourFunc('$PARAM1');
    EOF
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2001
    Posts
    31
    Hi Jeff,

    I tried what you provided, but did not work.
    Could you send me a more specific example.

    This is the kind of errors I got:
    --------------------------------
    create or replace procedure proc1(
    name varchar2) as
    begin
    dbms_output.put_line('The name is : ' || name);
    end proc1;


    SQL> execute proc1 ('akl');
    The name is : akl

    PL/SQL procedure successfully completed.

    From UNIX:
    ------------
    $ PARAM1=Akl
    $ echo $PARAM1
    Akl
    $ sqlplus / execute proc1('$PARAM1')
    /usr/bin/sh: Syntax error: `(' is not expected.
    $ sqlpus / execute proc1('$PARAM1');
    /usr/bin/sh: Syntax error: `(' is not expected.
    $ sqlplus / execute proc1 '$PARAM1'
    Usage: SQLPLUS [ [
    AED

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by appash
    Hi Jeff,

    I tried what you provided, but did not work.
    Could you send me a more specific example.

    This is the kind of errors I got:
    --------------------------------
    create or replace procedure proc1(
    name varchar2) as
    begin
    dbms_output.put_line('The name is : ' || name);
    end proc1;


    SQL> execute proc1 ('akl');
    The name is : akl

    PL/SQL procedure successfully completed.

    From UNIX:
    ------------
    $ PARAM1=Akl
    $ echo $PARAM1
    Akl
    $ sqlplus / execute proc1('$PARAM1')
    /usr/bin/sh: Syntax error: `(' is not expected.
    $ sqlpus / execute proc1('$PARAM1');
    /usr/bin/sh: Syntax error: `(' is not expected.
    $ sqlplus / execute proc1 '$PARAM1'

    Try:
    Code:
    $ PARAM1=Akl
    $ echo $PARAM1
    Akl
    $ sqlplus yourusername/yourpassword@yourdb << EOF
    execute proc1('$PARAM1');
    EOF
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Jun 2001
    Posts
    31
    Thanks a lot Jeff.. it worked.

    AED

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Just be careful with that, because if a user on unix does a

    /usr/proc/bin/ptree

    they potentially could see your username and password running the script.

    A better method I think would be

    USER1="scott"
    PASS1="tiger"
    COM2="connect $USER1/$PASS1"
    COM3="sqlplus /nologon"

    $COM3 < $COM2
    set serveroutput on
    execute test_package.test_procedure
    ESQL

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I would prefer to do it with OS authentication, but the poster seemed to get confused.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Actually, in my example I forgot about passing the parameters to oracle also. I'm sure they'll work it out.

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

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