Querying multiple rows from Oracle table using package

Question

I wrote a package to query rows from a table. This select query will call other functions and returns all the rows from table. But when i write a package with all functions and sprocs , my sproc with select statement gives me an error saying i cannot execute without into statement. But if i use into then it will return only one row. How can i retrieve all rows using oracle sp?

    Procedure GetData As 
BEGIN 
Select Jobid, JobName, JobLocation, JobCompany, X(jobid) FROM jobsTable; END GetData; 
END;

I had to change it to following make the error go away:

Procedure GetData As 
  r_Jobid jobsTable.jobid%type; 
  r_JobName jobsTable.jobName%type; 
  r_JobLocation jobsTable.jobLocation%type; 
  r_temp varhar2(10); 
BEGIN

  Select Jobid, JobName, JobLocation, JobCompany, X(jobid) 
   INTO r_jobid, r_jobName, r_jobLocation, r_temp 
   FROM jobsTable; 

END GetData; 

END;

Answer 1

You need to use a Cursor for multiple return results. Take a look at this article on using Cursors for some details.

You could do something like:

DECLARE
  CURSOR myCur IS 
        SELECT Jobid, JobName, JobLocation, JobCompany, X(jobid)
        FROM jobsTable;
BEGIN
  FOR resultRow in myCur
  LOOP
     --do your stuff here
  END LOOP;
END;

Answer 2

This is a better approach to returning multiple rows from a function:

FUNCTION GET_DATA()
RETURN SYS_REFCURSOR IS

results_cursor SYS_REFCURSOR;

BEGIN

  OPEN results_cursor FOR
    SELECT t.jobid, 
           t.jobName, 
           t.joblocation, 
           t.jobcompany, 
           X(t.jobid) 
      FROM JOBSTABLE t; 

  RETURN results_cursor;

END;

I agree with afk though that this doesn't appear to be what you really need to be using. Here's my recommendation for using a cursor:

 CURSOR jobs IS
     SELECT t.jobid, 
            t.jobName, 
            t.joblocation, 
            t.jobcompany, 
            X(t.jobid) 
       FROM JOBSTABLE t; 

  v_row jobs%ROWTYPE; --has to be declared AFTER the cursor to be able to reference the row type

BEGIN

  OPEN jobs;
  FETCH jobs INTO v_row;

  IF jobs%FOUND THEN
    --do stuff here, per row basis
    --access columns in the row using: v_row.jobid/etc
  END IF;

  CLOSE jobs;

END;

Are you aware that this:

Procedure GetData As 
  r_Jobid jobsTable.jobid%type; 
  r_JobName jobsTable.jobName%type; 
  r_JobLocation jobsTable.jobLocation%type; 
  r_temp varhar2(10);

...means you defined local variables? You won't be able to get information out of the procedure. If you do, you'd need parameters, like this:

Procedure GetData(IO_R_JOBID IN OUT JOBSTABLE.JOBID%TYPE,
                  IO_R_JOBNAME IN OUT JOBSTABLE.JOBNAME%TYPE,
                  IO_R_JOBLOCATION IN OUT JOBSTABLE.JOBLOCATION%TYPE,
                  IO_R_TEMP IN OUT VARCHAR2(10)) AS

I use the IO_ to note which parameters are IN/OUT. I'd use IN_ or OUT_ where applicable. But the key here is to define OUT if you want to get a parameter back out.

Also - packages are just logical grouping of procedures & functions, with the ability to define constants scoped to the package. The package itself doesn't execute any SQL - it's still a function or procedure that is executing. God how I wish SQL Server had packages...

Answer 3

You could use a pipelined function. For this example I'm only fetching the ID columns, but you just need to add the others.

CREATE PACKAGE jobsPkg AS
  TYPE jobsDataRec IS RECORD ( jobid jobsTable.jobid%type );
  TYPE jobsDataTab IS TABLE OF jobsDataRec;
  FUNCTION getJobsData RETURN jobsDataTab PIPELINED;
END jobsPkg;
/

CREATE PACKAGE BODY jobsPkg AS
  FUNCTION getJobsData RETURN jobsDataTab PIPELINED
  IS
    output_record  jobsDataRec;
  BEGIN
    FOR input_record IN ( SELECT jobid FROM jobsTable ) LOOP
      output_record.jobid := input_record.jobid;
      PIPE ROW (output_record);
    END LOOP;
  END getJobsData;
END jobsPkg;
/

This function can then be used as a row source:

SELECT * FROM TABLE( jobsPkg.getJobsData );