Re: order by problem with stored procedure


<sybrandb@xxxxxxxxx> wrote in message
news:1149073478.406941.180290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
:
: 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


.

Relevant Pages

  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... stored procedure, the user needs EXECUTE rights to the stored procedure. ... Query 1 requires the user to have SELECT rights on the Customers ... Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the ...
    (microsoft.public.sqlserver.tools)
  • Re: set a select statement stored in a table in a variable
    ... You do not require a dynamic sql statement to use a variable after = clause. ... > Inside a stored procedure I want to set ... > into a variable and then execute it using ... > the query I get the statement ...
    (microsoft.public.sqlserver.programming)
  • Re: Query question
    ... dynamic sql inside of a stored procedure. ... that allow any string as a substitute. ... The query execution plan can be reused. ...
    (microsoft.public.sqlserver.server)
  • Re: Can I bind MDX queries
    ... You can write the MDX query in a Stored procedure and use Dynamic SQL ... Server) with a linked analysis server. ...
    (microsoft.public.sqlserver.olap)
  • Re: Help me convince the dev manager. Please.
    ... The query cost w/ dynamic SQL won't necessarily be lower. ... Some might complain about the maintaining multiple stored procs, ... > is no database design or structure that would help with this problem. ...
    (microsoft.public.sqlserver.programming)