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.
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.
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.
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
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.
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).
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.
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.
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.
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".
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]........
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].......
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...
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;
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].....
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.
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.
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.
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?
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.
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.
