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


ADVERTISEMENT

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

SQL & PL/SQL :: Procedure That Will Format SQL

Feb 9, 2011

plsql package with a procedure or function that can format sql code. I am looking for two things:

1) I want to pass a sqlid or (address,hash_value,child_number) to a package fuction/procedure and have it return the sql from v$sqltext in a formatted manner.
2) I want to pass a sql statement to a routine and get it back formatted.

I don't need lots of flexibility in how it is formatted, don't really care if commas are leading or trailing, don't care if indentation is 2,3,4 spaces. I just want something readable.

Am willing to take any set of datatypes for parameters. A refcursor going in or coming out, clob in or out, varchar2 in or out. Anything is better than what I got now cause right now I am limited to cut/paste into Toad or a web page. I am looking for a callable plsql routine. An oracle supplied package would be nice but they have never provide this to my knowledge.

View 6 Replies View Related

SQL & PL/SQL :: Generating XML From Procedure?

Mar 11, 2012

Below is the procedure I created to generate csv file from oracle procedure.

PROCEDURE proc_csv(p_path varchar2 , p_separator varchar2 default ',')
AS
CURSOR c_data
IS
select * FROM product ;
v_file UTL_FILE.file_type;
BEGIN

[code]...

I have two doubts about this procedures

1) In one schema this procedure is generating empty csv file with zero bytes, while in other schem It's working fine, It's showing data. I am unable to make what's the difference.

2) The csv file is not displaying column headings. I want column headings to be displayed on csv file.

View 4 Replies View Related

SQL & PL/SQL :: Know If Procedure Is Running?

Jul 28, 2010

My problem is that i don't want to execute a procedure from my scheduler if the previous execute didn't finished. Is there a manage view or table that provide the procedure state?

View 15 Replies View Related

PL/SQL :: No Output From Procedure

Dec 4, 2012

When I run the below procedure it does not return any data. But it returns stating Procedure created. When I just run the query it returns the result.

So what am I going wrong here.

set serveroutput on
CREATE OR REPLACE PROCEDURE PROD.STATUS_COUNTS
(
p_count IN OUT number,
p_status IN OUT varchar2,
p_mpp IN OUT number
)
AS
BEGIN

[Code]....

View 7 Replies View Related

How To Use Parameters In Plsql Procedure

Oct 21, 2010

I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.

Example:
in excel the column has : 'ABC','GEH','HGT',LTP'

create or replace procedure abc(temp_col varchar2)
.
....
....
...
SELECT COLA, COLB, COLC
FROM TABLE_TEMP
WHERE TCOL IN temp_col;

This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.

View 2 Replies View Related

Scheduling Job In Stored Procedure

Dec 21, 2011

PFB code i used to schedule a job as per my requirement. And the procedure is executing fine, but when im about to run it is getting hang.

create or replace procedure scheduler_alert(frq varchar2,intrvl number) is
begin
dbms_scheduler.create_job(
job_name=>'scheduler_alert',
job_type=>'stored_procedure',
job_action=>'alertlog_error',
start_date=>SYSTIMESTAMP,
repeat_interval=>'FREQ='||frq||';INTERVAL='||intrvl,
enabled=>true,
auto_drop=>false);
end;
/

When im trying to run the job it is getting hang.

exec dbms_scheduler.run_job('scheduler_alert');

View 3 Replies View Related







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