Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: pass variables to the sql

    Hi guys,
    I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
    from eg:
    UPDATE PS_TEST_TBL
    SET TESTFIELD1 = TEST1A
    WHERE TESTFIELD2 = TEST2A
    AND TESTFIELD3 = TEST3A
    ;
    UPDATE PS_TEST_TBL
    SET TESTFIELD1 = TEST1B
    WHERE TESTFIELD2 = TEST2B
    AND TESTFIELD3 = TEST3B
    ;
    instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
    is this possible.. we will save lot of time..

    please advise.

    Thanks,
    SM

  2. #2
    Join Date
    May 2003
    Posts
    45

    Re: pass variables to the sql

    You can do this by saving all your update statments to .sql or .txt file.
    so when ever u need to run the update just call the .sql or .txt file from Sql prompt

    SQl>@xx.sql or
    SQl>@c:\orawin\bin\aa.txt; Make sure you call the file from correct place.

    If you want the values to be changed every time.U can do this as follws

    UPDATE PS_TEST_TBL
    SET TESTFIELD1 = &TEST1A
    WHERE TESTFIELD2 = TEST2A
    AND TESTFIELD3 = TEST3A; &TEST1A for numeric values and '&TEST1A' for char

    So the system will prompt :Enter value for TEST1A

    Originally posted by meelagupta
    Hi guys,
    I have a quick question .. every month we write lot of script to fix the data.. we use the same sql statements .. just the values are different..
    from eg:
    UPDATE PS_TEST_TBL
    SET TESTFIELD1 = TEST1A
    WHERE TESTFIELD2 = TEST2A
    AND TESTFIELD3 = TEST3A
    ;
    UPDATE PS_TEST_TBL
    SET TESTFIELD1 = TEST1B
    WHERE TESTFIELD2 = TEST2B
    AND TESTFIELD3 = TEST3B
    ;
    instead of writing multiple scripts can we put all the variables (in a file) and pass the file to the file with the sql..
    is this possible.. we will save lot of time..

    please advise.

    Thanks,
    SM

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: pass variables to the sql

    Another way would be to create a stored procedure with the update statements, and pass the values in as parameters.

Posting Permissions

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