Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: Horacle. Ye can't get ye flask!

    OK, I posted this in the Oracle forum, but either they can't answer the question or they are too embarrased to admit how difficult Oracle is to use.

    All I'm trying to do is create a simple procedure for pulling data from a table. In SQL, of course, it would just be

    declare @STATUS_REP_ID as int
    set @STATUS_REP_ID = 335

    select N_STATUS_REP_ID, D_LAUNCH_DATE
    from TBL_CHIP_CHIP_STATUS_REPORT
    where N_STATUS_REP_ID = @STATUS_REP_ID

    But in Oracle? Ye can't get ye flask!
    When I run this:

    declare
    STATUS_REP_ID NUMBER;

    begin
    STATUS_REP_ID := 335;
    select N_STATUS_REP_ID, D_LAUNCH_DATE
    From ISGCOSDEV.TBL_CHIP_CHIP_STATUS_REPORT
    where N_STATUS_REP_ID = STATUS_REP_ID;
    end;

    ...I get an error stating that I have to select into something? Fine, I can select into variables, but then how do I get 10,000 lines of data out to my calling program? My God, I'm thinking that Oracle requires one to use CURSORS to do this one record at a time. That is sick.

    Anybody here dealt with this before?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The (very) little I know about Oracle says that Cursors are actually acceptable programming. Unfortunately, I do not have a PL/SQL book to back that up with. In the end, performance will tell. But, you are probably thinking what will performance be after 2 years of data accreting on the system like so many barnacles? As for that, I have no idea.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    knock yourself out....

    Code:
    --@Q:\Packages\Ben_GetCodeTables_Package.pkg;
    -- ***********************************************************************
    -- *         Prudential Benefits Annual Enrollment System                *
    -- * Description: Collection of procedures to Return requested data to   *
    -- * 		  the Web application					 *
    -- * - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - *
    -- * Modifications Log:                                                  *
    -- * User     Date       Description                                     *
    -- * -------- ---------- ------------------------------------------------*
    -- * x002548  06/09/2000 Initial Version                                 *
    -- * - - - - - - - - - - - - - 2002 Changes - - - - - - - - - - - - - - -*
    -- * x002548  07/27/2001 Remove Procedure Get_ErrorMsg_sp	         *
    -- * x002548  07/27/2001 Add Procedure Get_BGL_AD_D_CAP_sp	         *
    -- * - - - - - - - - - - - - - - - - - - -  - - - - - - - - - - - - - - -*
    
    CREATE OR REPLACE PACKAGE Ben_GetCodeTables_Package
    AS
    -- ***********************************************************************
    -- ********** S T A R T - G L O B A L - D E C L A R A T I O N S **********
    -- ***********************************************************************
    
    TYPE CurRefType     IS REF CURSOR;
    
    PROCEDURE Get_Country_sp        (CountryCur       IN OUT CurRefType);
    
    PROCEDURE Get_StateNames_sp     (StateNamesCur    IN OUT CurRefType);
    
    PROCEDURE Get_EnrXlateTable_sp  (EnrXlateTableCur IN OUT CurRefType);
    
    PROCEDURE Get_PsXlateTable_sp   (PsXlateTableCur  IN OUT CurRefType);
    
    PROCEDURE Get_CovrgCd_sp        (CovrgCdCur       IN OUT CurRefType);
    
    PROCEDURE Get_BenefPlan_sp      (BenefPlanCur     IN OUT CurRefType);
    
    PROCEDURE Get_PlanRelat_sp      (I_PLAN_TYPE	  IN ENR_PLAN_RELATIONSHIPS.PLAN_TYPE%TYPE
    				,I_BENEFIT_PLAN   IN ENR_PLAN_RELATIONSHIPS.BENEFIT_PLAN%TYPE
    				,I_RELATIONSHIP	  IN ENR_PLAN_RELATIONSHIPS.RELATIONSHIP%TYPE
    				,PlanRelatCur     IN OUT CurRefType);
    
    PROCEDURE Get_HlthProvider_sp   (HlthProviderCur  IN OUT CurRefType);
    
    PROCEDURE Get_BGL_AD_D_CAP_sp (BGL_AD_D_CAPCur  IN OUT CurRefType);
    
    
    END Ben_GetCodeTables_Package;
    
    /
    
    CREATE OR REPLACE PACKAGE BODY Ben_GetCodeTables_Package
    AS
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_Country_sp) D E C L A R A T I O N S *****
    -- *********************************************************************
      PROCEDURE Get_Country_sp   (CountryCur      IN OUT CurRefType) IS
      BEGIN
    
       OPEN CountryCur FOR SELECT COUNTRY
    			     ,DESCR
    			     ,DESCRSHORT
                     FROM PS_COUNTRY_TBL
                    WHERE COUNTRY = 'USA';
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_Country_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_StateNames_sp) D E C L A R A T I O N S **
    -- *********************************************************************
      PROCEDURE Get_StateNames_sp  (StateNamesCur   IN OUT CurRefType) IS
      BEGIN
    
       OPEN StateNamesCur FOR SELECT STATE
    			        ,DESCR
    			        ,DESCR_AC
                                FROM PS_STATE_NAMES_TBL
                               WHERE COUNTRY = 'USA'
    			     AND NUMERIC_CD IS NOT NULL 
    			    ORDER BY STATE;
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_StateNames_sp;
    
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_PsXlateTable_sp) D E C L A R A T I O N S*
    -- *********************************************************************
      PROCEDURE Get_PsXlateTable_sp (PsXlateTableCur  IN OUT CurRefType) IS
      BEGIN
    
       OPEN PsXlateTableCur FOR SELECT FIELDNAME
    			          ,FIELDVALUE
    			     	  ,XLATLONGNAME
    			     	  ,XLATSHORTNAME
    	                     FROM PS_XLATTABLE
            	            WHERE LANGUAGE_CD = 'ENG'
    			      AND EFF_STATUS  = 'A';
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_PsXlateTable_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_EnrXlateTable_sp) D E C L A R A T I O N S
    -- *********************************************************************
      PROCEDURE Get_EnrXlateTable_sp (EnrXlateTableCur IN OUT CurRefType) IS
      BEGIN
    
       OPEN EnrXlateTableCur  FOR SELECT FIELDNAME
    			     	    ,FIELDVALUE
    			     	    ,XLATLONGNAME
    			     	    ,XLATSHORTNAME
    	                       FROM ENR_XLATTABLE
            	              WHERE EFF_STATUS  = 'A';
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_EnrXlateTable_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_CovrgCd_sp) D E C L A R A T I O N S  ****
    -- *********************************************************************
      PROCEDURE Get_CovrgCd_sp      (CovrgCdCur       IN OUT CurRefType)  IS
      BEGIN
    
       OPEN CovrgCdCur  FOR SELECT COVRG_CD               
    			      ,DESCR                  
    			      ,DESCRSHORT             
    			      ,SPOUSE_COVERAGE        
    			      ,DOMESTIC_COVERAGE
    			      ,MAX_NUM_OF_DEPS        
    			      ,MIN_NUM_OF_DEPS        
    	                  FROM PS_COVRG_CD_TBL 
            	         WHERE EFF_STATUS  = 'A';
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_CovrgCd_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_BenefPlan_sp) D E C L A R A T I O N S ***
    -- *********************************************************************
      PROCEDURE Get_BenefPlan_sp    (BenefPlanCur     IN OUT CurRefType)  IS
      BEGIN
    
       OPEN BenefPlanCur  FOR SELECT PLAN_TYPE      
    			        ,BENEFIT_PLAN                    
    			        ,DESCR                       
    			        ,DESCRSHORT     
    	                   FROM PS_BENEF_PLAN_TBL;
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_BenefPlan_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_PlanRelat_sp) D E C L A R A T I O N S ***
    -- *********************************************************************
      PROCEDURE Get_PlanRelat_sp    (I_PLAN_TYPE	 IN ENR_PLAN_RELATIONSHIPS.PLAN_TYPE%TYPE
    				,I_BENEFIT_PLAN  IN ENR_PLAN_RELATIONSHIPS.BENEFIT_PLAN%TYPE
    				,I_RELATIONSHIP	 IN ENR_PLAN_RELATIONSHIPS.RELATIONSHIP%TYPE 
    				,PlanRelatCur     IN OUT CurRefType)  IS
      BEGIN
    
       OPEN PlanRelatCur  FOR SELECT PLAN_TYPE      
    				,BENEFIT_PLAN   
    				,COVRG_CD       
    				,RELATIONSHIP   
    				,QA_CERTIFY          
    	                   FROM ENR_PLAN_RELATIONSHIPS
    			  WHERE PLAN_TYPE    = I_PLAN_TYPE
    			    AND BENEFIT_PLAN = I_BENEFIT_PLAN
    			    AND RELATIONSHIP = I_RELATIONSHIP; 
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_PlanRelat_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_HlthProvider_sp) D E C L A R A T I O N S*
    -- *********************************************************************
      PROCEDURE Get_HlthProvider_sp (HlthProviderCur  IN OUT CurRefType)  IS
      BEGIN
    
       OPEN HlthProviderCur FOR SELECT PLAN_TYPE      
    				  ,BENEFIT_PLAN            
    	                      FROM ENR_HLTH_PROVIDER;
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_HlthProvider_sp;
    
    -- *********************************************************************
    -- ***  P R O C E D U R E (Get_BGL_AD_D_CAP_sp) D E C L A R A T I O N S*
    -- *********************************************************************
      PROCEDURE Get_BGL_AD_D_CAP_sp (BGL_AD_D_CAPCur  IN OUT CurRefType)  IS
      BEGIN
    
       OPEN BGL_AD_D_CAPCur FOR SELECT PLAN_TYPE      
    				  ,BENEFIT_PLAN
           				  ,VALID_OTHER_PLAN
           				  ,CAPPED_PLAN     
           				  ,CHARITY         
    	                      FROM ENR_BGL_AD_D_CAP;
    
       EXCEPTION
         WHEN OTHERS THEN
    	         RAISE;
    
      END Get_BGL_AD_D_CAP_sp;
    
    
    END Ben_GetCodeTables_Package;
    /
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Most excellent, Brett! Well, mostly excellent, anyway. This compiled without error, but how do I get the data to a Crystal Report? Do I have to pass a cursor object to the procedure when I call it?

    -----------------------------------------------
    CREATE OR REPLACE PACKAGE REPORTS_PACKAGE
    AS
    TYPE CurRefType IS REF CURSOR;
    PROCEDURE SP_CHIP_STATUS_REPORT(REPORT_CURSOR IN OUT CurRefType);
    END REPORTS_PACKAGE;
    /
    CREATE OR REPLACE PACKAGE BODY REPORTS_PACKAGE
    AS
    PROCEDURE SP_CHIP_STATUS_REPORT(REPORT_CURSOR IN OUT CurRefType) IS
    BEGIN
    OPEN REPORT_CURSOR FOR SELECT N_STATUS_REP_ID, D_LAUNCH_DATE FROM TBL_CHIP_CHIP_STATUS_REPORT WHERE N_STATUS_REP_ID = 335;

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;

    END SP_CHIP_STATUS_REPORT;

    END REPORTS_PACKAGE;
    /
    --------------------------------------------------------------
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I....do not know....

    We have Java call this from websphere

    When I need to create reports I did...(See attachment)
    Attached Files Attached Files
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How does anybody write apps for this F-ing thing? I can't believe anybody would choose this over SQL Server. It's certainly no faster that I can see.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    How does anybody write apps for this F-ing thing? I can't believe anybody would choose this over SQL Server. It's certainly no faster that I can see.

    My Company just blocked dbforums...that are using some shlep 3rd party vendor...

    scrubs...next they'll be block m$

    Yo blind dude...you jry it yet?

    It should scream....

    Don't let the cursors fool you...internally they're different...

    Just like a rs, but SQL delivers the data whethere you use it or not...if crytals whats chucks at a time (what's the most effecient network packet) than that's what they'll get...

    Explore the opportunity...it's a whole other world..

    and the still use st based methods...just communication is a little different...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oracle cursors are NOT like MS-SQL cursors in many respects... In this particular case, they behave more like a handle to a recordset than anything else (think of them like an ADO recordset instead of an MS-SQL cursor).

    -PatP

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett, the client where I'm booked right now just blocked dbforums too.

    Sheesh.

    What problem do these companies have with free advice from subject matter experts?

    And no, I'm still having trouble with that basic select procedure, but I had to put it aside for a while and work on table design and a dotnet ETL program.

    When I pick it up again I'll let you know how it goes.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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