#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11

    Execute Create Procedure/Function from SQL Script in SQLPlus


    Hey all,

    I have a series of SQL scripts which contain SQL statements to create tables, populate them, create functions and stored procedures. Now I would like to execute each sql file against SQLPlus using a batch file so that I can just run this one file and all the configuration work I need to do can get done.

    Problem is, when I try to execute the SQL file against SQLPlus, it gets upset with the Create Procedure/Function scripts...

    I am using the following command:

    sqlplus u/p@<someserver> @<path_to_sqlfile>.sql

    this sql file contains create procedure pl/sql code

    Is it possible create/compile SP/Functions that are contained within SQL Files using SQLPLus? Or do I have to physically write them out in SQLPLus (or load them in SQLDeveloper) to accomplish this?

    Many thanx
    B
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    it gets upset with the Create Procedure/Function scripts...
    I'm betting that that is not the error message you get

    What do you get?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Originally Posted by clivew
    I'm betting that that is not the error message you get

    What do you get?
    I wish it was

    SQL*PLus Release...etc
    Conencted to: Oracel 11g Database Edition...etc

    129

    that's it...everytime I press enter it adds another sequenial number:

    130
    131

    The number it gives is the last line number in the sql file? It seems to do this with sql files that contain basic insert statements as well as the sql files that contain create procedure/function pl/sql
    Last edited by amstel_za; February 18th, 2010 at 06:18 AM.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    How about posting the (entire) contents of one of those scripts?
    It is hard to simply guess at the problem in a vacuum.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Originally Posted by clivew
    How about posting the (entire) contents of one of those scripts?
    It is hard to simply guess at the problem in a vacuum.
    Ah yes, a very good idea

    Here is one of the scripts (in a sql file) that I am trying to execute

    create or replace PROCEDURE NW_UPDATE AS
    BEGIN

    ALTER TABLE MN_NW ADD ( fcode number, micode number, spro_style varchar2(100);

    UPDATE MN_NW SET (micode) =
    (Select micode from _LOOKUP Where MN_NW.FCODE
    = _LOOKUP.FCODE) ;
    UPDATE MN_NW SET (spro_style) =
    (Select style from _STYLES Where MN_NW.MICODE
    = _STYLES.MICODE) ;

    END NW_UPDATE;
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool End of the line ...er... procedure


    Originally Posted by amstel_za
    I wish it was

    SQL*PLus Release...etc
    Conencted to: Oracel 11g Database Edition...etc

    129

    that's it...everytime I press enter it adds another sequenial number:

    130
    131

    The number it gives is the last line number in the sql file? It seems to do this with sql files that contain basic insert statements as well as the sql files that contain create procedure/function pl/sql
    You need to add a slash (/) at the end of the create procedure/package/function statements.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Originally Posted by LKBrwn_DBA
    You need to add a slash (/) at the end of the create procedure/package/function statements.
    Hi,

    Do you mean END NW_UPDATE; becomes END NW_UPDATE;/ ?

    If so I had NOOOOO idea I needed to do that...learn something knew every day
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by amstel_za
    If so I had NOOOOO idea I needed to do that...
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch4.htm#sthref840
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2004
    Posts
    378
    Rep Power
    11
    Arrrghhh....I'm tattooing RTFM onto my forehead!!!!

    Thank you very much for the input and help all - worked like a charm!

IMN logo majestic logo threadwatch logo seochat tools logo