Quester about PL/SQL using SELECT between BEGIN END

Question

I am junior oracle dba and have a question about PL/SQL. May you give me some advice please? I would like to know that is it possible to put SELECT query into BEGIN ... END with SPOOL. The purpose of this code is using array content to be the source of SELECT FROM. Thank you very much for your kind help. Here is my shell script with PL/SQL code and it's not working:

#! /bin/bash
$ORACLE_HOME/bin/sqlplus "sysmonitor/********" << EOF

declare
  type array_t is table of varchar2(30);
  array array_t := array_t(
    'DBUSER_UNEW_TABLE1',
    'DBUSER_UNEW_TABLE2',
    'DBUSER_UNEW_TABLE3'
  );
begin
  for i in 1..array.count loop
    spool test.log
      select * from array(i);
    spool off;
  end loop;
end;
/

EOF

After reading the suggestion from the nice people as below, I made some change as below. However, I still don't know how to spool each result in a single file after looping SELECT the tables (DBUSER_UNEW_TABLE1,DBUSER_UNEW_TABLE2,DBUSER_UNEW_TABLE3). May you give me some advice please?

$ORACLE_HOME/bin/sqlplus "sysmonitor/********" << EOF

declare
  type array_t is table of varchar2(30);
  array array_t := array_t(
    'DBUSER_UNEW_TABLE1',
    'DBUSER_UNEW_TABLE2',
    'DBUSER_UNEW_TABLE3'
  );

begin
  for i IN 1 .. array.count
  loop
    select * from array(i);
  end loop;
end;

spool test.log
  select * from array(i);
spool off
exit
EOF

If I run the sql code under sqlplus command line, it returs error. select * from array(i); * ERROR at line 11: ORA-06550: line 11, column 20: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 11, column 1: PL/SQL: SQL Statement ignored

declare
type array_t is table of varchar2(30);
array array_t := array_t(
'DBUSER_UNEW_TABLE1',
'DBUSER_UNEW_TABLE2',
'DBUSER_UNEW_TABLE3'
);
begin
for i IN 1 .. array.count
loop
select * from array(i);
end loop;
end;
/

Spool is an SQL*Plus command, so you need to put it outside the anonymous PL/SQL block. Also, SPOOL will redirect output, so you need to print the result of the SELECT. See the answer to this question: http://stackoverflow.com/questions/8590826/how-to-create-a-oracle-sql-scrpt-spool-file — Klas Lindbäck, Mar 12 '14 at 09:51
@KlasLindbäck Thank you so much ~ :D I start to check it now ~ — Lester, Mar 12 '14 at 12:11
Excuse me... I guess I got new problem. After looping SELECT the tables (DBUSER_UNEW_TABLE1,DBUSER_UNEW_TABLE2,DBUSER_UNEW_TABLE3), how do I spool each result in a single file? I tried this as below but it's not working... $ORACLE_HOME/bin/sqlplus "sysmon/systex" << EOF declare type array_t is table of varchar2(30); array array_t := array_t( 'DBUSER_UNEW_TABLE1', 'DBUSER_UNEW_TABLE2', 'DBUSER_UNEW_TABLE3' ); begin for i IN 1 .. array.count loop select * from array(i); end loop; end; spool test.log select * from array(i); spool off exit EOF — Lester, Mar 12 '14 at 12:48

score 0 · Answer 1 · answered Mar 12 '14 at 08:45

0

Spool is SQL command. It cannot be used inside annonymous PL/SQL block.

answered Mar 12 '14 at 08:45

contravaluebets

85
1
8

Thank you very much for your advice! ~ :D per the advice, can I do that as below? spool test.log begin for i in 1..array.count loop select * from array(i); end loop; end; spool off; / – Lester Mar 12 '14 at 12:02
If you think this answer has answered your question, then please check the checkbox near to the post. – contravaluebets Mar 12 '14 at 12:52

Quester about PL/SQL using SELECT between BEGIN END

1 Answers1