Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!prodigy.net!cyclone.swbell.net!cyclone-sf.pbi.net!216.218.192.242!news.he.net!chekhov.conxion.net!news.oracle.com!not-for-mail
From: amogh <amogh.r@gmail.com>
Reply-To:  amogh.r@gmail.com
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.misc
Subject: Re: sqlQuery as string
References: <1123273175.363078.77840@f14g2000cwb.googlegroups.com>   <dd0huo$l8j$03$1@news.t-online.com>   <1123282217.245939.66490@g47g2000cwa.googlegroups.com>   <DOednadG-eNYqmnfRVn-qA@comcast.com>   <1123336555.540931.187080@g14g2000cwa.googlegroups.com>   <2ZudnT9RpPXWWGnfRVn-vQ@comcast.com>   <1123342310.229369.148160@g47g2000cwa.googlegroups.com>   <RaqdnXp_8a_dd2nfRVn-hA@comcast.com> <1123349881.483621.272600@g49g2000cwa.googlegroups.com>
In-Reply-To: <1123349881.483621.272600@g49g2000cwa.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 63
Message-ID: <y%CJe.5$5C.116@news.oracle.com>
Date: Mon, 08 Aug 2005 11:46:23 +0530
NNTP-Posting-Host: 152.69.155.88
X-Trace: news.oracle.com 1123482718 152.69.155.88 (Sun, 07 Aug 2005 23:31:58 PDT)
NNTP-Posting-Date: Sun, 07 Aug 2005 23:31:58 PDT
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:121101

Greg wrote:
> Can you actually use dbms_sql to do something like
> 
> SELECT DBMS_SQL.SOME_FUNCTION('sql query string') FROM DUAL
> 
> ?
> 
> As far as I know one can only use certain functions in SELECT queries.
> Is there a way to use procedures?
> 
> I don't care about scalar subqueries.
> 
> I am researching a potential vulnerability via functions that take
> string sql queries as argument (standard oracle functions if any or any
> functions from packages that could be used in an SQL query). owa_util
> might be something interesting to look into.
> 
> Thank you,
> Greg
> 
> Mark C. Stock wrote:
> 
>>"Greg" <gregclau@yahoo.com> wrote in message
>>news:1123342310.229369.148160@g47g2000cwa.googlegroups.com...
>>
>>>Actually there are a few XML functions in DBMS_XMLGEN that take in only
>>>SELECT queries but I was wondering if there are any other outside of
>>>those.
>>>Thanks,
>>>Greg
>>>
>>
>>most folks don't think of Oracle supplied packages when the subject is
>>oracle functions
>>
>>the packages dbms_sql and owa_util also handel dynamic sql
>>
>>can you describe what you're trying to accomplish?
>>
>>your previous example:
>>
>>    SELECT FUNCTION('SELECT COLUMN FROM TABLE') FROM TABLE
>>
>>could just be written as a scalar subquery, ie:
>>
>>    select
>>      ename
>>    , deptno
>>    , (select sum(sal)
>>      from emp e2
>>      where e2.deptno = e1.deptno
>>      ) as dept_sal
>>    from   emp e1
>>    order by ename
>>
>>why do you need to pass the query as a string, and under what circumstances?
>>
>>++ mcs
> 
> 
SELECT ENAME,CURSOR(SELECT EMPNO FROM EMP) FROM EMP;

Note that the cursor query here is not a string.