Execute SQL Script from AWS RDS Oracle

Question

I am trying to execute  a SQL script from AWS RDS Oracle instance.
The steps I followed

1)  CREATE D A Directory and GRANTED READ WRITE
EXEC rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'deployment_directory');
GRANT READ,WRITE ON DIRECTORY DEPLOYMENT_DIRECTORY TO rdsadmin;
2)  Downloaded the file to AWS RDS instance from the S3 bucket

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
      p_bucket_name    =>  'ojp-bvp-dev-01',
      p_directory_name =>  'DEPLOYMENT_DIRECTORY')
   AS TASK_ID FROM DUAL;

3)  The file deploy-1.sql got downloaded to the AWS RDS database in the DEPLOYMENT_DIRECTORY that was just created

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'DEPLOYMENT_DIRECTORY'));
FILENAME                                                               TYPE         FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- ---------
01/                                                                    directory        4096 22-JAN-23
deploy-1.sql                                                           file              674 22-JAN-23

4) The path of the directory
SELECT DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME='DEPLOYMENT_DIRECTORY';
 DIRECTORY_PATH
--------------------------------------------------------------------------------
/rdsdbdata/userdirs/01

5) I try to execute the SQL script and I get an error
SQL> spool /rdsdbdata/userdirs/01/deploy-1.log
SP2-0606: Cannot create SPOOL file "/rdsdbdata/userdirs/01/deploy-1.log"
SQL> @/rdsdbdata/userdirs/01/deploy-1.sql
SP2-0310: unable to open file "/rdsdbdata/userdirs/01/deploy-1.sql"

How do I execute sql scripts in AWS RDS Oracle? Is there a utility that can be used?

Thanks
Rajan

Answer

The sql script should be on the host where you are running sqlplus on, not on the database (RDS) server.

Answer

Yes, thanks I  I  uploaded the sql script on the HOST where I am running sqlplus ran the scripts and it worked. 
I dont know why I thought I had to upload it onto the AWS RDS server.

Thanks a lot!
Appreciate it

Answer

The steps you've followed to download the script from an S3 bucket and create a directory in the RDS instance are correct. However, there seems to be an issue with the execution of the script.

To execute the SQL script, you can use the @ command followed by the path of the script file. In this case, the path of the script file is '/rdsdbdata/userdirs/01/deploy-1.sql'

The error message "SP2-0310: unable to open file "/rdsdbdata/userdirs/01/deploy-1.sql"" suggests that the SQLPlus client is unable to access the file. This could be due to file permissions or a lack of read access to the file. You should check the permissions of the file to ensure that the user running SQLPlus has read access to it.

Another way to execute the script is to use the SQL*Plus command line utility, which can be run on the RDS instance or on a local machine to connect to the RDS instance. You can use the command: sqlplus -S /@:/ @/rdsdbdata/userdirs/01/deploy-1.sql

Another utility that can be used is the Oracle SQL Developer, which is a free GUI tool provided by Oracle for managing and executing SQL scripts on Oracle databases. It can be used to connect to the RDS instance and execute the script.

Lastly, it is important to note that RDS instances have certain limitations, like the lack of access to the underlying file system. So, it is better to use a method that does not rely on the file system, such as using the SQL*Plus command line utility or SQL Developer to directly execute the script.

Execute SQL Script from AWS RDS Oracle

Relevant content