0

I am using Oracle 19c. I created a procedure and use it in a sql script file and call the procedure from shell script. There are variables which need to be substituted from the shell script to procedure.

The script execute with "PL/SQL procedure successfully completed." but it does not gather stats without printing the message from DBMS_OUTPUT.

Procedure :

CREATE OR REPLACE PROCEDURE gather_schema_stats_proc (
   p_schema_name IN VARCHAR2  
) IS
BEGIN
   -- Print message before gathering stats
   DBMS_OUTPUT.PUT_LINE('Gathering statistics for schema ' || p_schema_name || '...');
   
   DBMS_STATS.GATHER_SCHEMA_STATS (
      ownname => p_schema_name,  
      cascade => TRUE             
   );
   
   -- Print message after gathering stats
   DBMS_OUTPUT.PUT_LINE('Statistics gathering for schema ' || p_schema_name || ' completed.');
END gather_schema_stats_proc;
/

SQL Script : gstat.sql

SET SERVEROUTPUT ON
DEFINE schema='$owner' --variable to be substituted
EXEC gather_schema_stats_proc('&schema');

Shell script :

#!/bin/bash
owner="SCOTT"    #variable for substitution 
export ORACLE_SID=abcdb
export ORACLE_HOME=/u01/app/oracle/product/db_home
exit|sqlplus -S '/ as sysdba' @gstat.sql

Thanks in advance

3
  • What is exit| for? Commented Mar 19 at 6:43
  • To exit connection of sqlplus when executed from shell script. Commented Mar 19 at 6:54
  • 1
    As per the linked duplicate, change the SQL script to: SET SERVEROUTPUT ON EXEC gather_schema_stats_proc('&1'); and then change the bash script to use: sqlplus -S '/ as sysdba' @gstat.sql "${owner}" to pass the shell variable into SQL*Plus as a substitution variable and then to use the substitution variable in the procedure call. Commented Mar 19 at 9:03

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.