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!

Can PL/SQL program call (execute) *.sql files.

Discussion in 'SQL PL/SQL' started by keekee, Apr 8, 2010.

  1. keekee

    keekee Guest

    Can PL/SQL program call (execute) *.sql files.

    For example:

    create or replace execute_sql (....)
    begin
    execute my_trigger.sql
    end;

    I use Oracle 10.0.2.0.1

    Thanks
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hmhm.. not an easy task .

    You have to use either
    1. external procedures or
    2. DBMS_PIPE package.

    Follow this link
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not directly. It can call an external procedure (C, C++ or Java stubs external to the database) which can be written to call SQL*Plus to execute the script. DBMS_PIPE cannot execute scripts as it only passes messages between PL/SQL blocks/programs. What prevents you from converting this script to a PL/SQL block, procedure or function? Without further information I see little purpose in this exercise.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    DBMS_PIPE can be used to execute shell scripts from Procedure. And it could be widely using apart from just passing messages between PL/SQL block. but executing Script could be tougher using DBMS_PIPE. But it is possible.

    See this link from asktom

    Another way to execute the shell script ( script to login the DB and execute the ql script) is by using DBMS_SCHEDULER.

    See this link from Orafaq wiki
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If the .sql script is wrapped in a shell script then I can agree it's possible. It won't work without a shell wrapper of some sort as sqlplus will need to be called. The same situation exists with extproc; the C/C++/Java stub needs to call sqlplus to execute the .sql script. All of this is likely more work than simply rewriting the script into a procedure to be called or incorporating the script code into the existing PL/SQL program unit.
     
  6. SteveS

    SteveS Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Stroudsburg, PA USA
    I don't know of any reason you can't read the file line by line concatenating each line to a variable in your program. You will have to remove the trailing ';' or '/' from it. It will also have to be in a directory on the database server - not on your local machine. The directory on the server will need to have an Oracle 'DIRECTORY' created for it and you will have to be granted read permission on the directory. Once you read the entire SQL script into a variable - execute it with 'EXECUTE IMMEDIATE your_sql_variable;'

    Here's some code I have that reads a log file - but a file is a file: (in this example I am ONLY interested in the last line, so I don't concatenate the lines together).

    Code (SQL):
     
    BEGIN
     
    v_directory := UPPER(p_directory);
    v_log_file_name := p_log_file_name;
    log_file := utl_file.fopen(v_directory,v_log_file_name,'R');
     
    LOOP
    utl_file.get_line(log_file,v_line);
    END LOOP;
     
    utl_file.fclose(log_file);
     
    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
        DBMS_OUTPUT.put_line('END OF FILE: read_log_file');
     
    RETURN v_line;
     
    END READ_LOG_FILE;
     
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The ending seimcolon terminating most SQL queries cannot be included in the dynamic query string and the string is restricted in length to 32767 bytes. The first restriction can invalidate any concatenated SQL statement from a .sql file and make it useless if the semicolon is not removed. The second restriction can eliminate some .sql files from consideration simply because they are too long to concatenate into a 32767 byte variable:

    Code (SQL):
     
    SQL> DECLARE
      2          sql_file        utl_file.file_type;
      3          sql_txt         varchar2(32767);
      4          v_line          varchar2(300);
      5  BEGIN
      6          sql_file := utl_file.fopen('FILE_TEST', 'schema_info.sql', 'R');
      7
      8          loop
      9                  utl_File.get_line(sql_file, v_line);
     10                  sql_txt := sql_txt||v_line||' ';
     11          END loop;
     12
     13          utl_File.fclose(sql_file);
     14
     15          EXECUTE immediate sql_txt;
     16
     17  END;
     18  /
    DECLARE
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: NUMERIC OR VALUE error: CHARACTER string buffer too small
    ORA-06512: at line 10

    SQL>

     
    Yours is an interesting suggestion but it can fall short simply because you are not aware of the limitations of EXECUTE IMMEDIATE.
     
  8. SteveS

    SteveS Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Stroudsburg, PA USA
    Well, I guess I've been told my suggestion isn't welcome in a rather harsh way since it didn't agree with what you had previously told everyone. Don't worry, I won't bother trying to help anymore here anymore. Attitudes like yours kill websites.
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You've been told nothing that should lead you to believe your suggestion isn't 'welcome', only that under certain conditions it may fail to produce the desired results.

    Please explain what 'attitude' is present in my response? I have illustrated shortcomings in a suggested solution and brought to light possible errors that could arise, nothing more. You have attributed 'attitude' where none esists.
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hello steve

    I see no personal comment in Zargon's post. All he was doing was pointing out technical limitation of your solution. That does not mean suggestion is not welcome. I am sorry you felt offended.

    Regards