Solved

How to execute an Oracle Package

Posted on 2008-10-21
9
870 Views
Last Modified: 2013-12-18
Here's the Oracle package . . .

f_costAtCompl(as_jobId, as_costTypeCd, as_cc {, adt_endDate, adt_maxCreationDate})

I want to know the syntax of the SQL line to execute this package with the following variables:

as_JobId = 1012802
as_costTypeCd = M
as_cc = 445303
adt_endDate = 12/31/2009
adt_maxCreationDate = 01/02/2007

The first three variables are string; the last two are date.

Thanks!

The
0
Comment
Question by:glennes
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22769182
exec Your_package_name.f_costAtCompl(as_jobId => '1012802'
                                 , as_costTypeCd => 'M'
                                 , as_cc  => '445303'
                                 , adt_endDate => '12/31/2009'
                                 , adt_maxCreationDate => '01/02/2007'
                         )

0
 

Author Comment

by:glennes
ID: 22769850
When I run this, I get the following error:

PLS-00302: component 'F_COSTATCOMPL' must be declared

How do I modify the script to declare F_COSTATCOMPL ?
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 250 total points
ID: 22770250
the procedure has to be added into the package specification.



CREATE OR REPLACE PACKAGE your_package_name
is
 
   procedure F_COSTATCOMPL
   ( 
-- as_jobId, as_costTypeCd, as_cc {, adt_endDate, adt_maxCreationDate}
   );
END your_package_name;
 
 
CREATE OR REPLACE PACKAGE BODY
your_package_name
is
 
CREATE OR REPLACE PACKAGE your_package_name
is
 
   procedure F_COSTATCOMPL
   ( 
-- as_jobId, as_costTypeCd, as_cc {, adt_endDate, adt_maxCreationDate}
   )
   IS
   BEGIN
   -- code here
   END;
END;

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 27

Expert Comment

by:sujith80
ID: 22773904
From sql prompt execute the following command to see whether you have the above mentioned procedure available in your schema.

desc f_costAtCompl

If it says "object does not exist"; then you need to find the correct package/procedure name and make sure that it is compiled in your schema.

After that you can use the "exec" commmand as indicated in the above post to execute your code.
But I suggest to use  "to_date" for the date values.
0
 

Author Comment

by:glennes
ID: 22775262
When I run it now, I get this SQL error:

A PL/SQL Parse error occurred.
Please check the all_errors table for errors.

Also, when I execute the command
desc f_costAtCmpl
I get this error: Couldn't resolve object (Not local and not public synonym.  Needs owner?)
  "desc f_costAtCmpl"
When the error appears, the word "desc" in the command is highlighted.

Before I ran jamesqu's script, the package pk_jobForecasts had several "f_" procedures listed under it. After I ran it, all these are gone. Perhaps the CREATE OR REPLACE command deleted them all - not sure. I checked our live database in Oracle and the procedures listed under this package name are still present. But, the "desc f_costAtCmpl" command returns the same error when run under our live database.

Can you suggest something else I should try doing? There are a number of packages/procedures that I need to use which are similar to this one, so I need to make this work. I've increased the points to 500. Your help is greatly appreciated!

This is running using Oracle SQL. Is there anything about Oracle SQL syntax that differs from the scripts you guys have suggested?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22775302
run the following command and post the results.

select user, owner, object_type, object_name
from all_objects
where object_name = upper('f_costAtCmpl');
0
 

Author Comment

by:glennes
ID: 22775842
The result set is "NO RECORDS"
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 22778526
That means the package 'f_costAtCmpl you are trying to execute is not present in the scope of the system. Consult somebody who knows the application and seek their help to have it created in your system.
A forum like this can help you to fix technical issues, But its quite difficult to guess what could be missing in your environment in cases like this.
0
 

Author Comment

by:glennes
ID: 22811308
For those who may be looking for the same thing I was seeking, here is the answer I was looking for:

 SELECT pk_jobForecasts.f_costAtCmpl ("JOB"."JOB_ID",'%','%')
 FROM   "PRODN"."JOB" "JOB"
where job_id = '1012802'

In the script above, PRODN is the name of the Oracle server and 1012802 is a particular JOB_ID in the JOB table. Running this script will return the f_costAtCompl for JOB_ID 1012802 by firing the f_costAtCompl function as it exists in the Oracle package pk_jobForecasts. Quite simple, indeed. Works perfectly.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to send multiple emails at the same time in PHP 12 57
ODBC in excel 2016 in Windows 10 via VBA 16 60
Query Syntax 17 32
convert null in sql server 12 31
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

786 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