How to pass parameter from sqlplus(procedure completed) to your shell script


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to pass parameter from sqlplus(procedure completed) to your shell script
# 1  
Old 08-08-2008
How to pass parameter from sqlplus(procedure completed) to your shell script

Code:
if [ ! -f  /export/home/oracle/nas/scott21.dmp.gz ]
then
   # mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com
        echo "FILE does not exist"
        exit 1
fi
echo "FILE EXIST"
size=-1
set $(du /export/home/oracle/nas/scott21.dmp.gz)
while [ $size -ne $1 ]
do
        echo "Inside the loop"
        size=$1
        set $(du /export/home/oracle/nas/scott21.dmp.gz)
        sleep 1
done
echo "FTP DONE"
gunzip /export/home/oracle/nas/scott21.dmp.gz > /dev/null
if [ $? -ne 0 ]
then
        #mail -s "Import failed file exist but corrupted" sanjay.jaiswal@xyz.com
        echo "FILE corrupted"
        exit 1
fi

echo "starting import"
 sqlplus /nolog << EOF
 connect system/oracle
 @/export/home/oracle/nas/zero_test_create.sql
 exit
  EOF
`
echo "On linux Prompt"
imp system/oracle file=/export/home/oracle/nas/scott21.dmp grants=n rows=y ignore=y log=/export/home/oracle/nas/zero_test.log
 touser=prakash,sanjay fromuser=scott,system

echo "Import Completed"
cat /export/home/oracle/nas/zero_test.log | grep -v IMP-00041 | grep 5 IMP- > import_error.log
# mail -s "Export failed " sanjay.jaiswal@xyz.com < import_error.log
exit 0
 
==========
 
declare
cursor username_c is SELECT TARGET_ADMIN FROM impdp;
cursor kill_session_c(username_v varchar2) is SELECT sid,serial# FROM V$session WHERE username=username_v;
username_vc username_c%rowtype;
kill_session_vc kill_session_c%rowtype;
l_kill_count NUMBER DEFAULT 0;
l_user_exist number default 0;
run varchar2(80);
BEGIN
        open username_c;
        LOOP
        fetch username_c INTO username_vc;
        EXIT WHEN username_c%notfound;
        open kill_session_c(username_vc.TARGET_ADMIN);
                LOOP
                        fetch kill_session_c INTO kill_session_vc;
                        EXIT WHEN kill_session_c%notfound;
                        DBMS_OUTPUT.PUT_LINE('The '||  username_vc.TARGET_ADMIN  || ' session is being killed ');
                        run := 'alter system kill session ''' || kill_session_vc.sid || ','
                                        ||kill_session_vc.serial# || ''' immediate ' ;
                        execute immediate run;
                        DBMS_OUTPUT.PUT_LINE('The '||  username_vc.TARGET_ADMIN  || ' session with sid,serial#'             ||kill_session_vc.sid ||' , ' || kill_session_vc.serial# );
                END LOOP;
        close kill_session_c;
                LOOP
                        SELECT nvl(count(*),0) INTO l_kill_count FROM v$session WHERE username = username_vc.TARGET_ADMIN;
                        EXIT WHEN l_kill_count = 0 ;
                        dbms_backup_restore.sleep(0.1);
                END LOOP;
        run := 'drop user ' || username_vc.TARGET_ADMIN || ' cascade' ;
        BEGIN
                execute immediate run;
                DBMS_OUTPUT.PUT_LINE('Droping ' || username_vc.TARGET_ADMIN);
        EXCEPTION
                WHEN others then
                        DBMS_OUTPUT.PUT_LINE('Oracle Error ' ||substr(sqlerrm,1,250));
        END;
                LOOP
                        SELECT nvl(count(*),0) INTO l_user_exist FROM dba_users WHERE username = username_vc.TARGET_ADMIN;
                        EXIT WHEN l_user_exist = 0 ;
                        dbms_backup_restore.SLEEP(1);
                END LOOP;
        DBMS_OUTPUT.PUT_LINE(username_vc.TARGET_ADMIN || ' dropped');
        DBMS_OUTPUT.PUT_LINE(username_vc.TARGET_ADMIN || ' is being created ');
        run := 'create user ' || username_vc.TARGET_ADMIN || ' identified by advantage ' ;
        execute immediate run;
        run := 'grant connect,resource to ' || username_vc.TARGET_ADMIN   ;
        DBMS_OUTPUT.PUT_LINE(username_vc.TARGET_ADMIN || ' is created ' );
        execute immediate run;
--      update impdp set CREATE_USER=1 where TARGET_ADMIN=username_vc.TARGET_ADMIN;
        commit;
--      datapump_admin_api(username_vc.TARGET_ADMIN);
        DBMS_OUTPUT.put_line(username_vc.TARGET_ADMIN);
   END LOOP;
END;
/

# 2  
Old 08-08-2008
I am sorry. what i want is that the shelll should wait till hte pl/sql(called from shell) porcedure is completed and after completion shell should start the rest with rest of code.
Please also let me know if hte shell has program has any othere problem

Thanks and Regards
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to pass the parameter in xml file in UNIX shell script?

Hi, I have an XML file like the following... <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <ONDEMAND_JOB VERSION="5.1" LOCALE="en_US"> <IMPORT_JOBSET TC_CONNECTION_NAME="default" ENVIRONMENT="PRD" USERNAME="Administrator" PASSWORD="AdminPassword" CALENDAR="Main Monthly Calendar"... (2 Replies)
Discussion started by: Debalina Roy
2 Replies

2. Programming

How to pass parameter from file to sqlplus in UNIX?

i have file in which i have employee id are there and every time number of employee id are different in file means number of count of employee id in file are every time different. 343535435 365765767 343534543 343543543 i want to pass this file to sqlplus and sql command is ... (7 Replies)
Discussion started by: pallvi_mahajan
7 Replies

3. Post Here to Contact Site Administrators and Moderators

Unable to pass shell script parameter value to awk command in side the same script

Variable I have in my shell script diff=$1$2.diff id=$2 new=new_$diff echo "My id is $1" echo "I want to sync for user account $id" ##awk command I am using is as below cat $diff | awk -F'~' ''$2 == "$id"' {print $0}' > $new I could see value of $id is not passing to the awk... (0 Replies)
Discussion started by: Ashunayak
0 Replies

4. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

5. Shell Programming and Scripting

Passing a parameter from a shell script to sqlplus

Hi All, I'm new to Linux and scripting, apologies in advance for 'stupid' questions. Please help... Im writing a script that calls a sqlplus script but the sqlplus requires inputs and i cant seem to get this to work. here is my code. #!/bin/sh TERM=vt100 export TERM... (4 Replies)
Discussion started by: Mahomed
4 Replies

6. Shell Programming and Scripting

How to get OUT parameter of a stored procedure in shell script?

I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible? (6 Replies)
Discussion started by: vel4ever
6 Replies

7. Shell Programming and Scripting

How to call stored procedure with CLOB out parameter from shell script?

I have written a stored procedure in oracle database, which is having a CLOB OUT parameter. How can i call this stored procedure from shell script and get the CLOB object in shell script variable? (0 Replies)
Discussion started by: vel4ever
0 Replies

8. Shell Programming and Scripting

Pass parameter to nawk from shell script

I need to parse log files using nawk, but I'm not able to pass script input argument (date) to nawk, for example: ------------ #!/bin/ksh read date nawk -F, '{if($1==date) print $4" "$5}' ------------- Is there a way to pass an argument to nawk from shell script. Many thanks... (8 Replies)
Discussion started by: samer.odeh
8 Replies

9. Shell Programming and Scripting

How to Pass the Output Values from the PL/SQL Procedure to Shell Script?

hi, Could anyone tell me how to pass the output values of the PL/SQL procedure to Shell script and how to store that values in a shell script variable... Thanks in advance... (5 Replies)
Discussion started by: funonnet
5 Replies

10. Shell Programming and Scripting

passing parameter 4m shell script to a DB stored procedure

hi all please tell me how to pass parameters 4m shell script to a DataBase stored procedure. To be specific i have sybase DB. i mean i want the syntax of the command.. how to connect to DB, pass user id and password, pass the required parameter to SP.. .. need ur help frnds.. hema (0 Replies)
Discussion started by: hema2026
0 Replies
Login or Register to Ask a Question