Oracle Procedures -Select/Insert Into - Need Multiple Results to Return -

Question

I've seen questions similar to this but I can't seem to get this to properly work. I'm working on a procedure where it's pretty much acts as a simple select statement. Closures is the name of the main table. I'm using TOAD & APEX if that information is needed.

Tbl_Web_Closures
(
RECORDID          NUMBER,
BEGDATE           DATE,
ENDDATE           DATE,
STLOCATION        VARCHAR2(50 BYTE),
DESCRIPTION       VARCHAR2(400 BYTE),
STATUS            NUMBER,
TYPEOF            NUMBER,
INACTIVEDATE      DATE,
AGENCY            NUMBER,
CITY              NUMBER,
ROUTEDESCRIPTION  VARCHAR2(4000 BYTE),
NAMEEVENT         VARCHAR2(40 BYTE),
TIMESTART         DATE,
TIMEEND           DATE

I've gotten a clear understanding of the fact hat you can't use a simple select statement in a procedure and that you need some form of an into statement.

I have attempted many variations and still can't get the results I am looking for. I need not just one result but all that match the query.

Which is

SELECT "STLOCATION" AS "Location",
   "BEGDATE" AS "Begin Date",
   "ENDDATE" AS "End Date",
   "TBL_WEBCLOSURES"."DESCRIPTION" AS "Description",
   "TBL_TYPE"."TYPESIT" AS "Type",
   "TBL_CITY"."CITY" AS "City"

    FROM "TBL_CITY" "TBL_CITY",
   "TBL_TYPE" "TBL_TYPE",
   "TBL_STATUS" "TBL_STATUS",
   "TBL_AGENCY" "TBL_AGENCY",
   "TBL_WEBCLOSURES" "TBL_WEBCLOSURES"

    WHERE     "TBL_AGENCY"."AGENCYID" = "TBL_WEBCLOSURES"."AGENCY"
   AND "TBL_STATUS"."STATUSID" = "TBL_WEBCLOSURES"."STATUS"
   AND "TBL_TYPE"."TYPEID" = "TBL_WEBCLOSURES"."TYPEOF"
   AND "TBL_CITY"."CITYID" = "TBL_WEBCLOSURES"."CITY"
   AND "TBL_WEBCLOSURES".AGENCY = '2'
   AND ENDDATE = TRUNC (SYSDATE + 5);

I've tried numerous variations including

create or replace procedure            my_Procedure (v_my_var in  char default null
                                    ,cv_1     OUT TBL_WEBCLOSURES%rowtype) as rt TBL_WEBCLOSURES%rowtype;
 begin


 SELECT *
 into   rt
 FROM TBL_WEBCLOSURES 
 WHERE  ENDDATE = TRUNC (SYSDATE + 5);



 END my_Procedure;​

Another one

  CREATE OR REPLACE PROCEDURE "ABC6" (
   stlocation      IN     VARCHAR2,
 o_stlocation       OUT TBL_WEBCLOSURES%ROWTYPE,
   BEGDATE         IN     DATE,
 o_begdate          OUT TBL_WEBCLOSURES%ROWTYPE,
   ENDDATE         IN     DATE,
 o_enddate          OUT TBL_WEBCLOSURES%ROWTYPE,
  DESCRIPTION     IN     VARCHAR2,
 o_description      OUT TBL_WEBCLOSURES%ROWTYPE,
   TYPESIT         IN     NUMBER,
 o_typesit          OUT TBL_WEBCLOSURES%ROWTYPE,
   CITY            IN     NUMBER,
 o_city             OUT TBL_WEBCLOSURES%ROWTYPE)
IS
BEGIN
   SELECT "STLOCATION" AS "Location",
      "BEGDATE" AS "Begin Date",
      "ENDDATE" AS "End Date",
      "TBL_WEBCLOSURES"."DESCRIPTION" AS "Description",
      "TBL_TYPE"."TYPESIT" AS "Type"
      "TBL_CITY"."CITY" AS "City"

  INSERT INTO o_stlocation,
      o_begdate,
      o_enddate,
      o_description,
      o_typesit,
      o_city
 FROM "TBL_CITY" "TBL_CITY",
   "TBL_TYPE" "TBL_TYPE",
   "TBL_STATUS" "TBL_STATUS",
   "TBL_AGENCY" "TBL_AGENCY",
   "TBL_WEBCLOSURES" "TBL_WEBCLOSURES"
 WHERE     "TBL_AGENCY"."AGENCYID" = "TBL_WEBCLOSURES"."AGENCY"
       AND "TBL_STATUS"."STATUSID" = "TBL_WEBCLOSURES"."STATUS"
       AND "TBL_TYPE"."TYPEID" = "TBL_WEBCLOSURES"."TYPEOF"
       AND "TBL_CITY"."CITYID" = "TBL_WEBCLOSURES"."CITY"
       AND "TBL_WEBCLOSURES".AGENCY = '2'
       AND ENDDATE = TRUNC (SYSDATE + 5);
    END;

I've modified, tried different ways, etc. I just can't seem to get it to come out correctly.

Answer 1

I think I pretty much figured it out.

CREATE OR REPLACE PROCEDURE roadclosure_notif4
IS
  road_refcur2   SYS_REFCURSOR;
  v_stlocation   TBL_WEBCLOSURES.stlocation%TYPE;
  v_enddate      TBL_WEBCLOSURES.DESCRIPTION%TYPE;
  BEGIN
  OPEN road_refcur2 FOR
     'SELECT "STLOCATION",

     "ENDDATE"

FROM "TBL_CITY" "TBL_CITY",
   "TBL_TYPE" "TBL_TYPE",
   "TBL_STATUS" "TBL_STATUS",
   "TBL_AGENCY" "TBL_AGENCY",
   "TBL_WEBCLOSURES" "TBL_WEBCLOSURES"
WHERE     "TBL_AGENCY"."AGENCYID" = "TBL_WEBCLOSURES"."AGENCY"
   AND "TBL_STATUS"."STATUSID" = "TBL_WEBCLOSURES"."STATUS"
   AND "TBL_TYPE"."TYPEID" = "TBL_WEBCLOSURES"."TYPEOF"
   AND "TBL_CITY"."CITYID" = "TBL_WEBCLOSURES"."CITY"
   AND "TBL_WEBCLOSURES".AGENCY = 2
   AND ENDDATE = TRUNC (SYSDATE + 5)';

  DBMS_OUTPUT.PUT_LINE ('Location    End Date');


 LOOP
  FETCH road_refcur2 INTO v_stlocation, v_enddate;

  EXIT WHEN road_refcur2%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE (v_stlocation || '     ' || v_enddate);
 END LOOP;

 CLOSE road_refcur2;
END;
 /