Solved

How to execute an Oracle Package

Posted on 2008-10-21
9
867 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 27

Expert Comment

by:sujith80
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:glennes
Comment Utility
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
Comment Utility
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
Comment Utility
The result set is "NO RECORDS"
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now