Re: string manipulation in procedures

On Mon, 28 Feb 2000 13:14:37 +0000, kev <kevin.porter_at_fast.no> wrote:

>Sybrand Bakker wrote:
>
>> On Mon, 28 Feb 2000 11:01:10 +0000, kev <kevin.porter_at_fast.no> wrote:
>>
>> >Hi,
>> >
>
>[snip]
>
>>
>> >
>> >- Kev
>> >
>> >
>>
>> What you want is dynamic sql. AFAIK you can't just combine a REF
>> CURSOR with dynamic sql. You could do something similar with dynamic
>> sql, however your procedure is going to return a cursor handle only,
>> fetching and looping needs to be done separately.
>>
>
>But can I integrate the user-supplied query into another query string which
>I can then execute?
>
>I am using PHP, I can return and use a cursor no problem. I just need to be
>able to do that query in the procedure.
>
>thanks,
>
>- Kev
>
>PS - What is Dynamic SQL? Do I have to spend money to get it?
>PPS - Why do Oracle make it so difficult to do this when free DBs such as
>MySQL make it as easy as "select this from that LIMIT 20,10"?
>

On second thought and seeing other replies: Oracle seems already to have lifted limitations, as someone else provides a complete query in a string in his example. Your example however is different as your Mysql variable is inside an inline view. Regrettably (and don't ask me what the limitations are, I really don't know) inline views do not work exactly the same way in PL/SQL as they work in ordinary sql. You could try to do it this way (and fool the compiler) (select mysql) a. If that doesn't work try to read up on ref cursors on Thomas Kytes' website (http://osi.oracle.com/~tkyte/ResultSets/index.htm[l?])

PS dynamic sql is the dbms_sql package, it is standard Oracle.

PPS this calls for a political answer. You must admit Oracle has many features (row level locking and the like) MySQL doesn't have. Yes, somehow you pay for robustness, and you can't play around indefinitely anymore. I know sqlserver a bit better than MySQL and I dislike it for it's inherent instability. Oracle exists since 1979, and was the first commercial sql implementation (even before DB2), and was created in an era PC's and the web didn't even exist. That heritage will always show. SqlServer is now in 7th release and it still has not all the features Oracle has. Anyway, usually new Oracle features come with new limitations, which in turn are lifted in future releases. So there is hope!

Regards,

Sybrand Bakker, Oracle DBA

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Feb 28 2000 - 00:00:00 CST