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


ADVERTISEMENT

SQL & PL/SQL :: Return Results Based On 2 Columns?

Jul 15, 2013

I've been having an issue and cannot figure it out for the life. First, here's an example set of the data I'm using so you can see exactly what I'm asking.

Emplid Effdt Effseq

10001 '01-JAN-99' 0

10001 '01-JUL-11' 0

10001 '01-JUL-11' 1

10001 '01-JUL-11' 2

10001 '01-JUL-12' 3

What I need to do here, is obtain 3 rows. The 3 rows I need are rows 1, 4, and 5. I need row 1 because its a completely different date. I need row 5 for the same reason: it's a different date. The issue arises with how I can obtain row 4. The problem is that because rows 2, 3, and 4 all have the same effective date(effdt), SQL Developer just returns one of those rows. Because those 3 rows all have the same effective date(effdt), the tie breaker becomes the effective sequence(effseq) number. When the effective date(effdt) is the same, you need to grab the maximum effective sequence(effseq) number and return that whole row's results such as the emplid, effdt, and effseq. It seems so straight forward and something you can use a subquery for, but its not that simple. Note, that you can specifically use the emplid = 10001 in any specific form because there's many employee id's. Also, the rows will not be in a specific order so you cannot just always grab rows 1, 4, and 5. Some employees may only have a single row in the database, and some may have 50 rows. Everything solely depends on the combination of employee id(emplid), effective date(effdt), and effective sequence(effseq) as the tie breaker.

View 7 Replies View Related

Query Return Results That Contain Duplicates - Only First Or Last Required

May 27, 2008

I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:

select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA
where PLLA.po_line_id = PLA.po_line_id
and PLLA.attribute4 is not null

So my output is something like this:

RCE12 This is an item for AUL1
RCE13 This is an item for PWEILL
RCE14 This is an item for AUL1

I just want either the RCE12 or RCE14 record and not both since they both have the same description.

View 2 Replies View Related

Return Results Where Employee ID That Has 2 Different Timezone IDs On Same Date

Jun 21, 2012

I have a table with the following columns

EMPLOYEEIDNUMBER(12,0)
PUNCHDTM DATE
TIMEZONEIDNUMBER(12,0)

I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example

EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456

To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.

View 3 Replies View Related

SQL & PL/SQL :: Query To Return Results From Three Previous Non-consecutive Days?

Dec 10, 2011

I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.

View 7 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

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

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

Execute / Display Results Of Procedure With Ref Cursor OUT Parameter

Apr 12, 2012

How do i execute and display results of a procedure declared with ref cursor OUT parameter.

I am using SQL Developer and Oracle 10.2.

View 4 Replies View Related

Commit Completes Although Current Online Redo Log File Have Been Removed

Apr 10, 2013

Although i have removed current online redo log file in linux os (Oracle Linux),when i type "commit" it says that "commit complete".

Is this fair for this princip?*:" if Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed."*

I think that it can lead to loss of data in some cases..I'm using Oracle 11g R2 on OEL (x64)..

P.S : I haven't multiplexed current ORL group files...

View 9 Replies View Related

SQL & PL/SQL :: To Return 100 Values By Using The Procedure

Aug 26, 2010

I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.

Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.

View 1 Replies View Related

PL/SQL :: Return Values From Procedure

Dec 19, 2012

the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.

CREATE OR REPLACE PROCEDURE test IS

cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;

BEGIN

open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;

View 14 Replies View Related

SQL & PL/SQL :: Insert Function / Procedure Return New ID

Jun 22, 2010

i'm trying to make a sp/function for inserting a record and return the new index.The previous code used regular inserts and needed an additional round-trip to get the id before inserting,, since this is part of a import routine performance is an issue.

CREATE OR REPLACE PROCEDURE SaveAisHeader (
P_Id Out Number,
P_ImportedOn IN Date,
P_Aisimporttypeid In Number,
P_Description In Varchar2,
P_Importedby In Varchar2
[code].....

View 12 Replies View Related

PL/SQL :: Procedure Doesn't Return Values

Nov 29, 2012

I have a stored procedure which has varchar2 as IN and sys_recursor has OUT parameters.

CREATE OR REPLACE PROCEDURE check_values (
     my_values            IN          emp.dept_no%TYPE,
     p_cursor        OUT sys_refcursor
)

[Code]....

/The problem I am facing is in where condition, if I give quoteValues it doesn't fetch me any records when I execute the procedure from sqlplus, but if I am giving my_values it does fetch me records. I am receiving IN parameters like 9856,9712,8723, so first I put single quote around the emp_no and pass that to where condition.

How can I resolve this issue?

View 15 Replies View Related

SQL & PL/SQL :: Use A Procedure Or Function If Have To Return Only 1 Datatype

Feb 1, 2012

how to decide whether to use a procedure or function if i have to return only 1 datatype.

View 5 Replies View Related

Return Multiple Rows From Stored Procedure?

May 26, 2010

How to return multiple rows from the stored procedure in ORACLE..

View 2 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

Windows / .NET :: Return String From Oracle Stored Procedure Using Excel 2003 VBA

Apr 12, 2013

I am trying to return a value of an oracle stored procedure using Excel VBA.

OS: Win XP SP3
Excel 2003
Ora Client: 11g

By trying different things I have noticed, that I could have troubles with the ODBC-connection. Maybe I am not using the right one. To store data returned from select statements I have an ODBC-Connection (Driver: Oracle in XE), which works perfectly, e.g.:

'-----------------------
Sub Extract_Data()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db_name, UserName, Password As String
cn.Open db_name, USerName, Password

[code]....

When debugging the connection string, I find the Provider=MSDASQL5.1.

View 2 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

SQL & PL/SQL :: Getting Duplicate Results

Feb 7, 2012

I am getting duplicate results..I have been at it for hours now.This is what I have:

DECLARE
v_course_id classes.course_id%TYPE := :course_id;
v_instr_id classes.instr_id%TYPE := :instructor_id;
[code]...

The output I get is this (There is only 1 of each in the database)

Class ID: 1 Status: Enrolled Name: John O'Reilly
Class ID: 1 Status: Enrolled Name: Natacha Hansen
Class ID: 1 Status: Enrolled Name: Reed Jetto
Class ID: 1 Status: Enrolled Name: Janis Greenberg
Class ID: 1 Status: Enrolled Name: Vishal Singh
Class ID: 1 Status: Enrolled Name: Francis Hamilton
[code] ...

View 2 Replies View Related

Calling SP Results In ORA-00911

Apr 20, 2007

I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.

call get_all_customer_orgs;

The get_all_customer_orgs procedure is defined as.

create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor)
is
begin
open p_recordset for
select * from customerorgs;
end get_all_customer_orgs;

I don't understand what the invalid character could be.

View 5 Replies View Related

Any Way To Pivot Results Of A Query?

Apr 23, 2007

Is there anyway to pivot the results of a query?

so if i have:

SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP

And it give the following output:

A 10
B 50
c 24

Is there anyway to put into this format?

A B C
10 50 24

I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!

View 1 Replies View Related

Select Field Where Results Are The Same

Feb 26, 2008

I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.

lets see .. so a count of records where widget = widget or something along those lines.

View 9 Replies View Related







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