Passing Contents of File as Parameter to Sql*Plus Command

Question

I'm trying to write a sqlplus command that creates a table from a query that is stored in an .sql file.

The particular .sql file that contains the query would be supplied to my sqlplus command as a variable (&v_InputQuery).

I've tried something like this, but it doesn't work.

CREATE TABLE &v_OutputTable AS
(
< &v_InputQuery
)
;

I get an error saying that there's a missing SELECT keyword.

What I'd really like is for &v_InputQuery to be replaced not with the name of the file specified by the user, but with the actual contents of the file. Is there a way to do that?

Thank you very much.

Answer 1

Yes, you can do that. If your query is in a file called v_InputQuery.sql, you can do this:

CREATE TABLE &v_OutputTable AS (
@v_InputQuery.sql
) ;

It's important that the @ is the first character on the line. SQL*Plus will read the file and put its contents at that location. So make sure you don't have any terminating characters in the file such as ; or /.

Answer 2

Unfortunately, You cannot create a SQL*Plus command, but instead create a shell script to do it!

Lets say my_script.sh is below

#you can always complete the user interaction at unix/dos
USER=your_user
PASS=your_pass
DB=your_db
OUTPUT_TABLE=$1;
QUERY_FILE=$2;
SELECT_QUERY=`cat $QUERY_FILE`;

sqlplus -S ${USER}/${PASS}@${DB} << !
SET SERVEROUTPUT ON;
VAR EXITCODE NUMBER;

BEGIN
   EXECUTE IMMEDIATE ' CREATE TABLE $OUTPUT_TABLE AS $SELECT_QUERY ';
   :EXITCODE := SQLCODE;
EXCEPTION
WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE(SQLERRM);
   :EXITCODE := SQLCODE;
END;
/

exit :EXITCODE
!

Executing the script as below(Depends on OS)

ksh my_script MY_OUTPUT_TABLE my_sql.sql;