SQL & PL/SQL :: Stored Procedure Execution Jump To End

Mar 13, 2013

I created the following stored procedure which I am calling from a script. I compiled my Stored Procedure with Debug Info. For some reason Execution jumps from the second BEGIN to the END statement.

Since the SP compiles w/o any errors, I suspect I have a logic error.

Stored Procedure:
CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW
(
VALIDATED OUT int,
LAST_NAME IN VARCHAR2 DEFAULT NULL,
FIRST_NAME IN VARCHAR2 DEFAULT NULL,
DOB date DEFAULT NULL,
PAT_NUMBER OUT int,
FACILITY_KEY OUT CHAR
)
AS
BEGIN
/* SELECT * */
BEGIN
[code].......

View 7 Replies


ADVERTISEMENT

Forms :: Debug - Values Stored During Execution?

Aug 25, 2010

I have a set of code being perform in the package body of a program unit I want to debug step by step and see what values are being stored during executing or running the form


/* Without HP and deposit, Down Payment(GST) = Total(GST) */
if nvl(v_deposit, 0) = 0 and nvl(v_loan_amt, 0) = 0 then
v_gst_fr_dps := 0;
v_gst_hp := 0;
v_gst_bal_pay := v_total_gst;
[code].........

Like in the above example I want to see the value in v_gst_fr_dps,v_gst_hp,v_gst_bal_pay etc when executing and display in the screen.

View 1 Replies View Related

SQL & PL/SQL :: Find Names Of All Stored Proc Invoked On Execution Of Main Api?

Oct 1, 2013

I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.

For ex:
Proc A
call c
call d
end............

[code]...

This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?

View 16 Replies View Related

PL/SQL :: Call More Than One Stored Procedure In New Stored Procedure

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

Function And Procedure Execution

Jul 7, 2010

I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.

how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.

View 3 Replies View Related

SQL & PL/SQL :: To Generate Procedure Execution LOG

Sep 3, 2013

I have a very big oracle procedure. Since it's too big and calling many other procedures, I am not able to debug the exceptions thrown. Any oracle utility which logs all the procedures called by the master procedure step by step and maintains a detailed record.

View 21 Replies View Related

SQL & PL/SQL :: Parallel Execution Of A Procedure?

Jul 22, 2010

Is it possible to run a single procedure in parallel. (Not looking for multifple, using DBMS_JOB)

I am using Oracle 9i.

View 2 Replies View Related

SQL & PL/SQL :: Procedure And Function Execution

Jul 8, 2010

I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.

View 2 Replies View Related

SQL & PL/SQL :: Dropping A Procedure During Execution

Apr 26, 2012

When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .

View 5 Replies View Related

SQL & PL/SQL :: Write Log When Procedure Execution Is Successful

Aug 10, 2011

My need is to check whether procedure execution is successful and write sysdate and procedure name into log table.

Sure I can check target table which procedure uses to write result and count lines after execution. But I've got different procedures, some of them can write 0 lines and it's not error. I think Oracle should have something like return codes and some number means "ok".

View 12 Replies View Related

SQL & PL/SQL :: Oracle 11g - Parallel Execution Of Procedure

Jul 28, 2010

Oracle Version is 11g.

I need to call the same procedure with different parameter multiple time in parallel.

I have job_control Table

CREATE TABLE JOB_CONTROL
(
JOB_CONTROL_ID NUMBER NOT NULL,
JOB_SEQ_NO NUMBER NOT NULL,
MODULE_NAME VARCHAR2(32 BYTE) NOT NULL,
JOB_STATUS VARCHAR2(15 BYTE),
NO_OF_RECORDS NUMBER,
PROCESSED_RECORDS NUMBER
);

Insert into JOB_CONTROL
(JOB_CONTROL_ID, JOB_SEQ_NO, MODULE_NAME, JOB_STATUS, NO_OF_RECORDS)
Values
(20, 1, 'SALES', NULL, 5);
Insert into JOB_CONTROL
[code]........

Now the table look like this.

JOB_CONTROL_IDJOB_SEQ_NOMODULE_NAMEJOB_STATUSNO_OF_RECORDSPROCESSED_RECORDS
201SALES5
212SALES5
223SALES5
234SALES5
245SALES5
256SALES3

based on the data available in table i wanted to execute a procedure 6 times

create table job_table
(now date, seq_no number)

the procedure will have one parameter as job_seq_no

create of replace procedure job_call (i_job_seq_no number)
is

begin
for i in 1.. i_job_seq_no
loop
insert into job_table
values
[code]......

the problem is i want to do this activity in parallel , i.e. all six calls to the procedure would be parallel. and this will be one time activity.

View 10 Replies View Related

PL/SQL :: Return Results Before Procedure Execution Completes

May 21, 2013

Is there any way of returning output parameter values to calling environment before completion of procedure execution. I may achieve it by using GTTs, looking for any other way (because calling environment again need to issue select statement to retrieve data from GTT).

Example case:

Procedure have multiple ref cursors as out parameters.
....
...
if exp1=exp2
then
open v_ref_var1 for select ...from ... ;
end;
[code]..........

If the first if condition satisfies, ref cursor - v_ref_var1 data should be immediately available for the calling environment.

View 4 Replies View Related

Server Utilities :: Execution Of Oracle 11g Wrapped Procedure Gives Ora-00900 Error

Apr 4, 2013

I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.

The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.

I tried even Oracle 10g wrap it is also not working fine.

View 4 Replies View Related

Scheduling Job In Stored Procedure

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

How To Use Synonym In Stored Procedure

Aug 28, 2009

how 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.

View 5 Replies View Related

Shellscript From Stored Procedure

Jan 10, 2012

I 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.

View 1 Replies View Related

SQL & PL/SQL :: Java Stored Procedure

Mar 12, 2010

I'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 ?

View 15 Replies View Related

PL/SQL :: Dynamic SQL In Stored Procedure

Jun 11, 2013

I 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 Related

SQL & PL/SQL :: Stored Procedure Just Hangs?

Nov 20, 2011

I 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]....

View 11 Replies View Related

SQL & PL/SQL :: Sequence In Stored Procedure

Apr 18, 2011

I 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?

View 7 Replies View Related

SQL & PL/SQL :: Stored Procedure Vs Function?

Jun 9, 2010

Interviewer 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 Related

How To Execute A Stored Procedure In Oracle

May 22, 2012

how 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.

View 1 Replies View Related

When Are Statements Within Stored Procedure Compiled

Jul 23, 2010

connect 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"?

View 2 Replies View Related

Stored Procedure To Read XML Files?

Apr 23, 2009

i 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..

View 2 Replies View Related

Temp Table Within A Stored Procedure

Jul 15, 2013

I'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]........

View 1 Replies View Related

Outputting Results Of Stored Procedure

Jan 11, 2008

I 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).

View 14 Replies View Related

Difference Between Stored Procedure And Function?

Jan 12, 2010

I 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).

View 2 Replies View Related

Oracle 8.5.1 - Stored Procedure Not Added To DB

Mar 21, 2007

After 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!

View 1 Replies View Related

SQL & PL/SQL :: Calling Exe File From Stored Procedure

Jun 15, 2011

I 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.

View 2 Replies View Related

SQL & PL/SQL :: Usage Of Cursors In Stored Procedure

Feb 22, 2012

i 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??

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved