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.
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>.
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]....
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.
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????
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 RelatedI 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
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 Relatedhow can I call a procedure from another procedure? whats the syntax?
View 19 Replies View RelatedI 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;
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 RelatedExecute 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;
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.
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 ?
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 ?
, 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)
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 Relatedis it possible that two different package can contain same procedure?
View 1 Replies View RelatedI 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]......
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 ;
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.
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.
In package specification, there 3 procedures But in package body, there are 2 procedures...will this execute?
View 14 Replies View RelatedI 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].....
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.
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.
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 RelatedWhen 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]....
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.
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');
