SQL & PL/SQL :: View Log Of Stored Procedure After Called By Application?
Aug 27, 2012How can I view the log of a stored procedure after it is called by an application?
View 5 Replies
ADVERTISEMENT
Application Express :: Procedure Automatically Called Again After 5 Minutes?
Jun 13, 2013Apex 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 RelatedView A Stored Procedure Without Granting Execute Permissions?
Jul 13, 2012Is there a way to grant rights to view a stored procedure without granting execute permissions?
View 2 Replies View RelatedPL/SQL :: Materialized View Based On Stored Procedure Data
Sep 23, 2013is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it?
View 4 Replies View RelatedApplication Express :: URL For APEX To Run A Stored Procedure
Jun 26, 2013I would like to run this procedure using a URL ... I have apex installed. What URL would I use. This procedure is compiled in the database. I have been trying to look thru the APEX installation guide for an example but I must be missing it somewhere.
CREATE OR REPLACE PROCEDURE ORASYSAD.HELLOWORLD is htp.print ('<hl>Hello World</hl>'); end;
Application Express :: How To Execute Stored Procedure From APEX Page
Oct 17, 2012I just started to play with this system and don't understand how to execute the the stored procedure (that creates report) from the APEX page. I know that they have sample documentation and java script call (show below):
<script language=”JavaScript” type=”text/javascript”>
function callMyRep() { var formVal1 = document.getElementById(’P_PAR1′).value;
var url;
url = ‘rep1?p_session_id=&SESSION.’+'&p_par1=’+ formVal1; window.location.href=”f?p=&APP_ID.:12:&SESSION.”;
w = open(url,”winRep”,”Scrollbars=1,resizable=1,width=800,height=600″); Close here. X
[code]....
In this example rep1 is the stored procedure that creates report.How can I execute it using url?
Real Application Clusters :: Stored Procedure Killed Root Blocker In 10g?
Jan 30, 2013I need the procedure that killed root blocker in RAC environmental 10g, I found the stored procedure for 11g which simple but 10g required some expertise because I want to just exec [procedure] it will kill root blocker either locally and remote node
View 5 Replies View RelatedApplication Express :: Coding Stored Procedure - Create Material IN And OUT Form
Nov 21, 2012I have to develop an application for a store, where some material In and Out on daily basis. and i want to see current status any time.
How to do that means coding of stored procedure, on whose basis i can create material IN and OUT form.
SQL & PL/SQL :: Procedure Called To Trace
Jul 12, 2012I 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.
PL/SQL :: Get A Called Procedure / Function Name Within Package?
Jan 11, 2013is 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.
PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure
Dec 24, 2012Execute 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;
Application Express :: Tree To Popup A Called Page
Feb 8, 2013Ive created a tree structure and i want it to popup the called page.select case when connect_by_isleaf = 1 then 0
when level = 1 then 1
else -1
end as status,
level,
'<SPAN style="color:red;">' || "ENAME" || '</SPAN>' as title,
(case when level = 1 then '#IMAGE_PREFIX#Fndtre11.gif'
[code]....
Scheduling Job In Stored Procedure
Dec 21, 2011PFB 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');
How To Use Synonym In Stored Procedure
Aug 28, 2009how to use a synonym in a stored procedure.
I have created a public synonym for a remote table on a different schema.
Im now trying to use the synonym to load that data into a temporary table in my schema using a stored procedure and im getting an error.
how to use a synonym in a stored procedure.
Shellscript From Stored Procedure
Jan 10, 2012I want to call a shellscript which is in application sever through a stored procedure in database sever.i dont do this by dbms scheduler.
May i knw some sytax with examples and the settings to change to accept the external procedure call.
SQL & PL/SQL :: Java Stored Procedure
Mar 12, 2010I've written a Java stored procedure that deletes some provided file. The PL/SQL procedure looks like :
procedure delete (file in varchar);
The procedure does the work correctly when the provided file name exists on the DB server, but doesn't when the file is ou of the DB server.
Is there a way to resolve this ?
PL/SQL :: Dynamic SQL In Stored Procedure
Jun 11, 2013I would like to use dynamic sql for an select query with where clause and then use the dynamic sql in pl/sql stored procedure. how to create dynamic sql (select query) and how to use it in pl/sql stored procedure.
View 4 Replies View RelatedSQL & PL/SQL :: Stored Procedure Just Hangs?
Nov 20, 2011I entered the following procedure code into SQLPLUS for compilation, but it just hangs. I suspect the cause is an infinite loop, but I can't locate it.
CREATE OR REPLACE PROCEDURE populate_sales_fact
AS
BEGIN
INSERT INTO sales_fact
(orderid,
prod_key,
order_day_key,
shipping_day_key,
sales_dollar_amount,
quantity,
cust_key,
emp_key)
[code]....
SQL & PL/SQL :: Sequence In Stored Procedure
Apr 18, 2011I have a sequence my_seq in schema schema1. I have granted select on this sequence to schema2. Doing :
select schema1.my_seq.nextval from dual
in schema2 work as expected. However when I try to compile a package body in schema2 using my_seq in an insert statement, it fails with:
PLS-00302: component 'MY_SEQ' must be declared
What's even stranger is that I have stored procedures that are using the exact same code that are currently compiled and working. Recompiling them yields this error. How is this possible?
SQL & PL/SQL :: Stored Procedure Vs Function?
Jun 9, 2010Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
View 3 Replies View RelatedHow To Execute A Stored Procedure In Oracle
May 22, 2012how to you execute a stored procedure in ORACLE..For example in SQL SERVER its just
EXEC Proc_Name ParameterValues
How the hell do you do this in oracle i just want to test if my stored procedure works.
When Are Statements Within Stored Procedure Compiled
Jul 23, 2010connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created.
...
When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
Stored Procedure To Read XML Files?
Apr 23, 2009i have this stored procedures:
create or replace
PROCEDURE INSERTXML2(
p_xml_in XMLType,
p_table IN VARCHAR2 ) AS
v_context DBMS_XMLStore.ctxType;
v_rows NUMBER;
BEGIN
[code]....
that works well in little XML files but in XML files that are bigger the stored procedures to not work because string maximum length in Oracle is 4000.
im working with Oracle SQL Developer and Sap Mii, the XML file is generated in Sap Mii and then i have to pass it in one step to database..
Temp Table Within A Stored Procedure
Jul 15, 2013I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........
Outputting Results Of Stored Procedure
Jan 11, 2008I am new to the wonderful world of Oracle. I want to be able to view the results of a stored procedure in an output window, say out of Oracle SQL developer. Unfortunately it appears I need to write some more code to actually view the data. Consider the following:
CREATE OR REPLACE
PROCEDURE TESTSPROC2(c_test out sys_refcursor) AS
BEGIN
open c_test for
select * from test_table order by id_no;
END TESTSPROC2;
to view this I need something on the order of:
DECLARE
cc sys_refcursor;
r cc%rowtype;
BEGIN
TESTSPROC2(C_TEST => cc);
loop
fetch cc into r;
exit when cc%notfound;
DBMS_OUTPUT.PUT_LINE('C_TEST = ' || r.data_element);
end loop;
close cc;
END;
But this is weakly typed so I get all sorts of errors associated with the row definition of line 3. create the record based on the table (something like: r test_table%rowtype).
What I really want is a generic reader than can be ported around to output any sproc I put together.
On a more generic note, why Oracle has chosen to make PL/SQL inordinately more complicated than say MS SQL/Servers tSQL? I mean in tSQL I would just write:
CREATE OR REPLACE PROCEDURE TESTSPROC2
AS
select * from test_table order by id_no;
GO
and viola, a nice result set spits out in Query Analyzer (or a .net application).
Difference Between Stored Procedure And Function?
Jan 12, 2010I have to call oracle stored procedure or a function to update user id of one or several rows depending on what user selects.
The oracle person will be writing the stored procedure or function. I am a java developer but was never involved in writing oracle functions. So..
I want to know what is the difference betn stored procedure and function?
From java point of view are there any guidelines when to prefer one over the other(betn stored procedure and function).
Oracle 8.5.1 - Stored Procedure Not Added To DB
Mar 21, 2007After compiling a simple valid SP in Toad for Oracle 8.5.1:
CREATE OR REPLACE PROCEDURE proc_test
AS
DECLARE v_name VARCHAR2(20) := 'Ed Edson';
BEGIN
DBMS_OUTPUT.PUT_LINE('Hi, my name is ' || v_name);
END;
The SP is not added to the schema. Also the three status fields (status, created and Last Update in the Procedure Editor remain <Unknown>. It is not a rights or privilege issue, because the creation works perfectly in SQL*Plus!
SQL & PL/SQL :: Calling Exe File From Stored Procedure
Jun 15, 2011I want to call an exe file through DB Procedure. How it is possible?
1create or replace procedure proc3
2is
3begin
4 host('calc');
6* end;
from FORMS it works fine but it does not work through DB. I found same question through this link
[URL].........
but no answer is given.
SQL & PL/SQL :: Usage Of Cursors In Stored Procedure
Feb 22, 2012i have created a stored procedure with a cursor in order to perform a function where the annual_sal from the employee_annual_sal table is refered and checked. The empno for all the records which satisfies the condition mentioned inside the loop should be displayed in an variable. My code is below
create or replace PROCEDURE sp_test_cursor(out_empno OUT number)
IS
v_get_data number;
v_get_empno number;
cursor c1 is
select annual_salary
from employee_annual_sal;
[Code]...
What should i do to return mulitple values in a single variable??
SQL & PL/SQL :: Calling Stored Procedure With Default Value?
Apr 3, 2012I have the following Stored Procedure:
CREATE OR REPLACE PROCEDURE AFESD.TEST_PROC (I_NUM IN NUMBER,
I_NUM2 IN NUMBER DEFAULT 3, D_DATE IN DATE DEFAULT sysdate,D_OUT OUT DATE)
IS
[Code]....
I dont know what I need to pass in order to give it the default value.