Calling PL/SQL Loop in UNIX Environment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Calling PL/SQL Loop in UNIX Environment

  1. #1
    Join Date
    Mar 2002
    Posts
    17
    Hey!!

    I have a problem in my batch Process which is running in
    HP-UNIX 9000 Series (Oracle as Database).I need to run a PL/SQL Loop
    on UNIX Environment but it could be stopped whenever the situation arise.
    To achieve this i Planned to design a table where the LOOP refers a particular
    column while running.I dont know the unix command to do , Please guide me

    Here the Example

    --- LOOP----

    a=`date +%H%M`
    flag=true
    while $flag
    do
    " Here I am calling a PL/SQL Loop"
    sleep 180
    a=`date +%H%M`
    Here the variable "b" should be referred from a Table in the Database
    if [ $a -ge $b ]
    then
    flag=false
    fi
    done


    ---TABLE---

    Table Stucture

    SQL> desc user_log
    Name Null? Type
    ------------------------------- -------- ----
    USERA VARCHAR2(6)
    TIME VARCHAR2(6)
    DTEM DATE

    The values In the table

    SQL> select * from user_log;

    USERA TIME DTEM
    ------ ------ ---------
    LOOP 230000 11-MAR-02
    CS11 230000 11-MAR-02


    I need to refer a "TIME" column where the usera = "LOOP".

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Hi,
    with code below you get the value of a db column into a UNIX variable:
    Code:
    b=`sqlplus -s user/pwd@database <<EOF
    set head off
    select time from user_log where usera='LOOP';
    exit;
    EOF`
    However, it is not very clear to me what you want to do.
    If you need to run a PL/SQL loop and stop it in some situation you have to write it in PL/SQL but the code you posted runs in shell and every 3 minutes reads a column in a database table.
    Post more ...
    Ales

  3. #3
    Join Date
    Mar 2002
    Posts
    17
    Originally posted by ales
    Hi,
    with code below you get the value of a db column into a UNIX variable:
    Code:
    b=`sqlplus -s user/pwd@database <<EOF
    set head off
    select time from user_log where usera='LOOP';
    exit;
    EOF`
    However, it is not very clear to me what you want to do.
    If you need to run a PL/SQL loop and stop it in some situation you have to write it in PL/SQL but the code you posted runs in shell and every 3 minutes reads a column in a database table.
    Post more ...
    Ales
    Thanks for ur reply, which solved my problem...

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