CODEWITHSUNDEEP
sqloracle-databasestored-proceduresplsqloracle12c
jdmneon
jdmneon

Reputation: 474

Returning a custom type in a PL/SQL Stored Procedure

My goal is to write either a function or a stored procedure in PL/SQL that returns a table for the user to immediately view. I have dozens of history tables and want to give myself a snapshot of a single users history across all of the tables. but I am running into an error and I am not sure if this is even the best way to do this.

My current approach is to gather all of the information from the tables into a custom "summary table" type and then return it. However I am getting errors when using my custom "userTable" as the type of my out parameter

CREATE OR REPLACE PROCEDURE GetUserSnapShot(myid in Number, numdays in Number,
 myTable out userTable)
AS 
BEGIN
  /*maybe insert a row into the table here?*/
  myTable := null;
END;


DECLARE
   TYPE userChanges IS RECORD (
       historyTable VARCHAR(100),
       historyId NUMBER,
       changeType VARCHAR(6),
       changeDate DATE
     );
  TYPE userTable IS TABLE OF userChanges INDEX BY BINARY_INTEGER;
  myTable userTable;
 BEGIN
   GetUserSnapShot(5, 7, myTable);
END;

here is the error I get when trying to run this: identifier 'USERTABLE' must be declared which is confusing to me since I have declared userTable

Upvotes: 2

Views: 2503

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

You've defined the type USERTABLE in your anonymous block, but the procedure doesn't know what's in your anonymous block and thus can't see the type.

I suggest that it would be best for you to use a package so you can put the record type, table type, and the procedure in the package. An example would be:

CREATE PACKAGE MY_PACKAGE AS  -- define the package spec
  TYPE userChanges IS RECORD
    (
    historyTable VARCHAR(100),
    historyId NUMBER,
    changeType VARCHAR(6),
    changeDate DATE
    );

  TYPE userTable IS TABLE OF userChanges
    INDEX BY BINARY_INTEGER;

  PROCEDURE GetUserSnapShot(myid    in     Number,
                            numdays in     Number,
                            myTable in out userTable);
END MY_PACKAGE;

CREATE PACKAGE BODY MY_PACKAGE AS  -- define the package body
  PROCEDURE GetUserSnapShot(myid    in     Number,
                            numdays in     Number,
                            myTable in out userTable)
  AS
    rec  userChanges; 
  BEGIN
    rec.historyTable := 'SOMEWHERE';
    rec.historyId    := 1;
    rec.changeType   := 'ABCDEF';
    rec.changeDate   := SYSDATE;

    myTable(1) := rec;
  END;
END MY_PACKAGE;

Now you can reference the packaged procedure and type in your anonymous block as:

DECLARE
  MY_PACKAGE.myTable userTable;
BEGIN
  MY_PACKAGE.GetUserSnapShot(5, 7, myTable);
END;

Best of luck.

Upvotes: 2

Related Questions