0

I have an sqlplus script which needs to call a function. This function creates an SQL select dynamically and has already been compiled in the database. My script needs to call this function, executes the SQL request it returns and then spool the data in a CSV file. My SQLPLUS code is below:

SET HEAD OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 32000
SET PAGESIZE 0
SET TERMOUT OFF
SET ECHO OFF
SET COLSEP ,

    spool /a/b/rvibap/c/&1..csv

    EXECUTE IMMEDIATE build_select(&1)
    spool off;
/

However, I am getting the below error in my CSV file:

BEGIN IMMEDIATE build_select(TYPE); END;

            *
ERROR at line 1:
ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "BUILD_SELECT" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "BUILD_SELECT" to continue.

I am calling my SQL script in the following way :

@test.sql TYPE

I have also tested the build_select function and it functions correctly; return a query in String.

refresh
  • 1,319
  • 2
  • 20
  • 71

2 Answers2

1

There are few issues with your code.

  • There is no BEGIN END block enclosing your EXECUTE IMMEDIATE expression.
  • EXECUTE IMMEDIATE does not display the results of an sql select statement.
  • You are calling your execute script as @test.sql TYPE which will be parsed as EXECUTE IMMEDIATE build_select(TYPE) i.e plain TYPE without quotes - which will throw an error. You can resolve this however by running it as

@test.sql "'TYPE'"

As I said you cannot directly execute and view results from a dynamic SQL select statement in 11g and below. So you can use the technique used in the other answer which I am not clear about if it generates the select SQL neatly.

If you are in 12c, you can use something like 

open a_ref_cursor for build_select(&1);
dbms_sql.return_result(a_ref_cursor);

For 11g and below , here are some of the techniques explained.

How to output result of SELECT statement which is executed using native dynamic SQL?

A better solution if you are ok not to use your function would be

Generating SQL*Plus script using SQL*Plus

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
0

Wrong syntax... Maybe you could call the spool you just created?

Here is what could work:

SET HEAD OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET LINESIZE 32000
SET PAGESIZE 0
SET TERMOUT OFF
SET ECHO OFF
SET COLSEP ''

spool /a/b/rvibap/c/&1..sql

prompt SET COLSEP ,
select build_select(&1) from dual;

spool off;

spool /a/b/rvibap/c/&1..csv

@/a/b/rvibap/c/&1

spool off;

Hope it helps.

J. Chomel
  • 8,193
  • 15
  • 41
  • 69
  • The solution does not work : PLS-00221: 'BUILD_SELECT' is not a procedure or is undefined. The Build_Select is a function and I want the SQLPLUS script execute the SQL request Build_select is returning and to spool the data in a CSV file. – refresh Oct 13 '17 at 12:16
  • Here is another proposal: `select your_function from dual` might work. It depends on the content of your function. I edited. – J. Chomel Oct 13 '17 at 12:26
  • Yes it works. But display only the select statement in the CSV file. I want it to execute the select and spool the results in the CSV. In the CSV it display 'select * from TYPE;'. I want it to execute the select query. – refresh Oct 13 '17 at 12:39