Hi everyone,
When I am using dynamic sql with USING clause ,the results are not sorted in Ascending order.
DECLARE
TYPE emp_refcursor IS REF CURSOR;
emp_rc emp_refcursor;
TYPE v_emp_id IS TABLE OF number INDEX BY PLS_INTEGER;
TYPE v_last_name IS TABLE OF varchar2(50) INDEX BY binary_integer;
V_empno v_emp_id;
v_ename v_last_name;
p_deptno number := &U_DEPTNO;
v_limit number := 10;
v_ordcolumn varchar2(20) := 'employee_id';
v_stmt varchar2(1000);
BEGIN
v_stmt :=
'select employee_id,las t_name from employees
where department_id = :x order by :y ';
dbms_output.put _line(v_stmt);
OPEN emp_rc FOR v_stmt USING p_deptno,v_ordc olumn;
LOOP
FETCH emp_rc BULK COLLECT INTO v_empno,v_ename LIMIT v_limit;
EXIT WHEN v_empno.count = 0;
FOR I IN v_empno.first .. v_empno.last
LOOP
dbms_output.put _line(v_empno(i )||' '||v_ename(i));
END LOOP;
END LOOP;
END;
When I use dynamic sql with out USING cluase,results are sorted in Ascending order.
DECLARE
TYPE emp_refcursor IS REF CURSOR;
emp_rc emp_refcursor;
TYPE v_emp_id IS TABLE OF number INDEX BY PLS_INTEGER;
TYPE v_last_name IS TABLE OF varchar2(50) INDEX BY binary_integer;
V_empno v_emp_id;
v_ename v_last_name;
p_deptno number := &U_DEPTNO;
v_limit number := 10;
v_ordcolumn varchar2(20) := 'employee_id';
v_stmt varchar2(1000);
BEGIN
v_stmt :=
'select employee_id,las t_name from employees
where department_id = '||p_deptno ||
' order by '||v_ordcolumn;
dbms_output.put _line(v_stmt);
OPEN emp_rc FOR v_stmt;
LOOP
FETCH emp_rc BULK COLLECT INTO v_empno,v_ename LIMIT v_limit;
EXIT WHEN v_empno.count = 0;
FOR I IN v_empno.first .. v_empno.last
LOOP
dbms_output.put _line(v_empno(i )||' '||v_ename(i));
END LOOP;
END LOOP;
END;
P.S :---- department_id (used) = 50;
Please can some one explain why this is happening like this.
Thanks
Raghu
0 2685 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Flapper |
last post by:
Help please,
Have a situation when converting from Oracle SP's to SQL SP's. The old
oracle cursor was roughly as follows
CURSOR cur_rsStock IS
select
*
from
(select StockRowId, CategoryId
|
by: php newbie |
last post by:
I am trying to write a cursor to update certain rows in a particular
order as follows: (I need the cursor version, not SQL, as the update
logic depends on the order of rows and some other conditions. I
removed the Order-By clause from the statement to simplify it; it
gives the same error message with or without it.)
DECLARE prod_cursor CURSOR
FORWARD_ONLY
KEYSET
FOR SELECT 1 FROM all_products WHERE p_qty = 0 FOR UPDATE
|
by: Bernard Dhooghe |
last post by:
To retrieve data from a query where multiple rows can be returned, a
cursor can be used.
Different programming interface exist for cursors: embedded SQL, CLI,
SQL PL, SQLJ, JDBC.
I we look at the CLI interface, as a statement is first prepared in CLI
before a cursor is associated with it, the cursor attributes are not
known at prepare time. Does it mean that the query path for retrieving
rows is not dependent of the cursor attributes...
|
by: Yusuf INCEKARA |
last post by:
I have a stored procedure :
CREATE PROCEDURE STP_GETSTORELIST @RETCUR CURSOR VARYING OUTPUT
AS
set @RETCUR = CURSOR
FORWARD_ONLY STATIC FOR
SELECT ID,STORE_NAME FROM T_INF_STORE
ORDER BY STORE_NAME
OPEN @RETCUR
|
by: traceable1 |
last post by:
SQL Server 2000 SP4 with AWE hotfix. Windows 2003 SP1.
I have a stored procedure which is not working the way I think it
should be.
I have a CURSOR which has a variable in the WHERE clause:
DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
| |
by: david.isaacks |
last post by:
Is there a way on a form of making one field the default field such
that as
soon as you navigate to the form page, the cursor is sitting on that
field
ready for data to be entered.
|
by: £ukasz W. |
last post by:
Hello everybody!
I have a small table "ABC" like this:
id_position | value
---------------------------
1 | 11
2 | 22
3 | 33
|
by: mh1hep |
last post by:
I have two DB2 cursors accessing the same tabel but in adifferent order.
I open the first one, fetch all the data, Then close it.
When I open the second one with the new order, it starts at the last row of the first cursor. I even tried doing a commit to see if it would clear but it doesn't.
How can I get the second cursor to start at the first row in the new order?
Thanks
below are the definitions of the two cursors:
|
by: rkandas |
last post by:
Order by based on input
Select col1, col2, col3
from table 1
order by col1 <ASC/DESC>, col2 <ASC/DESC>, col3 <ASC/DESC>
The sort order for col1, col2, col3 are parameters to the program. Can anyone explain how to parametrize the sort order without using a dynamic sql.
DECLARE cursor clause based on parameter
DECLARE CURSOR csr1 <SCROLL/NOSCROLL> CURSOR
|
by: yeohyc |
last post by:
Hi All,
I have a problem here. I have done a request for move order it was stored into a custom table with the format:
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-01(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-02(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-03(Q)2500
(O)336126(G)83(I)21823(M)630065120(L)LOT-DISB2P-04(Q)2500
System Reads in (O) = Move Order
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |