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
ADVERTISEMENT
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
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
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
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
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
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
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
Dec 15, 2011
how can I call a procedure from another procedure? whats the syntax?
View 19 Replies
View Related
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
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
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
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
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
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
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
Mar 31, 2011
is it possible that two different package can contain same procedure?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
