Re: order by problem with stored procedure

<sybrandb_at_yahoo.com> wrote in message
news:1149073478.406941.180290_at_y43g2000cwc.googlegroups.com...
:
: Alen Cappelletti wrote:
: > Hi,
: > I try to short dinamically the result my stored procedure.
: > SP seems not validate the field passed.
: >
: > I try durectly from my query sw with -ORDER BY 'no word' -
: > and effecly ther is no error and record return are the same present
: > ordered into the table.
: > So I think the problem is in the SP.
: > The field passed is read like varchar.
: > No i'ts impossible pass teh real name dinamically or I must use
: > numbers?
: > Like ORDER BY 1....
: >
: > Thanks,
: > Alen Italy
: > ------
: > CREATE OR REPLACE PACKAGE BODY "PKG_UTENTI" AS
: > PROCEDURE Utenti(
: > strCookieMD5 IN varchar2,
: > intDaRs IN number,
: > intArs IN number,
: > strOrdinamento IN varchar2,
: > OBJ_CUR_Utenti OUT CUR_Utenti
: > )
: > AS
: > var_TotaleRs number;
: > BEGIN
: >
: > --Tot record
: > SELECT
: > NVL(Count(*),0)
: > INTO
: > var_TotaleRs
: > FROM UTENTI
: > WHERE (STATO = 1);
: >
: > --Rs principale
: > OPEN OBJ_CUR_Utenti FOR
: >
: > SELECT
: > u.*,
: > var_TotaleRs TotRs,
: > rownum SeqNum,
: > strOrdinamento Ord
: > FROM UTENTI u
: > WHERE (STATO = 1)
: > ORDER BY strOrdinamento;
: >
: > END Utenti;
: > END PKG_UTENTI;
:
:
: I already explained to you you needed *dynamic sql*.
: What in that explanation you didn't understand, so you reposted this?
:
: --
: Sybrand Bakker
: Senior Oracle DBA
:

only values can be passed to a query via variables (whether the query is in a stored procedure or not)

you cannot pass object names and columns names to a query -- this requires that the entire query be generated (constructed) at runtime, which is what DYNAMIC SQL means (see NATIVE DYNAMIC SQL in the PL/SQL Users Guide -- you can do this by searching for NATIVE DYNAMIC SQL at tahiti.oracle.com)

short example:

if the host variable :ob_col contains the value 'ENAME'

then the statement...

select :ob_col, sal from emp order by :ob_col

...is the equivalent of...

select 'ENAME', sal from emp order by 'ENAME'

...because a *value* is passed to the SQL statement, not a column name

however, with dynamic sql, the value of the variable is used to build the SQL statement, so:

execute immediate 'select ' || :ob_col||', sal from emp order by '||:ob_col;

is the equivalent of:

execute immediate 'select ENAME, sal from emp order by ENAME';

++ mcs Received on Wed May 31 2006 - 06:20:35 CDT