SQL & PL/SQL :: Create Or Replace - Package Body Created With Compilation Errors

Mar 8, 2012

CREATE OR REPLACE PACKAGE pkg_mkt_hub_load_collection
AS
PROCEDURE sp_final_load_mkt_hub;
END pkg_mkt_hub_load_collection;
/
CREATE OR REPLACE PACKAGE BODY pkg_mkt_hub_load_collection
AS
c_default_limit CONSTANT PLS_INTEGER:=5000;

[code]....

show error

error code

SQL> @pkg_mkt_hub_load_collection.sql
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY PKG_MKT_HUB_LOAD_COLLECTION:
LINE/COL ERROR
-------- -----------------------------------------------------------------
57/4 PL/SQL: Statement ignored
PLS-00306: wrong number or types of arguments in call to 'MULTISET_INTERSECT_ALL'
SQL>

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Package Body Created With Compilation Errors (Warnings On)

Aug 6, 2013

I've compiled a package with warnings ON. Getting message SP2-0809: Package Body Created with Compilation Errors.When I checked the errors by issuing "Show Errors", it shows "No error".

SQL> ALTER PACKAGE PKG_ABC COMPILE PLSQL_WARNINGS = 'ENABLE:ALL';
SP2-0809: Package Body Created with Compilation Errors
SQL> SHOW ERRORS
No Errors

how to check errors for that, or oracle simply pointing that package might have performance issue.

View 8 Replies View Related

Trigger Created With Compilation Errors

Dec 13, 2010

I've build a simple trigger like this:

CREATE or REPLACE TRIGGER my_trigger
BEFORE UPDATE OF PASSWORD ON mytable
FOR EACH ROW
DECLARE
BEGIN
:NEW.PASSORD := 'xyzt';
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Error '|| sqlerrm);
END trigger_create_user;

But, when i execute it, i get the following message:Warning: Trigger created with compilation errors.Even if i do nothing in trigger body:

CREATE or REPLACE TRIGGER my_trigger
BEFORE UPDATE OF PASSWORD ON mytable
FOR EACH ROW
DECLARE
BEGIN
END trigger_create_user;

I still get that message.

View 7 Replies View Related

SQL & PL/SQL :: View Created With Compilation Errors / ORA-01775 - Looping Chain Of Synonyms

Jun 21, 2011

I created a view in DB2 .I copied the code of view from DB3 and created in DB1. The view got created but with compilation errors.

