SQL & PL/SQL :: Retrieving Multiple Values In Stored Procedure Using Variables And Cursors
Oct 6, 2011
The goal is to create a stored procedure that will retrieve multiple values from a table.
GUI is in Java and they will trigger our procedure to show list of all employees and their roles , doj etc.
So I wrote the following procedure.
---------------------------------
create or replace
PROCEDURE emp_test(
c_cursor OUT SYS_REFCURSOR)
AS
BEGIN
OPEN c_cursor
FOR
SELECT emp_name, emp_doj, emp_role FROM emp_table ;
END;
---------------------------------
I'm using sql developer, stored procedure is compiled and I can manually run it by right clicking on the procedure and click 'Run'.
When I intend to run it by executing the script "Execute Procedure name ", I get errors.
In SQL Developer, I open new SQL file and key in
EXECUTE emp_test;
Highlight it and run the script, here is the list of errors that I get.
-------------------------------------------
Error starting at line 18 in command:
execute frm_lst
Error report:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'emp_test'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
--------------------------------------------
Issue 2:
Instead of using cursor, is there a way to use multiple parameters and insert the data retrieved from select column_name from table into these parameters and build it in a stored procedure. I tried something like below which did not work.
____________________________________________________
CREATE OR REPLACE PROCEDURE emp_test1
(e_name OUT emp_name%TYPE,
e_dob OUT Edob%TYPE)
IS
BEGIN
SELECT emp_nam, Edob
INTO e_name, e_dob
FROM emp_table
END emp_test1;
End;
______________________________________________________
Just so you know, there is no input parameter or input feed, when called the procedure should return all the values.
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??
assigning values to a particular variable that i need for my button trigger. I Understand that you can assign multiple values to a variable that has a varchar or char data type....is there a way to assign multiple values to a variable that has a 'number' data type?? I need this for my 'where' clause
declare usergrade varchar(4) := 'pass'; user_unitcode number(6) := ;--needs three unit codes to equal pass
Below are the data for rows that I want to insert into CUSTOMER_PRODUCT table from a stored procedure.
Instead of making round trips twice to insert these two rows, I looking for a way to pass in the data for both those rows and then insert them from within the stored procedure in one shot.
The stored procedure will be invoked by Java and .NET.
Sample Data for CUSTOMER_PRODUCT:
ROW 1: CUSTOMER_ID : 1000 PRODUCT_TYPE : PROD123 IS_MEMERSHIP : Y IS_EMAIL_SUBSCRIPTION: Y
ROW 2: CUSTOMER_ID : 1001 PRODUCT_TYPE : PROD123 IS_MEMERSHIP : Y IS_EMAIL_SUBSCRIPTION: Y
Question 1: Should collection be used? (or) is there any other approach that could be utilized?
Question 2: Are there any performance concerns in passing collection and iterating it to fetch value to insert into CUSTOMER_PRODUCT table?
In normal sql statement: select sum(order_items.quantity*items.price) sales_price from order_items,items where order.item_id=items.item_id;
I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.
Ex: Cursor C_employees(C_empid number, C_cityname varchar2) is select emp_name, office_name from employee where employees where empid = c_empid and city = c_city_name;
I know we can pass one parameter to the cursor but I do not know how to pass multiple parameters.
declare v1str varchar2(100):='select empno,ename from emp'; v2str varchar2(100):='select empno,ename,sal from emp'; type t_array is varray(2) of varchar2(100);
[Code]....
So my problem is while executing the different sql statements by passing it to the procedure,how can the procedure would behave dynamically.It must be able to process all the sql statements.
how to print cursors? I mean like columnname : value. of couse curosr.columnname gives what I want. but I want genrally used way.
CREATE OR REPLACE PROCEDURE PROC_TEMP IS BEGIN FOR C1 IN ( SELECT sbjct_id,sbjct_step FROM RND.SBJCT_ORDR M) LOOP DBMS_OUTPUT.PUT_LINE('c1 : '||c1); END LOOP; END;
Am using Oracle 9i and developing stored procedures in SQL Developer. Have a situation where one stored procedure A is calling another B. Stored proc B builds the SQL based on parameters passed in. For one of the parameters i would like to use a bind variable in the SQL that proc B builds. The bind var is passed back to proc A as a part of the SQL string. In proc A, i then try to bind that variable to a parameter(value), however, the bind does not seeem to work.
The SQL string contained in v_SQLStatement defined as VARCHAR(4000) that is passed back to proc A looks like:
SELECT em.event_title AS event_name, e.start_date AS start_date, e.end_date AS end_date FROM d_event_ml em inner join d_event e ON em.event_id = e.event_id WHERE em.language_id = 46 AND e.end_date >= SYSDATE AND e.stream_id IN ( :v_x1 ) AND e.event_id IN (SELECT event_id FROM d_events_seas [code]....
and o_EventList is defined as REF CURSOR. i'm experiencing is that :v_x1 stays as :v_x1 and does not change.This is my first attempt at using bind vars. URL....
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;
----- SELECT SUM(s1.PRODUCT_QTY) INTO anz FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3 WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID AND s2.REGION = 'ANZ' AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
[Code]..
The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.
The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like
----- SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3 WHERE .................. -----
PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2, P_Txn_Ccy IN VARCHAR2, P_Allocation IN VARCHAR2, l_lty_id IN VARCHAR2 ) IS l_balance_fmt NUMBER := 0; BEGIN [code]....
it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..
I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30); END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011' INTO END_DT FROM DUAL; [code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
I am currently working in form 10g and i m newbie to that technology...I want to debug my form and also check step step by values of variables which i defined.
create or replace procedure my_proc(p_user in varchar2) is l_cursor sys_refcursor; l_query constant varchar2(1000) := 'select a' || 'from ' || p_user || '.user_table' || 'where param0 = :x' || 'and param1 = :x' || 'and param2 = :x'
[Code]...
Suppose I execute my_proc many times and for multiple values of p_user. For performance reasons, will l_query be stored in the cache as I am using bind variables or it will not since I have the concatenation with p_user value ?
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.