Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    1

    Unhappy Unanswered: Sql not in (path)

    This is about an automated script, for which I need to create all tables of the schema. I defined the path to save all that i need in the batch file which calls the create table.SQL.

    My question is:
    Which SQL can I use a PATH with NOT IN to check existing table names and create all other ones?

    SELECT TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER = '&2'
    and &2.TABLE_NAME NOT IN %CURRDIR%\%FILELIST% %BACKUPDIR%\%1_%~2
    ORDER BY TABLE_NAME
    ;

    Any help will be appreciated!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    sqlplus knows NOTHING about Operating System environmental variables!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2011
    Posts
    9
    You would have to pass the environment variables into SQL@PLus itself. Something like this

    e.g.
    Code:
    sqlplus.exe /NOLOG @mysqlfile.sql OWNER %CURRDIR% %FILELIST% %BACKUPDIR% %1:_~2%
    and within the header of your sql file

    Code:
    DEFINE OWNER="&1"
    DEFINE CURRDIR="&2"
    DEFINE FILELIST="&3"
    DEFINE BACKUPDIR="&4"
    DEFINE FILENAME="&5"
    and then within the sql

    Code:
    SELECT TABLE_NAME
    FROM ALL_TABLES
    WHERE OWNER = '&&OWNER'
    and TABLE_NAME NOT IN ('&&CURRDIR\&&FILELIST', '&&BACKUPDIR\&&FILENAME')
    ORDER BY TABLE_NAME
    ;
    Well, that should get the environment variables in but I don't think that SQL is going to bring back what you want and needs some work.

    Hope that helps,
    Mark

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by skolan View Post
    ... Blah, blah, blah...
    My question is:
    Which SQL can I use a PATH with NOT IN to check existing table names and create all other ones?
    ... Etc ...
    Table names in the ALL_TABLES view normally DO NOT match the name of an OS directory path/file.
    Last edited by LKBrwn_DBA; 06-03-11 at 16:16.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    in a PLSQL use UTL_FILE to read ur file, prepare the sql and execute it
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

Posting Permissions

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