Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Unanswered: newbie pl/sql outputs select results

    Hi,
    I want to write a PL/SQL search engine that does some complex checking of various tables for a client's website. I could write it all in a PHP $sql = "SELECT ..." but I want to put it the sql into PL/SQL and am having trouble figuring out how PL/SQL outputs results.

    Basically, I want to call the procedure with some keywords and have it return the results. Something like this:

    create package jonsearch is

    procedure getrecords(kw IN varchar, results OUT ???) is
    begin
    --complex sql goes here
    end;

    end jonsearch;

    jonsearch.getrecords("keywords") would return the results just like select * from table would return results.

    My trouble is that every tutorial I have read relies on dbms_output.put_line to output data. I want to output the results as a set, with an output variable, but I can't find a tutorial that shows how to use output variables.

    Any help, even pointing me to a tutorial, would be great.

    thanks,

    Jon

  2. #2
    Join Date
    Jun 2003
    Posts
    20
    It turns out what I was looking for is called a REF CURSOR. I needed to create a package that defines this reference cursor, and then use that as my output variable.

    This is described here, if anyone is interested:

    http://www.oracle-base.com/Articles/...Recordsets.asp

  3. #3
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Not understanding...

    How are you wanting to output the variables? If you want to simply return the values, you will use the DBMS_OUTPUT package. If you are returning to Apache through the modplsql module, you will use the HTP and HTF packages.

    As to REF Cursors, here is a Reader's Digest version:

    http://www.dbforums.com/t974133.html

    JoeB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •