DBMS package that issuse commands to SQL*PLUS??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: DBMS package that issuse commands to SQL*PLUS??

  1. #1
    Join Date
    Aug 2001
    Location
    Philadelphia, NJ
    Posts
    44

    Question

    I need to know if there is a package in oracle 7.3.4 that will allow me to send a sql plus command from a procedure/ pl/sql block? I need to send the command "net send username . I know using the HOST command allow this to happen in sql*plus but I it to be ran from a procedure. Any ideas on how to do this?

    Thanks
    Eric
    EJC

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by coleex
    I need to know if there is a package in oracle 7.3.4 that will allow me to send a sql plus command from a procedure/ pl/sql block? I need to send the command "net send username . I know using the HOST command allow this to happen in sql*plus but I it to be ran from a procedure. Any ideas on how to do this?
    No, there is no such, package. In 7.3 you can't even use external procedures, so the only option is to use DBMS_PIPE and a "deamon listener". Very messy stuff....

    But does this "net send..." has to be executed sinchronously? If this is not very strict demand you could take advantage of UTL_FILE package to write command to some batch file and have OS scheduler periodically check for this batch file and execute it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    We had a similar problem at one place I worked. The solution we used was messy. Basically we had the situation where we wanted PLSQL to fire unix commands. In the end we ended up having the PLSQL insert the command to an oracle tanle, and then having a UNIX cron-job read the table once per 3 minutes and throwing whatever text at the OS.

    This worked BUT.... its a huge security risk if you are not carefull. It meant that anyone with insert priviledge on the table could effectively run OS commands as ORACLE. Not nice. Not pretty. Not designed by me. Saying that, it did work.

    Failing that, im sure that I saw somewhere an example procedure of using Java in the database to fire OS commands - that may be your safest bet.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by bmycroft
    Failing that, im sure that I saw somewhere an example procedure of using Java in the database to fire OS commands - that may be your safest bet.
    Unfortunately not in 7.3.4
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Ah, yes, that'll teach me not to read the first line properly...

  6. #6
    Join Date
    Aug 2001
    Location
    Philadelphia, NJ
    Posts
    44

    Cool Will try that

    Ok I'll try that UTL_FILE package and see what that does. Thanks for the input. I was begging to think i had the most original problem in the histroy of oracle. =) Thanks will post if it works.

    Thankyamuch
    EJC

  7. #7
    Join Date
    Oct 2001
    Location
    Mexico
    Posts
    1

    Post

    Originally posted by coleex
    I need to know if there is a package in oracle 7.3.4 that will allow me to send a sql plus command from a procedure/ pl/sql block? I need to send the command "net send username . I know using the HOST command allow this to happen in sql*plus but I it to be ran from a procedure. Any ideas on how to do this?

    Thanks
    Eric

  8. #8
    Join Date
    Oct 2001
    Location
    Pune
    Posts
    1

    Wink DBMS_Package

    For that u have to run the three sql file named as

    utlirp.sql,catalog.sql.catporc.sql when connected to sys/system

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I'm not sure if utl_file_dir parameter do exist in version 7.3.4, anyway, just create an output file using dbms_output
    and execute this output through scheduling.

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