Avatar of gaurav sharma
gaurav sharma
 asked on

Call a procedure on command line

I have the below procedure. how can i call it on the command line with sqlplus
Oracle Database

Avatar of undefined
Last Comment
Alex [***Alex140181***]

8/22/2022 - Mon
gaurav sharma

ASKER
CREATE OR REPLACE PROCEDURE recomp IS
BEGIN
   FOR cur IN (SELECT object_name,
                      object_type,
                      owner
               FROM   all_objects
               WHERE  object_type IN ('PACKAGE',
                                      'PACKAGE BODY')
               AND    owner = 'TRON2000'
               --                 AND object_name like 'EM%'
               ORDER  BY object_name)
   LOOP
      BEGIN
         IF cur.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE 'alter package "' || cur.owner || '"."' ||
                              cur.object_name || '" compile body';
         ELSE
            EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner ||
                              '"."' || cur.object_name || '" compile';
         END IF;
      EXCEPTION
         WHEN OTHERS THEN
            NULL;
      END;
      --dbms_output.put_line('Recompiled: ' || cur.OBJECT_TYPE || ' ' ||
   --                   cur.owner || '.' || cur.OBJECT_NAME);
   END LOOP;
END;
/
SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
gaurav sharma

ASKER
If I were to put this call in a file would it be .sql ? and what would be the contents of a file ?
johnsone

It would be one line:

exec recomp;

Then from within SQL*Plus, you would run that using

@<path_to_script>/<script_name>
Your help has saved me hundreds of hours of internet surfing.
fblack61
gaurav sharma

ASKER
This is the error I get:

 Failed to execute:  EXEC RECOMP
java.sql.SQLException: ORA-00900: invalid SQL statement
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.