1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Calling .sql file inside another .sql file

Discussion in 'SQL PL/SQL' started by gokul1242, Jun 5, 2012.

  1. gokul1242

    gokul1242 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    I have created a procedure and saved as category.sql

    i run this file using the command @source/category.sql

    Now i have created another procedure and save as test1.sql

    Now i want to run this test1.sql inside the category.sql
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Gokul , I have 3 queries
    Can you share your answers here

    1.What is the importance of .sql ?
    2.What have you saved in .sql file ?
    3.Why do you want to call .sql file ?
     
  3. gokul1242

    gokul1242 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi I have created a procedure for validating user name and password .this procedure i have saved as a .sql file
    to run the .sql file i use the syntax @filename

    now i have created another procedure and saved as a .sql file...

    now i want to the run the second .sql file inside the first .sql file..
    ie., if the user validation is success i want to run this second.sql file....
     
  4. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    If you run @filename it will run the underlying script and procedure will be created.

    In order to run the procedure

    in sql

    prompt : Exec <Proc name{(param1,param2)}>;

    in pl/sql : {Call} <Proc name {(param1,param2)}>;

    Run the procedure which you have created in category.sql and call second procedure created in test.sql from first procedure.

    Update your results as soon as you complete the task.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I think the scripts are merely scripts and not stored procedures. And I think he/she wants to invoke some logic where if the first script executes succesfully the second script should be run. No such logic exists in the SQL*Plus interface -- you will need a shell script and some modifications to your original .sql scripts to make this work. See below:

    testit1.sql

    whenever sqlerror exit sql.sqlcode

    select * From emp;

    exit


    testit2.sql

    whenever sqlerror exit sql.sqlcode

    select * From dept;

    exit

    Shell script:

    #!/bin/ksh

    SQLP=`which sqlplus`
    USERPASS="blorb/yompo"

    if [ "$SQLP" = "" ]
    then

    echo "sqlplus not found"
    exit 8

    fi

    $SQLP $USERPASS @testit1.sql

    status=$?

    if [ $status -eq 0 ]
    then
    $SQLP $USERPASS @testit2.sql
    else
    echo "testit1.sql failed -- see log for errors'
    exit 9
    fi

    It's a basic example but it shows how to conditionally execute SQL*Plus scripts.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hi gokul,

    Oracle PL/SQL praogamming logic is not based on running sql files. File based programming is there for some other languages like perl. You can try the following steps if you want to run second procedure only after successfully executing the first procedure.

    1. Create the two proceduress in Oracle DB.
    2. Ensure that exception is handled in first procedure ans you get the proper success flag as output variable in first procedure.
    3. Frame a anonymous block (or another procedure) where you call the first procedure and get the success flag from it and call the second procedure based on the success flag.
    4. Call the anonymous block (or another procedure).

    regards,
    Rajuvan.