Solved

How to execute an Oracle Package

Posted on 2008-10-21
9
877 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:Glenn Stearns
[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
  • 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:Glenn Stearns
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
Revamp Your Training Process

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

 
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:Glenn Stearns
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:Glenn Stearns
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:Glenn Stearns
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

630 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