PL/SQL :: Procedure ORA-20000 / ORA-20001?

May 6, 2013

have problems with the following procedure - in both cases the problem seems to be in line 115 (RAISE;):

CREATE OR REPLACE PROCEDURE EFWP."P_BUILD_VOP_ENVELOPE"(
paramPlanungId LONG,
paramEnvelopeWKTPolygon VARCHAR)
AS
PRAGMA AUTONOMOUS_TRANSACTION;

[code]...

View 2 Replies


ADVERTISEMENT

PL/SQL :: ORA-20001 / Unhandled Error Occurred

Aug 31, 2012

I've a procedure and a trigger. The problem is when I call the proc from inside the trigger I get an error message saying that

Error: ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Unhandled error occured!
ORA-06512: at "ITEX.REFRESH_XSD", line 23
ORA-06512: at line 21

Here're these two problematic parts:

PROCEDURE:
create or replace
PROCEDURE Refresh_XSD(TableName IN varchar2)
IS
-- Variable declarations.
XML_Schema xmltype;
BEGIN

[code]....

So we have an xsd for a table only once. However that XSD needs to be refreshed when the table is altered, or created when the table is created. That's the reason I'm trying to use that trigger. If I comment out the procedure call from the trigger, everything works correctly except it does not do what needs to be done.

As far as I know only can't execute DDL statements from system triggers. But there's not a single DDL statement in there.

View 8 Replies View Related

Application Express :: Error In Mru Internal Routine ORA-20001

Jan 28, 2013

I have a tabular form, when salve at data, works fine. But when update at data following error occurs: Error in mru internal routine: ORA-20001: Current version of data in database has changed since user initiated update process. current checksum.

View 4 Replies View Related

SQL & PL/SQL :: ORA-20000 / ORU-10027 - Buffer Overflow?

Oct 7, 2010

I am running script which drop all public synonyms and create private synonyms for all eligible objects. I am passing value to procedure through cursor and I am using dbms_output.

ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 999999 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 101

I read that by: SET SERVEROUTPUT ON SIZE unlimited would resolve this problem.

What are the effects?Do I need to check any disk space, or any effect on the memory?

View 6 Replies View Related

SQL & PL/SQL :: ORA 20000 Index Partition In Unusable State?

Jan 3, 2012

ora 20000 index partition in an unusable state. what can i do

View 6 Replies View Related

Server Administration :: DBMS-STATS And ORA-20000?

Dec 20, 2011

I've just installed an Oracle 11.2.0.1.0 64 bit server on my windows 7 machine in order to play around while using attempting to run

exec dbms_stats.gather_table_stats('eii','v2x4e')

I get the following:
ORA-20000: Unable to analyze TABLE "EII"."V2X4E", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 20327
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

My initial google searches indicate that I need the select any table and analyze any privileges. I don't think that can be right/appropriate - but I've granted them anyway to no avail.

Select * from user_tables

returns tables in the System and sysaux tablespaces, but not my own schema/tablespace?

View 5 Replies View Related

PL/SQL :: How To Write Procedure To Load Data Into Table Using XML File As Input To Procedure

Sep 20, 2013

how to write procedure to load the data into a table using xml as input parameter to a procedure and xml file is as shown below which is input to me. 

xml version="1.0"?><DiseaseCodes><Entity><dcode>0</dcode><ddesc>(I87)Other disorders of veins - postphlebitic syndrome</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J04)Acute laryngitis and tracheitis</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity><Entity><dcode>0</dcode><ddesc>(J17*)Pneumonia in other diseases - whooping cough</ddesc><claimid>34543></claimid><reauthflag>0</reauthflag></Entity></DiseaseCodes>.

View 3 Replies View Related

SQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master

Mar 6, 2010

I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.

CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....

View 8 Replies View Related

SQL & PL/SQL :: How To Find Parent / Host Procedure Name Within Procedure

Sep 6, 2013

I have the following requirement.Say I have the following procedure created.create or replace procedure myproc (p1 varchar2, p2 varchar2) is begin

find_the_proc_name();
end;

In the above myproc, using find_the_proc_name() i should print the parent procedure name (or function) calling along with the parameters passed.

View 5 Replies View Related

PL/SQL :: Create A Procedure Inside A Procedure

Jun 25, 2012

DB version:11g

can we create a procedure inside a procedure...if not is there any alternative??

in db2 it is allowed so do oracle support this????

View 5 Replies View Related

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

Mar 31, 2004

ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.

View 3 Replies View Related

SQL & PL/SQL :: Compile A Procedure Within Another Procedure?

Mar 19, 2012

I wanted to Compile a Procedure within another procedure.

Step 1:

CREATE OR replace PROCEDURE Compile_test
IS
var1 NUMBER(20);
BEGIN
SELECT user_id
INTO var1
FROM dummy;
dbms_output.Put_line('the output is '
||var1);
END;

Step 2:

CREATE OR replace PROCEDURE Compile_test_in (proc_name VARCHAR2)
IS
var2 VARCHAR2(20);
BEGIN
var2 := 'proc_name';
EXECUTE IMMEDIATE ('alter procedure ||var2|| compile');
END;

Step 3:
exec compile_test_in ('compile_test')

When trying the step 3, i am getting the below error message.

Error at line 1
ORA-04050: invalid or missing procedure, function, or package name
ORA-06512: at "MUTHU.COMPILE_TEST_IN", line 6
ORA-06512: at line 1

View 5 Replies View Related

SQL & PL/SQL :: Calling Procedure Within A Procedure?

Oct 19, 2010

can we call a procedure within a procedure in a package or does it have to be declared before it can be called in the body,where can i find an example

View 9 Replies View Related

SQL & PL/SQL :: Call A Procedure Within A Procedure

Dec 15, 2011

how can I call a procedure from another procedure? whats the syntax?

View 19 Replies View Related

PL/SQL :: Procedure That Drops Another Procedure

May 29, 2013

I need to create a procedure that drops another procedure as follows:

create or replace procedure drop_any_proc (proc_name varchar2) is
drop procedure proc_name;
end;

View 5 Replies View Related

SQL & PL/SQL :: Procedure Within Procedure In Package

Mar 16, 2012

i need to clarify that, we can call a procedure inside a procedure, when i am using inside a package,whether i have specify the called procedure in the Package specification?

View 5 Replies View Related

PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure

Dec 24, 2012

Execute sp1 param1...param6
Execute sp2 param1...param8
Execute sp3 param1...param4

All these stored procedures deals with insert/updated transactions . i need to create a new stored procedure to execute all this in a single stored procedure which will be something like

create procedure sp4(param1...param8)
as
begin
Execute sp1 param1...param6
rollback if any error
Execute sp2 param1...param8
rollback if any error
Execute sp3 param1...param4
rollback if any error
end;

View 6 Replies View Related

PL/SQL :: How To Get Procedure Name

Jul 9, 2013

With our application we are updating one table (P_Balance) in this account status and date through some procedures. in some situation only account status column get proper value, but date column having NULL value.

Now we decided to track this, through which procedure this date column get updated null value. For this I decided to write one trigger for the table "P_BALANCE" through which procedure this update is happening.  How do I get the procedure name.

View 7 Replies View Related

How To Exit A Procedure

Apr 8, 2005

Suppose have the following condition in my procedure :

IF var_count = 0 then
'stop processing' --EXIT PROCEDURE
ELSE
'continue processing'
END IF;

How do I stop futher processing if var_count= 0 . I think about raising an exception...but is there another way out..like a command 'exit' to stop further processing ?

View 3 Replies View Related

SQL & PL/SQL :: How To Get The Name Of Current Procedure

Jul 13, 2011

I have a procedure like below:

create or replace procedure sp_test
is
vs_proc_name varchar2(40);
begin
--get the name of current procedure ,here is "sp_test"
--[color=red]but ,i do not want the hard code here[/color]
insert into test_Page(proc) values(vs_proc_name);
---
--
commit;
end;

I don't want to coding like this:

vs_proc_name:='sp_test';

so ,is there any building function to get that ?

View 5 Replies View Related

