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
Solved

How to execute an Oracle Package

Posted on 2008-10-21
9
871 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

829 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