SQL>
SQL> CREATE OR REPLACE FORCE VIEW bala.cee_efacts_pie_order_v (cenumber,
2 installationid,
3 areanumber,
4 clli,
5 servicedate,
6 forecasttype,
7 jobid,
8 shippriority,
[code]....

I checked the status of view in user_objects , it was invalid.How to make it valid ?

View 11 Replies View Related

Server Utilities :: ORA-04063 - Package Body Has Errors

Jan 30, 2013

I am getting the below error when i am trying to import the dmp file into my oracle 11g.

impdp system/password@orcl dumpfile=aaa.dmp directory=datapump remap_schema=dev:user

ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_
LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 949
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"

View 7 Replies View Related

SQL & PL/SQL :: Difference Between Include Program Header Before CREATE OR REPLACE PACKAGE Statement

Mar 2, 2010

Is there any difference between include program header before CREATE OR REPLACE PACKAGE statement and program header after CREATE OR REPLACE PACKAGE statement

View 4 Replies View Related

SQL & PL/SQL :: Saving Procedures With Compilation Errors

Feb 21, 2010

When creating objects, is there a way telling Oracle not to store(overwrite) procedures or other DDL in the database until the statements have no compilation errors?

View 4 Replies View Related

Client Tools :: Compilation Errors In Toad

May 15, 2008

I know that this my question is a little to much for some DB tool like TOAD, but maybe this tip is exist and I can't find it!

Does TOAD when I write something wrong in PL/SQL (trigger for example) and after compilation (run as script) when show message:

"Warning: compiled but with compilation errors" to show what is wrong in sql code? I found this option is some db tools, but I can't find this in TOAD, of course if it can do this!

View 4 Replies View Related

PL/SQL :: Creating A Package Body

Jan 6, 2013

I'm trying to create a package body but i keep getting an error message saying package created with complication.

CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......

View 2 Replies View Related

Server Utilities :: IMP-00041 / Object Created With Compilation Warnings

Sep 1, 2010

Every quarterly, we export the data from DEEXTRA and import into INEXTRA user.This is not working after i made the changes 2 tables and views in DEEXTRA .

I added new columns to 2 tables and associated 2 views. After this change, my import process got failed, with message especially for those views. "IMP-00041: Warning: object created with compilation warnings"

before i change the tables, import process was working fine. My doubt is, views were created in INEXTRA before the tables in import functionality.

I had given the grants similary the other objects. I belive no problems with privilages.

Because of table changes the order of the objects in exporting got disturbed? like in the exporting functionality first views created then the table?or the order of the objects in importing got disturbed? like first views created and then tables?

View 5 Replies View Related

SQL & PL/SQL :: Package Compilation Error

Jan 4, 2012

when am trying to compile package, am getting the below error am not understanding whats that exactly ,

PLS-00103: Encountered the symbol "FUNCTION" when expecting one of the following:

end not pragma final instantiable order overriding static
member constructor map

View 3 Replies View Related

SQL & PL/SQL :: Compiling Package BODY Causes Invalidation?

Aug 25, 2010

Up until today we assumed that compiling a package BODY-only would not cause invalidation of other code. However when we compiled the body of the HTP package, the OWA package became invalid and required re-compilation.

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

View 6 Replies View Related

SQL & PL/SQL :: How To See The Contents Of Wrapped Package Body

Mar 31, 2011

I want to see a stored procedure definition which is inside the body of a package and the package is wrapped.So I am not able to see the content of the package body.I am new to oracle and I don't know how to decrypt the wrapped package.how to read the content of a wrapped package inside oracle.

View 1 Replies View Related

SQL & PL/SQL :: Error While Compiling Package Body?

Jun 28, 2011

When i compile the package body i get errors.

CREATE OR REPLACE PACKAGE BODY CEE_OSPCM_PKG AS
PROCEDURE CEEOT_WORK_TYPE_PRC IS
CURSOR CUR_WORK_TYPE IS
SELECT *

[code]...

View 7 Replies View Related

SQL & PL/SQL :: Missing UTL_SMTP Package Body?

Mar 5, 2012

I tried to create a UTL_SMTP package using '@?/rdbms/admin/utlmtp.sql' script but there are no package body for UTL_SMTP is created. There are only UTL_SMTP package is create.

View 4 Replies View Related

Server Utilities :: IMP-00041 - Warning - Object Created With Compilation Warnings?

Jun 18, 2010

For the export and import I am doing teh following steps:

1.Export the tablse space from the test machine where the whole stack is created

exp system/password@orcl file="data/OracleDump/arsystem.dmp" OWNER="ARADMIN";

2.Following steps to import: These steps followed by installer before it imports the tablespace on the destination machine

•CREATE TEMPORARY TABLESPACE ARTMPSPC TEMPFILE '/data/ORACLE/DATABASES/ORCL/artmp' SIZE 250M REUSE AUTOEXTEND ON
•CREATE TABLESPACE ARSYSTEM DATAFILE '/data/ORACLE/DATABASES/ORCL/ARSys' SIZE 7000M REUSE AUTOEXTEND ON

[code]...

3. Import the tablespace on a different box

imp system/password@orcl1 file="/dump/arsystem.dmp" buffer=1024000 fromuser=aradmin touser=aradmin feedback=1000000 log="dump/arsystem.log"

I could see in teh log lot of IMP-00041: Warning: object created with compilation warnings

example:
IMP-00041: Warning: object created with compilation warnings
"CREATE FORCE VIEW "ARADMIN"."BMC_CORE_BMC_IMPACT" ("
""REQUESTID","SUBMITTER","CREATEDATE","ASSIGNEDTO","LASTMODIFIEDBY","MODIFIE"
"DDATE","STATUS","SHORTDESCRIPTION","CMDBROWLEVELSECURITY","INSTANCEID","CMD"

[code]...

View 8 Replies View Related

SQL & PL/SQL :: How To Export Package Body Using Spool Command

Jan 8, 2013

I am trying to Spool a Package Body into a file using sqlplus. I tried 2 days:

WAY1
SET LINESIZE 32000;
SET PAGESIZE 0;
SET TRIMSPOOL ON;
SET ECHO OFF;
SET TERM OFF;
SET FEEDBACK OFF;

[code]....

WAY2
.....
SELECT dbms_metadata.get_ddl( 'PACKAGE', 'SPI_xxxx_PCK', 'owner') FROM dual;
......

In Way1, I got different DDL or partial DDL. In Way2, I got only first 2 lines of Package body. I am running this command from Schema user only.

View 7 Replies View Related

Forms :: Error While Compiling Package Body

Sep 3, 2010

while compiling this package body, i get error

error 102 at line 21 column 11
encounterd symbol "Group" when expecting one of following

begin function package pragma procedure subtype type use form cursor

View 5 Replies View Related

PL/SQL :: Grant Command To View Package Body

Aug 3, 2012

I sent a request for the DBA's to grant me rights to the package body but I only see the spec with the grant execute command.

Here is the command I just sent:

grant create any procedure to DEVELOPER1;

But the DBA changed my grant command to:

grant create procedure to DEVELOPER1;

But I still cannot see the package body. This is on a test environment.

Would the "create any procedure" command see the package body of different schema owners package bodies?

Or is there another grant command to see different schema package bodies?

View 4 Replies View Related

Client Tools :: How To See Specs / Body Of Package Procedures

Jan 27, 2011

1- I do not have access to TOAD for Oracle yet
2- I can connect to SQLPLUS: Release 9.2.0.1.0
3- We have many stored procedures in packages that are available from the TOAD for Oracle interface
4- I need to be able to see the specs/body of some packages containing some procedures.
5- I am connected to the appropriate DataBase1 (for example), but from here what to do from SQLPLUS command prompt ?

For example: SchemaName1.PackageName1.ProcedureName1

View 39 Replies View Related

SQL & PL/SQL :: How To Declare Global Variable In Package Body (Not Spec)

Feb 26, 2013

I want to know how we can declare a Global Variable in Package body(Not Spec), So that i can use it in any procedures or function(Defined in same package).

View 11 Replies View Related

SQL & PL/SQL :: Hide Declaration Or Declare It Inside The Body Of Package

Mar 11, 2010

I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...

So I declared all these in the header

Like this

CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......

Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?

View 9 Replies View Related

SQL & PL/SQL :: Function That Returns A Table Type Inside A Package Body

Jul 26, 2011

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....

I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?

I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?

View 10 Replies View Related

Server Utilities :: Impdp - Package Body Import Taking Huge Time?

Sep 13, 2012

I am try to import 4G dump in Oracle 11R2 version, in that we have around 9000+ Package Body which is taking huge time than other objects (about 8 to 12 hrs) and also it is expecting lots of system space (roughly about 10GB).

I have tried both parallel and non-parallel.how to improve speed of the package body import.

Details about the Schema & Import No. of objects in Schema

SQL> select object_type,count(1) from user_objects GROUP BY ROLLUP( object_type);

OBJECT_TYPE COUNT(1)
------------------- ----------
FUNCTION 248
INDEX 5161
JAVA CLASS 471
JAVA RESOURCE 1
JAVA SOURCE 16
LIBRARY 1

ORA-00933: SQL command not properly ended

View 3 Replies View Related

SQL & PL/SQL :: Create Oracle Objects (Views) Using Conditional Compilation?

Jun 6, 2011

it is possible to create Oracle objects(Views) using Conditional compilation?

View 5 Replies View Related

PL/SQL :: Create Or Replace View

Sep 11, 2012

I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.

View 3 Replies View Related

SQL & PL/SQL :: Trigger Insert - Create Or Replace?

Jun 12, 2013

I have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges

I have that code, is that correct?

CREATE OR REPLACE
TRIGGER TR27
AFTER INSERT ON JOBS FOR EACH ROW
BEGIN
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80);
:NEW.MAX_SALARY := :OLD.MAX_SALARY;
END;

What I need to complete it?

View 2 Replies View Related

SQL & PL/SQL :: CREATE OR REPLACE - Error During Execution Of Trigger

Aug 9, 2011

I create this trigger:

CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
INSERT INTO HST (EMP_NO, START_DATE )
SELECT EMP_NO, SYSDATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/

when I insert a new record in table HST I get this error:

ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'

What I wrong in this trigger?How can I modify it?

View 7 Replies View Related

SQL & PL/SQL :: Trigger To Execute Immediate Create Or Replace View?

Jan 30, 2013

i try to make trigger to execute immediate create or replace view

(
CREATE OR REPLACE TRIGGER xxxx
BEFORE INSERT ON table1
REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW
declare
l number ;
v_ddl varchar2(4000);
v_job number;
[code]....

it give me error for insuffition privilage in execute immediate and after i make GRANT CREATE ANY TRIGGER TO user give me error can't commit in trigger.

View 8 Replies View Related

SQL & PL/SQL :: Bind Variables - Create Or Replace Procedure

Jun 12, 2012

create or replace procedure my_proc(p_user in varchar2) is
l_cursor sys_refcursor;
l_query constant varchar2(1000) :=
'select a'
|| 'from ' || p_user || '.user_table'
|| 'where param0 = :x'
|| 'and param1 = :x'
|| 'and param2 = :x'

[Code]...

Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved