Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2689
  • Last Modified:

FTP using PL/SQL and call PL/SQL procedure from UNIX

How can I call a PL/SQL procedure from a UNIX script?
And it can be done at a scheduled time?
Can I send a file using FTP from a PL/SQL procedure?, if not how can I do it with a UNIX script?

Thanks in advance!
0
marchmx
Asked:
marchmx
1 Solution
 
stemu2000Commented:
0
 
benpungCommented:
below is an example of a shell script that calls pl/sql procedures and scripts. this is done at a scheduled time by using the crontab on the unix server to initiate this shell script, which in turn calls the procedures. hope this helps some.


# . /oracle/local/scripts/oracle.kshrc needs to be ran in order
# for the SQL code to be understood.
. /oracle/local/scripts/oracle.kshrc

# Environment variables
DBINSTANCE=dev
LOGON_NAME=mylogon
LOGON_PASSWD=mypassword
XML_DIR="/home/ehsitdev"

# Change directory to the xml directory
cd $XML_DIR

# Connect to Oracle database
inst $DBINSTANCE

# Start sqlplus, log in to database, and initiate error log file
sqlplus -s $LOGON_NAME/$LOGON_PASSWD << SQLCMDS

     SET heading off
     SET termout on
     SET trimspool on
     SET feedback off
     SET serveroutput on

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Process Start Time" from dual;
EXEC setMaxMemorySize_proc(200000000);
COMMIT;
EXEC bo_material_proc;
EXEC transform_expression_proc;
COMMIT;
@$HOME/insert_to_tmp.sql
COMMIT;
EXEC get_tmp_record_proc;
@$HOME/update_picklist.sql
@$HOME/update_op_uom.sql
COMMIT;
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Process End Time" from dual;

EXIT
SQLCMDS
0
 
Helena Markováprogrammer-analystCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept benpung's comment as answer.

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Henka
EE Cleanup Volunteer
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now