1

Topic: Dynamic SQL in the batch

Greetings children.
Prompt how to launch please dynamic SQL from under a packet and that the result was produced in a file?
That that like

SPOOL RESULTS.TXT
L_SQL VARCHAR (2000);
L_SQL: = ' select * from TABLE1 ';
EXECUTE IMMEDIATE L_SQL;
SPOOL OFF

Where here at me an error?

2

Re: Dynamic SQL in the batch

Edgar wrote:

where here at me an error?

In attitude.
To begin with answer itself on :
- Where the packet is allocated?
- Where the file should be generated?
- Why a packet instead of a script?

3

Re: Dynamic SQL in the batch

Edgar, that you resulted - is better at all would not result. In your case as you ask questions, it should look here so:
"That that like":

begin
Abrupt general-purpose piece of the code.
end;

4

Re: Dynamic SQL in the batch

andrey_anonymous;
Thanks for the answer.
I should launch a script from under Windows by means of the utility sqlplus which would pour out results in a file on client side - Windonws. The packet batch.sql also is allocated on client side.
C:\sqlplus user/pass@DB @batch.sql
In batch.sql to me it is necessary to hammer dynamic SQL. Dynamics in usage of current date.

5

Re: Dynamic SQL in the batch

Edgar;

Edgar wrote:

Dynamics in usage of current date.

where dt = sysdate

6

Re: Dynamic SQL in the batch

env;
Thanks. The matter is that current date will form a name of the table from which sampling goes. So the dynamic request is really necessary to me.

L_SQL: = ' select * from DETAIL _ ' || to_char (sysdate, ' YYYYMMDD '); ';

7

Re: Dynamic SQL in the batch

In.bat:
C:\sqlplus user/pass@DB @batch.sql %date %> output.txt
%date % - system date
> output.txt - an output in a file output.txt

8

Re: Dynamic SQL in the batch

Edgar wrote:

Greetings children.
Prompt how to launch please dynamic SQL from under a packet and that the result was produced in a file?
That that like

SPOOL RESULTS.TXT
L_SQL VARCHAR (2000);
L_SQL: = ' select * from TABLE1 ';
EXECUTE IMMEDIATE L_SQL;
SPOOL OFF

Where here at me an error?

As delirium
To tire out request (?) in pipelined function, in a script  from it
It allows to use formatting means sql-plus at an output

9

Re: Dynamic SQL in the batch

Edgar wrote:

  that the dynamic request is really necessary to me.

substitution variables

10

Re: Dynamic SQL in the batch

Edgar;
https://oracle-base.com/articles/9i/gen … -csv-files
Section PL/SQL,  only to find a method to receive files from the server... smile

11

Re: Dynamic SQL in the batch

Edgar wrote:

the matter is that current date will form a name of the table from which sampling goes. so the dynamic request is really necessary to me.

you need to study the tool.
RTFM Using Substitution Variables (FAQ)
[spoiler]

SQL> column dt_ new_value dt noprint
SQL> set feed off
SQL> select to_char (sysdate, ' yyyymmdd ') as dt_ from dual;
SQL> select * from detail_&dt;
select * from detail_20170908
*
ERROR at line 1:
ORA-00942: table or view does not exist

[/spoiler]

12

Re: Dynamic SQL in the batch

The script text:

accept table_name prompt ' Table-Name:'
SPOOL RESULTS.TXT
select * from &table_name where rownum <4;
SPOOL OFF

Result:

SQL> @D:\SQL\dyn_tabname.sql
Table-Name: dual
old 1: select * from &table_name where rownum <4
new 1: select * from dual where rownum <4
D
-
X
SQL>

13

Re: Dynamic SQL in the batch

Elic;
Many thanks for council. You helped me.
Prompt how to suppress please an output following lines in the spooling beginning?

 old 1: select * from detail_&dt where 1 <> 1
new 1: select * detail_20170908 where 1 <> 1 

C

14

Re: Dynamic SQL in the batch

Edgar wrote:

how to suppress an output

RTFM set ver

15

Re: Dynamic SQL in the batch

Elic;
Thanks, found

 set verify off