Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to execute an Oracle Package

Posted on 2008-10-21
9
Medium Priority
?
884 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
  • 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 1000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 1000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

963 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