Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Passing parameter from Unix to Oracle SQL

    Hi, I'm trying to pass schema name as parameter to SQL from unix script. I have two code snippets that are working and would like to understand the best practise. The following are the snippets

    @test.sql $schema_name

    1. select * from "&1".table_name
    - I face an issue when schema name is passed in lowercase letters, but works fine when passed in uppercase. Not sure of what is causing this.

    2. select * from &1..table_name
    - This code works even if schema name is passed in lowercase.

    Please suggest me if any other alternatives, i'm using korn shell and oracle 11G.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL must be known & fixed at compile time.
    in order to have table name as variable you must use EXECUTE IMMEDIATE
    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
    Mar 2007
    Posts
    623
    Hi, the second code looks correct - and as you state it "works" I have no idea which "alternative" solution are you looking for. I see none without change of that script call; but it is impossible to deduce from this snippet, whether it would made sense.

    For understanding of its behaviour, you should consult it in the documentation. For Oracle, it is available in large set of books e.g. online on http://tahiti.oracle.com/.
    You may be interested in SQL*Plus User's Guide and Reference and SQL Language Reference ones.
    For 11gR2, relevant chapters are placed here:
    About running script in SQL*Plus including passing parameters: http://docs.oracle.com/cd/E11882_01/...2.htm#i2696724
    About using SQL*Plus substitution variables: http://docs.oracle.com/cd/E11882_01/...e.htm#CACIFHGB
    About object naming rules in SQL language: http://docs.oracle.com/cd/E11882_01/...008.htm#i27561
    Enjoy the reading.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Oracle stores object as uppercase by default. If you surround a string with double quotes, it does not convert to uppercase. The string "myschema".file will look for myschema.FILE

    The string myschema.file will look for MYSCHEMA.FILE
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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