Re: Specifying Cursor SQL

Tim Marshall wrote: 
I am just learning about PL/SQL.

From what I've read so far (John Palinski's book - I don't know how well it stands up against other publications, but it's done sterling service for me with respect to the Oracle SQL I've been doing the past few years), there doesn't seem to be an easy way of passing an SQL select statement to a function or procedure.

Here's what I'm trying to do (please note I've prefixed -- with an apostrophe for those whose newsreaders format .sig files differently from the post's main body):

create or replace function F_DISTRIB
(SQL_STATEMENT in varchar2(32000))
return number is
/*

SQL_STATEMENT is a select statement which results in anything from a simple single value to a complex construction that returns multiple values.

I specified varchar2(32000) as I don't think I can just leave things open ended with specifying it as just varchar2 without a length specification?

*/
'--
n_Distribution  number;
cursor cDistributions is
   select
    DIST_VALUE
   from
    SOME_TABLE
   where
    SOME_COLUMN IN (SQL_STATEMENT);
'--
begin
'--
  open cDistributions;
    <run through records which add decimal values to n_Distribution>
  close cDistributions;
'--
  return n_Distribution
'--
End


The thing I'm trying to do is pass the SQL statement to the cursor to be used in the cursor's where clause.

AFAIK, the above is not the way to do it. Could I please ask for some site, perhaps, that might explain how to use SQL in a dynamic way as I've tried to do up above?

Thanks very much in advance. 

Look at the examples in Morgan's Library (www.psoug.org) for
Native Dynamic SQL.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
.

Relevant Pages

  • RE: Error: 25002. The specified buffer size is not valid.
    ... Try moving tempdb to a storage card using SQL CE 2.0 Books Online section ... "Specifying the Location of the Temporary Database .. ...
    (microsoft.public.sqlserver.ce)
  • Re: ASP ADO?
    ... I'm not sure you cannot insert a row without specifying all the columns, ... > Error Message When Perform Single Table Column (SQL Insert ... > Microsoft OLE DB Provider for SQL Server error '80040e14' ... The number of values in the VALUES clause must match the ...
    (microsoft.public.scripting.vbscript)
  • [OT] connecting to MS SQL on a different computer (DBD::ODBC)
    ... Can anyone give pointeron how to connect to a server which runs MS SQL ... I thought it would be as simple as specifying a host and ... Prev by Date: ...
    (comp.lang.perl.misc)
  • Re: error 3128 in query
    ... Try specifying a field or the entire set of fields in the SQL string. ... > I use an ACCESS 2003 front end to SQL server 2000. ... > But the same statement works when I try it in SQL server's Query Analyzer. ...
    (microsoft.public.access.queries)