SQL & PL/SQL :: Procedure Insert Into?

Mar 12, 2010

, I'm trying to make a stored procedure in Oracle insertcion of records, but before you insert has to get the most code and generate a new one generated more than everyone else, I'm using Max, but as I assign to a variable as in SQL is:

Declare @ IDMax numeric
Select @ IDMax = Max (Code) From Members

Then I would make a:Insert into Users (Code, Name) values (@ IDMax, 'Victor');As serious for Oracle to perform com from declaring a parameter as the Code for me to store the value (Code Maximo)

View 2 Replies View Related

SQL & PL/SQL :: Using More Than Four DB Links In A Procedure

Jul 17, 2013

i want to use more than four db links in my stored procedure to retrieve data from different databases. but it says ORA-02020: too many database links are opened. i also wrote execute immediate to close some links but its not working.

View 16 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 :: Procedure For Getting Old And New Column Value

Sep 19, 2011

I want a procedure which gives me old and new column values which will be maintained in other table ..

for e.g. :=>
ihave a table " product" having columns as " p_id number(pk) ,
p_name varchar2(20),
p_qty number"
every updation in the "product" table will insert the old and new values in "Audit_product_table"
[code]......

View 24 Replies View Related

SQL & PL/SQL :: Procedure In Put Parameter

Sep 5, 2012

I am writing a procedure in which I have a input string parameter in the following way..('NYC,ATL,OKC,KAC,LA'). I need to use that string to search values in the table.

Example

create or replace procedure search_city(p_string varchar2)
/*
paramater will be like ('NYC,ATL,OKC,KAC,LA')
*/
is
v_city varchar2(40)
begin
for i in (select city_name from cities
where city_code in (p_string )
) loop

v_city := i.city_name;
end loop;
end ;

View 2 Replies View Related

SQL & PL/SQL :: How To Run Procedure In Parallel

Dec 28, 2012

we have many tables in Cursor For Loop :

CREATE OR REPLACE PROCEDURE EBILL_BULK_UPDATE_SERVICE(in_cycle VARCHAR2)
AS
v_cnt NUMBER; -----Variable used for checking table is partitioned or not partitioned
CURSOR cur_update -----Cursor defined for Updating EBILL tables for service_id
is
SELECT table_name
, cycle_name
FROM NNP_EBILL_UPDATE

[code]....

As our requirement that Execute Immediate should work for 5 or more tables updation parallely at a time.If one table get completed then it should take next table from loop and then start the code till completion of all tables.

View 11 Replies View Related

SQL & PL/SQL :: Enable To Run Procedure Through Job?

Mar 30, 2011

I am trying to execute procedure using dbms scheduler.but i am getting below errors

ORA-06550: line ORA-06550: line 1, column 407:
PLS-00103: Encountered the symbol "AMANORATEST" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "AMANORATEST" to continue.
, column :

but procedure is executing fine with sql commmand line.

View 1 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 :: Procedure Gets Halted Only For One Value?

May 20, 2010

I have written a pl/sql procedure which takes a number X as an input then searches for the policy numbers which have a total rating record not greater than that number X ...

The procedure works well for every single value as an input but 500! It gets halted for the X equals to 500.

I have used 2 cursors. To dig the problem i run the program with a single print line in the body and still it acts the same.

View 7 Replies View Related

SQL & PL/SQL :: Using VSIZE In A Procedure?

Apr 12, 2013

I am trying to output to the command line screen the nr of bytes (using VSIZE) in the string 'nOT', 'easy'. The first code snippet works fine, setting the first character of each string in capitals.

However I cannot display the number of bytes using VSIZE. Been scratching my head and trying/failing for about 2 hours now!

First snippet: (Works fine)


CREATE OR REPLACE PROCEDURE (firstname IN NVARCHAR2, surname IN NVARCHAR2)
IS

[Code]....

Second code snippet (attempting to using VSIZE to output number of bytes)

CREATE OR REPLACE PROCEDURE (firstname IN NVARCHAR2, surname IN NVARCHAR2)
IS

[Code].....

View 6 Replies View Related







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