How can we pass multiple parameters to cursors?
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
type ref_cur is ref cursor;
r ref_cur;
enam emp%rowtype;
dno dept.deptno%type;
begin
dbms_output.put_line('The Employee details are');
open r for select deptno from dept;
loop
fetch r into dno;
[code]....
Error at line 1
ORA-06550: line 12, column 28:
PLS-00103: Encountered the symbol "FOR" when expecting one of the following:
. ( % ;
if i need to use ref cursor to send parameters, is it possible? if yes how to use it?
i trying to pass the char varible to the cursor but it is not taking ,,, if i hardcode the values to the cursor it is takinghere is the detailed program ... any body can tell why this is not taking and tell me how to pass the values through it..
declare
v_name char(6) ;
cursor c1(c_name char) is
select name, parent,child,status from relation
start with name='%'
[code]....
i want to update a table passing the parameters and the updating the table according to it
i trired this
create or replace procedure move
(z1 in game.item_id%type,g1 in game.item%type)
as
begin
update game
set
postion= 'l'
where item_id=z1 or item_id=g1;
end move;
/
i want to pass parameters from xyz form to abc form when i press a button. how to pass parameters.....
View 5 Replies View RelatedI need to pass two parameters (STUD_ID) and (TERM) from Form A to Form B. I've read a lot but I'm not familiar with terms and oracle jargon yet! So I really need your straight forward clarification
From the Calling form I'm using when_button_pressed trigger...what shall i write in here?! In the Called form, I'm declaring the following under (When_new_form-instance):
declare
p1_id paramlist;
stud_id number(8);
term number(5);
begin
p1_id := get_parameter_list(stud_id);
p1_id := get_parameter_list(term);
:TEST_STUD_CRSE_REG.stud_id :=:parameter.stud_id;
:TEST_STUD_CRSE_REG.term :=:parameter.term;
next_block;
end;
-----------------
on the calling form:when_button_pressed I am using:
DECLARE
p1_id paramlist;
stud_id number(8);
term number(5);
BEGIN
call_FORM('test16SEP',NO_HIDE);
GO_BLOCK('TEST_STUD_CRSE_REG');
set_block_property('TEST_STUD_CRSE_REG',default_where,'STUD_ID=:TEST_STUDENT_INFO.STUD_ID');
execute_query;
END;
,I have a queryIn my query iam passing parameters using IN clause.The parameters contains in two tablesfor ex..select a.deptno, b.deptnofrom dept1 a, dept2 bwhere a.deptno = b.deptnoand NVL (a.deptno, b.deptno) in (10, 20, 30,.....) is this correct way to use NVL like this.i have a deptno consists in both the tables. How can i use this condition.
View 4 Replies View RelatedIs it possible to pass parameters from menu file to form when calling a form from menu item?
View 4 Replies View Relatedi try this url: [URL]
with this key in my cgicmd.dat :
url_config: server=ed_url2 report=%1 userid=user/passxd@base destype=file desformat=pdf
and i have this error:
Unable to open file 'my_parameter='.
The rwservlet converts the value of my parameter to 'my_parameter='. It adds a '=' systematically at the end of my parameter.
I want to create table from query with passing parameters
create table as temp
select * from emp
where hiredate between :sdate and :edate
and deptno = :dpt
when I tried in Toad after passing parameters it gives me error
ORA-01036: illegal variable name/number
I have a Pro*C program, which uses a dynamic query. The dynamic query is opened using result of another static cursor( 5 fields say , :a, :b , :c, :c, :d).
I am modifying the dynamic query and adding UNION for some requirement , which makes this dynamic query exactly double in size. ( means 2 set of prev. queries are joined by UNION, with one extra condition though).
The question is , Do I need to pass 2 set of variable to open the dynamic query now?
Like earlier , program was passed with (:a, :b , :c, :c, :d)
so now i should pass (:a, :b , :c, :c, :d , :a, :b , :c, :c, :d)?
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a form which has few textboxes, and few dropdown, and 3 links. Data is entered in few boxes, and then when a link is clicked, it goes to another page, but i want to pass the text in the text boxes, and the selected value from the dropdown also, to be passed as parameters to this link... But since these values are not yet saved, its not getting passed.. like it is something like this in the address bar add_new_event?&event_ id= &event_ name= & event_dt=
1. I m building a form in which students will file some data (name, address, etc) but i want to send a parameter on the URL i send them (their student ID number), which will be saved together with the data the end-users save.
e.g the form i m building is [URL]
can i add in the end of the URL the student_id of the end of the URL?
so it will be something like [URL]
So when student #10 will hit the URL on his browser, once he inputs hsi data and press SAVE button, on the database i can save STUDENT_ID=10 and the rest info he just entered?
2. Which is the login URL for end users? i have only the workspace login now..
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 am trying to execute a STORE PROCEDURE from SQL*PLUS with no success:
SQL> execute PACKAGE.PROC(201011,'144792');
BEGIN PACKAGE.PROC(201011,'144792'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'PROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
In fact, when i do: desc PACKAGENAME . I see that the procedure is waiting for 3 parameters and one of them is a REF CURSOR type:
SQL> desc PACKAGENAME
PROCEDURE PROCEDURENAME
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PWEEK NUMBER IN
PCLIENT VARCHAR2 IN
CRESULTS REF CURSOR IN/OUT
After searching a bit, i try the following:
SQL> execute PACKAGE.NAME(201011,'144792','CRESULTS
'=>:C1);
SP2-0552: Bind variable "C1" not declared.
SQL>
This is a preview of the PACKAGE header:
CREATE OR REPLACE PACKAGE PACKAGENAME
AUTHID CURRENT_USER
AS
--
TYPE CurTyp_Supp IS REF CURSOR;
--
TYPE TabTyp_Supp IS TABLE OF VARCHAR2 (10 BYTE);
--
TYPE ObjTyp_Prmt IS OBJECT (p_schemaname VARCHAR, p_filename VARCHAR);
--
PROCEDURE PROC(pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp);
This what the PACKAGE BODY looks like:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
PROCEDURE PROC (pWEEK NUMBER,
pCLIENT VARCHAR,
cResults IN OUT CurTyp_Supp)
QUESTION:
HOW DO I MANAGE TO EXECUTE THIS PROCEDURE FROM SQL*PLUS
How can I pass multiple value in one parameter
example
select * from table
where table_cd in ('01','02','03','04')
here i want to put multiple value like above query by select 1 value in list
like when user select 'A'
THE VALUE PASS IN WHERE CLAUSE ('01','02','03')
FOR 'B' ('03','045','07')
FOR 'C'('044','046','078')
I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :
where account_code = P_CODE.
Eg. The value of :P_CODE is 'CS'.
I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.
I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0
I want to set both nls_language and nls_date_language, and set them as different languages. fnd_global.set_nls_context() doesn't work. So I think maybe I can use the EXECUTE IMMEDIATE, and add them together into one statement.
View 7 Replies View RelatedI am trying to put transactions into 3 different "buckets" for each month of the year.
I am trying to figure out if the syntax for the "else" part of this code is correct... this will basically appear 12 times for each month of the year changing the value for month:
case when floor(sum(total_adjusted_net_amount)/100) < 0 and month in (01) then 0
when floor(sum(total_adjusted_net_amount)/100) > 29 and month in (01) and then 30
else when floor(sum(total_adjusted_net_amount)/100) and month in (01) end Groupin_01
When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN
send_mail('from@domain.com', 'to@domain.com, ???
'Subject', 'Message');
END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
I studied a document about lexical parameter in that it says "Lexical parameters are used to substitute multiple values at run time and are identified by a preceding '&'. Lexical s can consist of as little a one line where clause to an entire select statement"
Select * from emp, deptno &where.
and i know about substitution variables using & is this are same (lexical and substitution) or different.
I have two methods using pro*c to execute a query SQL and read the results. The query is a "select from" a view, this view is a union between two tables. The first method creates a cursor and fetches the results line by line. A second method creates a cursor and fetches the results in parts.
Method 1)
CODEEXEC SQL FETCH Cursor1 INTO ...
Method 2)
CODEEXEC SQL FOR :numLines FETCH Cursor1 USING DESCRIPTOR areaDescSQL;
I had executed this methods in my development enviroment, using Oracle 11g, Suse linux. As I expected, the method 2 spent much less time than the method 1. But, in the "customer enviroment", using another database, Oracle 11g and HP-UX, these methods spent almost the same time.
Is there some Oracle's configuration or parameter's settings that maybe explain that? What configuration can be differente between the two databases? What can I do to improve the time spent?
I'm currently looking for a way to declare a cursor in the 'declare' block using a previously defined variable that got its value from a query. For instance:
declare
my_company_id INTEGER := 'select c.company_id from company_table c where company_name='Wal-Mart';
cursor employees is
select e.employee_id from employees e where e.company_id = my_company_id;
Any way to do this?
an alternative to Oracle Cursors.My table has huge amount of data and is taking time in processing. I had thought of using BULK COLLECT but it can only be used to insert, delete or update data and will not allow me to select data.
View 33 Replies View Relatedi 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??
My requirement is like as follows,
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.
I have a table table1 with 2 crore records.
select * from table1;
id code updateddatetime
------------------------------------
1 10001 2011-10-21 15:31:21.390
2 10001 2011-10-21 15:31:22.390
3 10001 2011-10-21 15:31:21.390
4 10001 2011-10-21 15:31:22.390
5 10002 2011-10-21 15:31:22.390
I want to delete records like id 2 which has odd updated time which is more than id 3 updated time.
is there any alternatives without using cursors as it taking so much time to process.
homework assignment using pl/sql based on 2 tables I have created below? I am not sure of how to use cursors, loops and proper syntax.
ASSIGNMENT:
1. Create a PL/SQL Procedure (cursor to loop through the records, check the LastName, then update the grade table
where id=id on grade table)
Rule:
A
‐ LastName ends with a character between A‐F
B
‐ LastName ends with a character between G‐K
C
‐ LastName ends with a character between L‐P
D
‐ LastName ends with a character between Q‐T
E
‐ LastName ends with a character between U‐Z
Create TABLE Registration (RegistrationID number(10), SectionID number(10), CourseID number(10),
SectionNumber varchar2(10),
StudentID number(10), FirstName varchar2(20),
LastName varchar2(20), CourseNumber varchar2(20), CourseName varchar(20));
[code].....
DB version: Oracle DB 10g Enterprise Edition 10.2.0.4.0
I have the following four tables:
tab_main- which lists main projects
tab_sub_main - which lists sub projects
tab_budget - amounts per projects/subprojects
tab_total - I want to load the data here
The table script with data is attached.
I want to load data into tab_total fields for prj_type= 'J' as follows:
1. accn_no from tab_main table.
2. fy from tab_budget table
3. fy_total_amt which is the sum(amt) from tab_budget table by accn_no and fy
4. all_FY_amt which is the sum(amt) from tab_budget table by accn_no
5. all the audit fields- date/user inserted/updated will come tab_budget table
how to create this procedure with cursors.
CREATE OR REPLACE PROCEDURE LOAD_DATA_INTO_TAB_TOTAL_PROC
IS
CURSOR C IS
select distinct m.accn_no, a.control_no,m.prj_type,
b.fy, b.amt, b.user_created, b.date_created, b.user_last_mod, b.date_last_mod
from tab_main m,
tab_sub_main a,
[code]....
CREATE TABLE tab_main
(
ACCN_NO NUMBER(7) NOT NULL,
PRJ_TYPE VARCHAR2(1 BYTE) NOT NULL
)
/
Insert into TAB_MAIN
(ACCN_NO, PRJ_TYPE)
[code]....
I am trying to write which will return cursors to front end program.The logic which I got stuck is while exiting the procedure.I am having some cursors in teh procedures which are read by the front end script.I have one condition which when satisfied I have to exit from the procedure.But the constraint I am facing is if I exit at that logic, oracle closes all the cursor and front end script is not able to read teh cursor and hence throws an error.Is there any way to exit the procedure without closing the REF cursors.
View 3 Replies View Related