Updateing Package Body with sqlplus on linux

Hi Guys,

I have created a Package on a test server using oracle 10g em.
I'd like to move to update an existing package on a linux server with this new code.

Therefore I opened it in EM, listed the sql code EM would execute in order to update in on its server and pasted it into the sqlplus command line, but it only shows up weird results.

The code is something link:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME IS
-- PL/SQL Block
/* Constant Declaration */
.......
END PACKAGENAME;


Please also let me know how I can check if the code was updated correctly.

Best,
jp
SWB-ConsultingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrSQLCommented:
jp,
  If you already have EM open, you can use it to cut/paste the package and package body (do them seperately).  You can also use EM to check the status.  Just click on the schema/<your schema>/source objects/packages and the same for package_body to see status.

Good luck!
DrSQL
SWB-ConsultingAuthor Commented:
Thanks for your answer!

But this does not solve the issue: I have forgot to mention, that EM is not available on the the target system.

I have created the code on an EM development server, but want to move it to a server without EM.

Best,
jp
DrSQLCommented:
jp,

    Then, in sql*plus:

set linesize 100
select line,text from user_source where name='<your package>'
group by type order by line;

That will show you the source code.  And, the status can be found in:

select object_name,status from user_objects where object_type='PACKAGE' and object_name='<your package>';

Just be sure to enter the name in uppercase.

Good luck!
DrSQL
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

DrSQLCommented:
jp,

    Then, in sql*plus:

set linesize 100
select line,text from user_source where name='<your package>'
group by type order by line;

That will show you the source code.  And, the status can be found in:

select object_name,status from user_objects where object_type='PACKAGE' and object_name='<your package>';

Just be sure to enter the name in uppercase.

Good luck!
DrSQL
SWB-ConsultingAuthor Commented:
Guys,

sorry to ask again.
But the main issue is how to get the package body code updated on the linux server.

I have my code that comes from the EM has been altered there. I can view the sql statement which is used by EM to store my changes in the database. BUT running this SQL statement on the linux sqlplus command line tool only shows weired numbers like 1 2 3 4 5 6 ... 378 379 ... for some reason I have the feeling that the statment is executed rather than stored in the db.

Best,
jp
DrSQLCommented:
jp,
   It is loading it into a buffer.  Try entering:

set echo on
set termout on
set verify on
set showmode on


before you paste in the package.  When it is done, you may need to enter a "/".  If you enter an "L" at the SQL> prompt you should see your code in the buffer.

Good luck!
DrSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Instead of pasting it straight into SQL*Plus, paste it in a text file and execute the file in SQL*plus.

i.e./
paste the SQL in a file called junk.sql then from the sql*plus prompt:

SQL> @<proper_directory>/junk
SWB-ConsultingAuthor Commented:
@Slightwv:

Thanks for your comment, will consider it next time!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.