PL/SQL :: Get A Called Procedure / Function Name Within Package?
Jan 11, 2013
is it possible to obtain a called procedure/function name within package?
For a measuring and tracing purpose, I would like to store an info at the beginning of each procedure/function in package with timestamp + additional details if needed.
For example:
CREATE OR REPLACE PACKAGE BODY "TEST_PACKAGE" IS
PROCEDURE proc_1 IS
BEGIN
[Code]....
I would like to replace "???????" with a function which would return a name of called procedure, so result of trace data after calling TEST_PACKAGE.proc_2 would be:
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_2*
11.1.2013 09:00:01 START.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_1*
11.1.2013 09:00:01 END.*TEST_PACKAGE.proc_2*
I tried to use "dbms_utility.format_call_stack" but it did not return the name of procedure/function.
View 7 Replies
ADVERTISEMENT
Sep 16, 2012
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
View 6 Replies
View Related
Jun 9, 2012
Can we Export & Import of Procedure, Function & Package selection by name, as we can export & import of one or more table by name
View 2 Replies
View Related
Apr 30, 2013
I need to call the VB function below from a Procedure's PL/SQL code and capture the returned variable into a varchar2 variable.I looked at the several means and nothing seems to work.
View 5 Replies
View Related
Oct 4, 2012
My purpose is to audit the execution of a specified procedure, function in a package. So I try this audit option audit execute on dbms_java.longname Althought I'm using SYS, it leads to this error:
SQL Error: ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" But when I try audit execute on dbms_java It's ok and it audit every statement that using that package dbms_java. But thing I want is audit the specified procedure on this package, not all of this package.
why DBA_OBJ_AUDIT_OPTS show DBMS_JAVA package object type is procedure ???
View 1 Replies
View Related
Nov 3, 2011
In the below SQL, I am calling a function "listagg_nm(deptno)", and function is to get all the employee name for a particular department with comma separated
output is:
-------------------------------------------------
DEPTNO SAL LISTAGG_NM(DEPTNO)
10 5000 KING,CLARK,MILLER
10 1300 KING,CLARK,MILLER
10 2450 KING,CLARK,MILLER
20 2975 JONES,SCOTT,FORD,SMITH,ADAMS
[code]....
below are the code
select deptno,sal,listagg_nm(deptno) from emp order by deptno;
FUNCTION listagg_nm (p_deptno NUMBER)
RETURN CLOB
IS
v_str CLOB;
[code]....
My doubt/question is for every row in the SQL the function is being called. t means that for 1st row deptno=10, the function is called and return all concatenated name for department 10, then again for 2nd row deptno=10, it again do the same, similarly for all other department.
Can we avoid this, I mean, if function is already returned a concatenated string of ename for deptno=10, then in the next function call it will checks if it is for same department (i.e. deptno=10) then the function will not go into for loop and it will directly return the concatenated string of ename for deptno=10 which we already done for the first row and so on for other department number ( something like storing the value in variable. Is it possible to achive this and does this is good for performance as we are not going into loop inside the function to return the concatenated string of ename for the same deptno everytime ( I have to return more than 10000 char., so i used clob in function and similarly the numbers of row return by SQL is more than 1000)and also how to code this, I tried but unable to achive.
View 13 Replies
View Related
Jul 12, 2012
I have a procedure in the live environment , which operate in 2 mode.
1 regular load at 10 min
2 nightly
The code is same except they set the last loaded time for both in 2 dfferent rows, so that next time run will get incremental data.
I can trace this procedure on mode 1 that is is scheduled but unable to find how the 2nd one is getting trigger every night. there is no jobs corresponding to that. Is there any way i can find which method is trigger the procedure during night.
View 5 Replies
View Related
Jul 17, 2012
how the DB handles record locking when an aggregate function is called? For instance:
...
select count(*)
into v_count
from x;
...Is there a lock maintained on table x for the duration of the transaction so no rows can be inserted or deleted?
View 4 Replies
View Related
Aug 27, 2012
How can I view the log of a stored procedure after it is called by an application?
View 5 Replies
View Related
Jun 13, 2013
Apex 3.2. I have a page with a button. Once the user clicks the button a call is made to a procedure in a package.This procedure acesses a couple more procedures and functions, inorder to import data from one table to another. If the procedure takes more than 5 minutes to complete, the user receives an error message.This is actually the error message the user should get if he tries to do an import while another user’s import is running. Is it possible that if the called procedure doesn’t return in under 5 minutes the page is reloaded and the procedure called again resulting in the above error message.
View 3 Replies
View Related
Mar 11, 2010
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies
View Related
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
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
Sep 20, 2012
what is ref cursor?
How to use ref cursor in a package or in a function?
View 7 Replies
View Related
Mar 9, 2010
I have this following pakg
CREATE OR REPLACE PACKAGE pkg_test AS
-- Create a table type of the table you want
TYPE tbl_test IS varray(100) of VARCHAR2(30);
-- Function that will return the table type
FUNCTION fnc_test RETURN tbl_test;
-- End package
END;
CREATE OR REPLACE PACKAGE BODY pkg_test AS
FUNCTION fnc_test RETURN tbl_test IS
-- type table_name_va is varray(100) of VARCHAR2(30);
-- Variable of the type tbl_test
[code]...
But i am having problem calling this to test it.
declare
TYPE tbl_test IS varray(100) of VARCHAR2(30);
var_tbl_test tbl_test;
begin
var_tbl_test:= pkg_test.fnc_test;
[code]...
View 2 Replies
View Related
Feb 13, 2013
Can we create a Pipelined function in A Package ? I know we can create it standalone function.
View 11 Replies
View Related
Mar 31, 2011
is it possible that two different package can contain same procedure?
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
Nov 11, 2010
I want to add my function to the existing package.
View 8 Replies
View Related
Jun 6, 2013
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;
I got the blow error.
ORA-00905: missing keyword
View 2 Replies
View Related
Feb 15, 2012
The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.
However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:
select GetDate ('010312', 'dd-mm-yyyy')
from dual
Here is the code I used to create the function:
create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is
vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;
View 5 Replies
View Related
Jun 6, 2013
I want to provide execute privilege to a function in a package to other schema.
Owner wedb
Package MASTER
function in a a package is convert_function
GRANT EXECUTE ON wedb.MASTER.convert_function to HRDB;I got the blow error.
ORA-00905: missing keyword
View 5 Replies
View Related
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
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
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
Feb 8, 2013
I'm trying to un-escape the data of a table column using the package function - UTL_URL.Unescape . But the problem is my column is a CLOB and length of my data exceeds that acceptable by the function.
So, i'm just trying to simulate that function behavior normally. My logic is to scan all characters with pattern '%yy' (HEX) and then convert it into DECIMAL using TO_CHAR(,'XX') . But got stuck in this regexp-replace.
with xx as(
select '<p>%3Cp%3Ewefwef%3C/p%3E</p>' col from dual
)
select
UTL_URL.unescape(col) x1,
regexp_replace(col,'%([a-zA-Z0-9]){2}','2|.') x2
from
[code].......
View 17 Replies
View Related
Mar 2, 2010
There is any way to call private procedure out side of the package.
View 5 Replies
View Related
Jun 27, 2012
Recently I created a trigger in my production environment which affected a procedures execution.
Trigger code is as below
CREATE OR replace TRIGGER chk_fresh_lead_time
BEFORE INSERT ON location_refnum
REFERENCING NEW AS NEW
FOR EACH ROW
WHEN (NEW.location_refnum_qual_gid = 'FRESH_LEAD_TIME'
[code].......
This trigger will check if a value is inserted on Location ref num table for the qualifier LEAD_TIME only if the value exist in Fresh template table else it will throw an error message as Quote: Add the fresh template and then add the LEAD_TIME value
We have a procedure in one of the package which inserts values on the same Location ref num table but for a different qualifier say PENDING and not LEAD_TIME as above, but still the procedure is not being executed due to this trigger. How this trigger is affecting the procedures execution.
View 10 Replies
View Related
Jan 7, 2013
difference between oracle procedure & package.
View 1 Replies
View Related
Mar 9, 2010
I need to schedule a stored procedure and a stored package every day @6:30 AM.
I have also a merge statement that I need to execute before stored procedure is executed.
View 3 Replies
View Related
