Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Question Unanswered: New to Oracle - Truncates?

    I'm new to Oracle but have been in the PC database field for many years.

    As part of a process, I need to truncate 6 tables in Oracle. Currently I utilize TOAD 8.5 to do it. I utilize SQL+ for running of some canned .sql as well.

    I would like to programatically kick off the truncate from windows if possible.

    Can anyone make a reccommendation?
    ("Give up" doesn't count).


    Thanks in advance,
    Jerry

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You can use sqlplus.exe to run pl/sql scripts, e.g.

    sqlplus / @yourscript.sql

    TRUNCATE is a valid Oracle SQL statement (or command?).

    I think you should be able to look up the rest in the manuals.

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can use the AT scheduler in Windows to kick off a .BAT file that calls
    a SQL text file ...

    ex: callme.bat

    sqlplus @sql_file_to_be_called
    exit

    ex: sql_file_to_be_called

    connect your_username/your_password@your_database;

    truncate table your_tablename1;
    truncate table your_tablename2;

    exit;


    Just an example...
    HTH
    Gregg

  4. #4
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Thanks so much!
    Just what I was looking for.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad it worked out .

    Gregg

  6. #6
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    It seemed straight forward enough. I even found the help online.
    However, I must be missing something really basic.

    My bat file:

    sqlplus @ d:\testarea\test.sql
    exit


    My "test.sql" file:
    CONNECT ME_USR/PASS;
    DISCONNECT;


    I have manually executed the test.sql and it gives me an error:

    SP2-306: Invalid option.
    Usage: CONN[ECT] [logon} [AS {SYSDBA|SYSOPER}]
    where <logon> ::= <username>[/password>][@<connect_string>] | /
    Enter user-name:


    So I tried it from the DOS prompt:
    sqlplus ME_USR/PASS

    and it works great!

    Anyone have any idea on what am I missing here?

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    1. Don't have a space between @ and d:\
    sqlplus @ d:\testarea\test.sql

    2. The problem with the test.sql is the "connect"
    you don't need the word connect just

    user/pass@instance

    HTH
    Gregg

  8. #8
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    ok.

    It still is giving me problems and this should be a breeze.

    Let me ask a dumb question -
    Anything I can do at the SQL prompt, I should be able to put into the .sql file that is called from the .bat, correct?

    I can type My_USER/PASS and get logged in.

    When I place the following in a SQL file, no go:
    MYUSER/PASS;

    exit ;

    It's like I'm typing the wrong username or password.
    I've tried spaces all over the place (even though I didnt see anything in the docs) and it still gives me the error SP2-0306: invalid option.

    I still have hair, so I figure maybe I should start pulling...

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    MYUSER/PASS;

    ensure you don't have the ";" after password ...

    in the .bat file...

    sqlplusw @c:\myfile.sql
    exit

    myfile.sql

    myuser/pass@instance
    select ...
    exit


    HTH
    Gregg

  10. #10
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    It worked!!!
    I removed the "w" and just called sqlplus.
    The problem was with the semicolons. Once I took them all out, it worked great. Now I'm on a roll - thanks so much!

    I thought all lines needed to end in semicolons.

  11. #11
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Thumbs up

    I need to use the semicolons after each TRUNCATE statement line however.
    Works like a champ.
    THanks again.

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Glad it worked out

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •