Using Dbms_rectifier_diff Package

Apr 27, 2007

I ran the dbms_rectifier_diff.differences package successfully just to know the differences after it i droppped the all tables from the schema but now I have some funny table names exist in my schema like BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0. I want to remove it but dont know how, when I run the following command on sql prompt i get error message says 'sql command not properly ended'.

SQL>drop table BIN$SHabcAN0slsAAdjhf0/ABQCiA==$0;

View 1 Replies


ADVERTISEMENT

PL/SQL :: Find Out In Database Package Where Package Is Installed

May 28, 2013

I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').

What I would need is something like $$PLSQL_UNIT

View 8 Replies View Related

SQL & PL/SQL :: Global Exception In Package Available Outside Package?

Jan 15, 2012

I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:

e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);

Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?

View 4 Replies View Related

SQL & PL/SQL :: Creation Of Package

Jan 12, 2011

Why Package has to be created as two parts

1) Package spec
2) Package Body ?
Quote:PLS-00304: cannot compile body of '<PACKAGE_NAME>' without its specification

I learned it as a Thumb of rule! But IF there is any rule for it give that URL/link for that.

View 25 Replies View Related

SQL & PL/SQL :: Two Different Package Can Contain Same Procedure?

Mar 31, 2011

is it possible that two different package can contain same procedure?

View 1 Replies View Related

SQL & PL/SQL :: Package To Calculate Pay

Oct 20, 2010

Trying to accomplish:

I am trying to calculate pay with a package which consists of four functions for calculations and a procedure that calls the functions to calculate net pay.

DML DDL and package

I the DML and DDL and the package as an attachment.

Problem

Errors below

32/9 PLS-00103: Encountered the symbol "E" when expecting one of the following:

, ; for group having intersect minus order start union where connect The symbol "having" was substituted for "E" to continue.

32/54 PLS-00103: Encountered the symbol ")" when expecting one of the following:

LINE/COL ERROR
-------- ----------------------------------------------------------------
. ( * @ % & - + ; / at for mod remainder rem <an exponent (**)> and or group having intersect minus order start union where connect || multiset

33/9 PLS-00103: Encountered the symbol "INTO" when expecting one of the following:

. ( ) , * @ % & = - + < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || member submultiset

View 39 Replies View Related

SQL & PL/SQL :: Package And Procedure?

Jul 17, 2012

In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?

View 14 Replies View Related

SQL & PL/SQL :: Cause Of Invalid Package?

Mar 8, 2012

There are 4 packages got invalid 2 days back. when I analyze the database I came to know that there are 5 tables got truncated and 2 tables got altered during the issue period through the code. Those truncated tables have the indirect relationship with these 4 packages.but there is no any relation between these packages and altered table.

Also during that time I got the below error in my alert log.I am sure the cause this error is the invalid packages.

ORA-00600: internal error code, arguments: [kkxprpic8], [], [], [], [], [], [], []

I know if any alteration happens in a table, the refrence package will be getting as invalid. Apart from this, is there anyother cause to bring the package into invalid status? How to proceed further to find the root cause of thses invalid package?

View 6 Replies View Related

SQL & PL/SQL :: Package For Web Service

Nov 22, 2011

We are working on a POC to build Web services from Oracle PL/SQL packages. For this we need to create a PL-SQL package having a stored procedure which will accept one input parameter and sends out data sets with multiple rows.

The type of the output parameter should be compatible with JDBC so as to publish the package as a web service. We tried using an Array and Ref cursor in PL/SQL but it didn't work with JDBC.

what type of PL/SQL output we should use here?

Our Environment : Oracle 10.2.0.3.0, Jdeveloper (to convert PL/SQL package into to Web Service) and Web Logic 11G server to deploy the services.

View 4 Replies View Related

SQL & PL/SQL :: How To Debug Package

Apr 20, 2013

I have written a package including 13 procedures to insert data into a temp table but i am getting duplicate rows in dat table how to track from which procedure i am getting duplicate rows.

step 1 : procedures are inserting data using some joins into a temp table.
step 2: from temp table data will get stored into a target table where if the data already exist it will update the target table else will insert the data.(its a procedure where i used merge).

as i have duplicate data on temp table i am not able to update data on target table.

View 6 Replies View Related

PL/SQL :: Run Package In TOAD?

Jun 11, 2012

I have created a PL/SQL package and complied it and I got a meaasge with no errors.But I dont know how do I run the package to check whether the data has been populated in tables or not.I want to know do I need to write any code for execution to see the output.If I need to write how can I do it in TOAD.

View 6 Replies View Related

SQL & PL/SQL :: How To Use DBMS_SQL Package

Apr 27, 2010

How to use DBMS_SQL package. I tried with the following procedure.

CREATE OR replace PROCEDURE Crt_tab_inst(tab_name VARCHAR2,
col1_name VARCHAR2,
col1_value VARCHAR2)
IS
cur BINARY_INTEGER := dbms_sql.open_cursor;
fdbk BINARY_INTEGER;
[code]........

But when Iam executing the procedure, it is throwing the below error. This is the error Iam getting:

SQL> EXEC crt_tab_inst('MYTAB','MYCOL','NAME1');
BEGIN crt_tab_inst('MYTAB','MYCOL','NAME1'); END;
*
ERROR at line 1:
ORA-00984: column not allowed here
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SCOTT.CRT_TAB_INST", line 21
ORA-06512: at line 1

View 10 Replies View Related

PL/SQL :: Lock On Package

Feb 14, 2013

I am unable to compile my package because of a lock being held on it . Let me provide the details for better clarity .

select * from dba_ddl_locks where session_id=111 and owner='RAHUL';

session_id  owner       name                type                           mode_held         mode_requested
111           RAHUL     RAHUL_PKG        BODY                          NULL              None
111           RAHUL     RAHUL_PKG     Table/Procedure/Type      NULL              NoneThe session details of the session id 111 is as below

select sid,serial#,user#,username,command,status,process,sql_exec_start from v$session where sid=122

sid          serial#          user#                   username                     command                             status                     process      SQL_EXEC_START
111          3558             222                         RAHUL                      47                                   KILLED                   4420            13-DEC-12 02.00.00 AM

The strange this is that we have tried killing this session and the status above even shows ' 'KILLED', stilll the dba_ddl_locks is showing the lock details on the package RAHUL_PKG and we are unable to compile the package (the locking session is running since 13-Dec-2012 ) and we are unable to kill that . Also , is it like if i have executed a package that is referencing some other package then we wont be able to compile the referenced package until and unless the dependent package has completed its execution ??

View 6 Replies View Related

Executing WHOLE Package (of Procedures) At Once?

Aug 30, 2007

I could execute a package for eg if i had a package with procedures related to statistics and i run them each night, could i just do an exec on the package and it would run all those procedures??

Its not possible but i could call each procedure from ONE procedure

View 2 Replies View Related

Call A Procedure From Another Package?

Dec 10, 2007

in a certain procedure I'm trying to call a procedure from another package in the same Schema. Package-name: Haku_Hops, procedure-name: veto and submitted is a parameter called opnum. So the following brings no problem:

Hops_Haku.veto(opnum);

But what if in the beginning of the package body I create the following variable: hak_pah := 'Hops_Haku.'; Is it then in anyway possible to call this other procedure through this variable, like e.g. hak_pah||veto(opnum);?

Until now I've only gotten error, even after declaring the variable in the declaration part.

View 6 Replies View Related

Executing Function In A Package?

Mar 24, 2008

I have created one function in the package.

function : Tier_wh
package : Order_DESC

Function defined as

function TIER_WH( message in out Xorder_desc)
...
...
end;

Xorder_desc is defined as

create or replace type Xorder_desc type
(order number(10),
location number(10),
wh varchar2(20)
);

how to execute this function which is defined in the package .

View 1 Replies View Related

Truncate Partition From PL/SQL Package

May 8, 2013

I have an Oracle Package with a procedure in which

package with a procedure in which there is a truncate partition, ALTER TABLE table_name TRUNCATE partition_name DROP STORAGE and the all is run with an EXECUTE IMMEDIATE 'alter table ...' .

The point is that the procedure in the package is started from another DB via DB Link (schema USER1) and doesn't work because of lack of privileges.

Instead, if the same procedure is started as a procedure, standalone, not in the package but from the same user (USER1) it works perfectly.

Don't understand why and which privileges must give to the user to run the procedure from inside the package.

View 3 Replies View Related

SQL & PL/SQL :: Compiling Package And Seeing Error

Jun 11, 2012

How to see the error Oracle is returning?

SQL> select owner, object_name, object_type from sys.dba_objects
2 where status = 'INVALID' order by owner;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
CRMAPP
CRM
PACKAGE BODY

SQL> alter package CRMAPP.CRM compile;

Warning: Package altered with compilation errors.

SQL> show errors;
No errors.

SQL> select owner, object_name, object_type from sys.dba_objects
2 where status = 'INVALID' order by owner;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
CRMAPP
CRM
PACKAGE BODY

View 2 Replies View Related

SQL & PL/SQL :: How To Write Package For Deletion

Apr 21, 2012

how to write the package for deletion?

View 1 Replies View Related

SQL & PL/SQL :: Running Procedure Within A Package?

May 24, 2011

I have a package which has two procedures in it.

The second of which was put in just as a test:

PROCEDURE DST_RPT_INVOICE_REPRINT(refCur OUT Dsti_Rpt_Init_Pkg.RC, param_locationid VARCHAR2,
param_companycode VARCHAR2, param_frominvdate DATE, param_toinvdate DATE, param_project VARCHAR2,
param_invtype VARCHAR2, param_printed NUMBER) AS.....
Dsti_Rpt_Init_Pkg.PRINT_OUTPUT(strSql);
END DST_RPT_INVOICE_REPRINT

All this has within is an SQL statement which is built up (using the string, 'strSql') How can I view the output of a refCur to check what the final strSql is?

View 3 Replies View Related

SQL & PL/SQL :: Can Use Htp Package In Oracle Function

May 14, 2010

can we use htp package in function?

something like

if event_id = p_event_id THEN
htp.tableRowOpen;
htp.tableData(htf.bold('Evnt'), 'RIGHT', cattributes=>'CLASS=bptext');
htp.tableRowClose;
end if;

View 4 Replies View Related

SQL & PL/SQL :: Information On Package Contents

Oct 25, 2012

I am developing some automated test packages for my PL/SQL Packaged code. Going forward I can code the test package in conjunction with the code but I have some historic packages that I would like to develop these test packages for.

To save time I would like to employ oracle data dictionary views in order to construct the framework for my test package. This includes using SQL to get a list of procedures / functions within the package in order to create the test procedures (spec and body). I can do this in a basic way using the user_procedure view with something like...

SELECT 'PROCEDURE test_' || LOWER(procedure_name)
|| ' (p_result OUT VARCHAR2 IS BEGIN JTA.ACCOUNT_PROFILE_MAINT.' || procedure_name || ' END '
|| LOWER(procedure_name) || ';'
FROM user_procedures WHERE object_name = 'ACCOUNT_PROFILE_MAINT' AND subprogram_id != 0 ORDER BY subprogram_id;

However, the above only really works (in simplistic form.. without parameters) for procedures within the package. I would also like to be able to determine if the procedure listed is actually a function or procedure (so that I can alter the syntax accordingly to generate a correctly formatted string calling the program unit).

So, initially how do I determine the type of package program unit I have (Proc/Function)? Do I need to go to all_source to get this information or are there other views available I can join to?

Eventually I would like to extend this to be able to automatically include any parameters in the generated calling string.. again, is there any other option apart from all_source to get this information?

View 3 Replies View Related

Pl/sql Package For Giving Grants?

Mar 30, 2013

I am looking for a pl sql package which can give the following grants:

1. select_catalog_role
2 select any table

we want to run this package multiple names and each time we will give username as input during runtime.

View 1 Replies View Related

SQL & PL/SQL :: How To Use Ref Cursor In A Package Or In A Function

Sep 20, 2012

what is ref cursor?

How to use ref cursor in a package or in a function?

View 7 Replies View Related

SQL & PL/SQL :: Renaming A File From Package?

Sep 20, 2011

I have another question to ask on packages..

I have a dictionary under the schema

OWNERSYS
DIRECTORY_NAMEUTL
DIRECTORY_PATHc:oracleoradataspmap1utl

I have cretaed an external table to read data to a table from a csv file placed in "c:oracleoradataspmap1utl". The csv file name is say "pildata.csv"

I have cretaed a package to read data from the exteranl table and to insert it into a table.

INSERT INTO M_PILEINT SELECT
A.AREA AS "AREA",
A.SUB_FAC_DESC AS "SUB_FAC_DESC",
A.SCOPE_DETAIL AS "SCOPE_DETAIL",
A.MTO_ISSUE_DATE AS "MTO_ISSUE_DATE",
A.MTO_TAKE_BY AS "MTO_TAKE_BY",
A.COMMODITY_CODE AS "SECTION"
A.PILE_NAME AS "PILE_NAME"
FROM M_EXE_PILE A

(where M_EXE_PILE A is the external table which is reading from pildata.csv)

The package runs good and data is populated to M_PILEINT .Is there a way , I can rename the csv file (say to pildata_logxxxx.csv.. something like that) from within the package.Whenever the package is run , it will copy the data from exteranl table and renames the csv file to something else..?

View 7 Replies View Related

SQL & PL/SQL :: Call A Procedure In A Different Package?

Sep 13, 2010

I am wrinting a procedure. I want to call a procedure in a different package and get its out value to a variable.

how can I do that in PL SQL?

View 10 Replies View Related

SQL & PL/SQL :: Unable To Create DB Package?

Mar 1, 2012

I´m having a trouble creating a db package. I´ve changed my job and now i´m working full on DB instead of Forms Developer.

So my trouble is that i want to create some like a global cursor on DB package, i know you can create global variables and use it on all the procedures/function inside the package. But how about cursors?

My problem: I dont´know how to declare into the spec. I don´t know how to use it on body.

My actual

I have the structure below:

PackageSpec:
CREATE OR REPLACE PACKAGE [i]MyPackageName[/i]
IS
CURSOR [i]myGlobalCursor[/i](par1 IN VARCHAR2, par2 IN DATE)(
);
PROCEDURE [i]MyProcedure1[/i](par1 IN VARCHAR2, par2 VARCHAR2);

[code]....

View 13 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 :: Get DBMS-PIPE Package?

Sep 24, 2012

SET serveroutput on size 1000000
SET wrap on
SET linesize 80

DECLARE
v_text VARCHAR2 (4000);
v_res NUMBER;
v_num NUMBER;

[code]...

View 6 Replies View Related

SQL & PL/SQL :: Overloading In Oracle Package

Sep 21, 2010

I have some doubt over the overloading concept in oracle. Check the following code. Everything is same ie procedure name,data types and order.Only diffrence is the variable names.but oracle don't give any error.

create or replace package overeload_pkg as
procedure overload(x number,y varchar2);
procedure overload(y number,x varchar2);/* why oracle allow this */
end;
[code]....

View 11 Replies View Related







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