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?
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.
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?
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.
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 .
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:
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;" SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT -------------------------------------------------- PACKAGE BODY PKG_TEST IS /************************************************* ****/ PROCEDURE proc1 IS BEGIN update t1 set EDITION_NAME = 'AAAAAAA'; commit; END proc1; [code]....
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.
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 ???
I have a procedure with following code which runs in infinite loop.
BEGIN LOOP line :='Test Message'; UTL_SMTP.write_data(l_mail_conn, line || Chr(13)); END LOOP; EXCEPTION WHEN OTHERS THEN UTL_FILE.FCLOSE(NAMESFILE); END;
I executed the procedure and disconnected from sql developer by killing the sql developer process from task manager. Reconnected and changed the code and when i tried to compile the procedure. It is not compiling, because the procedure is already running in infinite loop by some other session.
Is is possible to stop the running procedure without killing the session.What will happen if the procedure is not stopped which is running in infinite loop.When I queried the following found that the session is still ACTIVE and CPU used showing as '0' and executing the procedure.
select SST.VALUE CPU, S.USERNAME, S.SID, s.status,S.SERIAL#, SQL_TEXT FROM V$SESSION S, V$SQLTEXT_WITH_NEWLINES T, V$SESSTAT SST WHERE S.SQL_ID IS NOT NULL
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?
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.
I have a stored procedure that is run from a command within our Clarity application.
The procedure involves some SQL Reads and SQL Inserts.
We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.
if there is a clever way of preventing the same SP to be run concurrently?
Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.
Are there better more efficient/effective ways of doing this?
I want to get all the column values in a table and save them into a text file.Beside UTL_FILE, is there any other method which will result better performance in writing to text file?
I have a requirement to create a packaged proc which lists down a set of database objects and its statuses whenever the status of objects is changed as valid/invalid in user_objects. Also, those valid objects need to be compiled while running the packaged proc.
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
