Solved

Call a procedure on command line

Posted on 2014-11-24
8
178 Views
Last Modified: 2014-12-24
I have the below procedure. how can i call it on the command line with sqlplus
0
Comment
Question by:gaurav sharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 

Author Comment

by:gaurav sharma
ID: 40463106
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;
/
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 40463161
From SQL*Plus, it would be:

exec recomp;
0
 

Author Comment

by:gaurav sharma
ID: 40463173
If I were to put this call in a file would it be .sql ? and what would be the contents of a file ?
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 35

Expert Comment

by:johnsone
ID: 40463242
It would be one line:

exec recomp;

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

@<path_to_script>/<script_name>
0
 

Author Comment

by:gaurav sharma
ID: 40463455
This is the error I get:

 Failed to execute:  EXEC RECOMP
java.sql.SQLException: ORA-00900: invalid SQL statement
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 250 total points
ID: 40464155
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 40464238
You stated multiple times that you were running from SQL*Plus, not java.

If you are really running from Java and not SQL*Plus, then use the documentation link provided by Alexander.
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 250 total points
ID: 40464310
I agree with johnsone: please clarify whether you run this within SQLPLUS or Java! Otherwise we might not be able to help you and this could get quite confusing!

I assumed, you got this error upon a Java execution based on the Java exception:
java.sql.SQLException: ORA-00900: invalid SQL statement
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question