Oracle capture variable and do if statement in SPROC

how to execute stored procedure in oracle with input parameter
create stored procedure in oracle for select statement
oracle execute stored procedure with parameters
stored procedure in oracle example with in out parameter
execute stored procedure with multiple parameters oracle
oracle stored procedure example
oracle stored procedure declare variable
pl/sql procedure example with parameters

I am new to oracle and I am trying to create a sproc. Currently I have this:

procedure ListCatalogueEntries(P_CUR out sys_refcursor,
                               P_CATALOGUEID varchar2, P_UNIQUEPRODUCTS varchar2) is

BEGIN
    IF P_UNIQUEPRODUCTS = 'Y' THEN
        OPEN P_CUR FOR
        SELECT 
            corprd.sku        prodcode,
            corprd.rangecode  cor_rangecode,
            corprd.rangename  cor_rangename,
            corprd.prodcode   cor_colourcode,
            corprd.prodname   cor_colourname,
            corprd.sku        cor_partcode,
            corprd.skudesc    cor_partname,
            corprd.proddesc   cor_partdesc,
            corprd.prodspec   cor_partspec,
            e.rangecode cus_rangecode,
            e.rangename cus_rangename,
            e.productcode cus_colourcode,
            e.productname cus_colourname,
            e.buyercode cus_partcode,
            e.skudescription cus_partname,
            e.skudescription cus_partspec                              
        FROM
            ccvw_web_products corprd
        INNER JOIN 
            cc_ob_catalogueentries e
        ON 
            corprd.sku = e.sku
        WHERE 
            nvl(e.catalogueid,P_CATALOGUEID) = P_CATALOGUEID                   
        ORDER BY 
            e.sku;
    ELSE
        OPEN P_CUR FOR
        SELECT 
            corprd.sku        prodcode,
            corprd.rangecode  cor_rangecode,
            corprd.rangename  cor_rangename,
            corprd.prodcode   cor_colourcode,
            corprd.prodname   cor_colourname,
            corprd.sku        cor_partcode,
            corprd.skudesc    cor_partname,
            corprd.proddesc   cor_partdesc,
            corprd.prodspec   cor_partspec,
            e.rangecode cus_rangecode,
            e.rangename cus_rangename,
            e.productcode cus_colourcode,
            e.productname cus_colourname,
            e.buyercode cus_partcode,
            e.skudescription cus_partname,
            e.skudescription cus_partspec                              
        FROM
            ccvw_web_products corprd
        LEFT OUTER JOIN 
            cc_ob_catalogueentries e
        ON 
            corprd.rangecode = e.rangecode
        WHERE 
            nvl(e.catalogueid,P_CATALOGUEID) = P_CATALOGUEID                   
        ORDER BY 
            e.sku;
    END IF;          

END ListCatalogueEntries;

Which does compile. What I am actually trying to achieve is this (This is in MSSQL):

DECLARE @CatalogueId NVARCHAR(50)
DECLARE @Unique NVARCHAR(1)
SET @CatalogueId = 'Test'

SELECT @Unique = uniquecatalogue FROM cc_ob_catalogueheader WHERE Id = @CatalogueId

IF @Unique = 'N'
    SELECT 
        corprd.sku        prodcode,
        corprd.rangecode  cor_rangecode,
        corprd.rangename  cor_rangename,
        corprd.prodcode   cor_colourcode,
        corprd.prodname   cor_colourname,
        corprd.sku        cor_partcode,
        corprd.skudesc    cor_partname,
        corprd.proddesc   cor_partdesc,
        corprd.prodspec   cor_partspec,
        e.rangecode cus_rangecode,
        e.rangename cus_rangename,
        e.productcode cus_colourcode,
        e.productname cus_colourname,
        e.buyercode cus_partcode,
        e.skudescription cus_partname,
        e.skudescription cus_partspec                              
    FROM
        ccvw_web_products corprd
    INNER JOIN 
        cc_ob_catalogueentries e
    ON 
        corprd.sku = e.sku
    WHERE 
        nvl(e.catalogueid,P_CATALOGUEID) = P_CATALOGUEID                   
    ORDER BY 
        e.sku;
ELSE
    SELECT 
        corprd.sku        prodcode,
        corprd.rangecode  cor_rangecode,
        corprd.rangename  cor_rangename,
        corprd.prodcode   cor_colourcode,
        corprd.prodname   cor_colourname,
        corprd.sku        cor_partcode,
        corprd.skudesc    cor_partname,
        corprd.proddesc   cor_partdesc,
        corprd.prodspec   cor_partspec,
        e.rangecode cus_rangecode,
        e.rangename cus_rangename,
        e.productcode cus_colourcode,
        e.productname cus_colourname,
        e.buyercode cus_partcode,
        e.skudescription cus_partname,
        e.skudescription cus_partspec                              
    FROM
        ccvw_web_products corprd
    LEFT OUTER JOIN 
        cc_ob_catalogueentries e
    ON 
        corprd.rangecode = e.rangecode
    WHERE 
        nvl(e.catalogueid,P_CATALOGUEID) = P_CATALOGUEID                   
    ORDER BY 
        e.sku;   

Can someone help me do this?

Kaushik helped me get here, but the solution I have implemented is this:

procedure ListCatalogueEntries(P_CUR out sys_refcursor,
                               P_CATALOGUEID varchar2) IS
V_UNIQUE cc_ob_catalogueheader.uniquecatalogue%type;    
BEGIN
    BEGIN            
        SELECT uniquecatalogue 
        INTO V_UNIQUE 
        FROM cc_ob_catalogueheader 
        WHERE CatalogueId = P_CATALOGUEID;    
    END;

    IF V_UNIQUE = 'Y' THEN
        OPEN P_CUR FOR
        SELECT 
            corprd.sku        prodcode,
            corprd.rangecode  cor_rangecode,
            corprd.rangename  cor_rangename,
            corprd.prodcode   cor_colourcode,
            corprd.prodname   cor_colourname,
            corprd.sku        cor_partcode,
            corprd.skudesc    cor_partname,
            corprd.proddesc   cor_partdesc,
            corprd.prodspec   cor_partspec,
            e.rangecode cus_rangecode,
            e.rangename cus_rangename,
            e.productcode cus_colourcode,
            e.productname cus_colourname,
            e.buyercode cus_partcode,
            e.skudescription cus_partname,
            e.skudescription cus_partspec                              
        FROM
            ccvw_web_products corprd
        INNER JOIN 
            cc_ob_catalogueentries e
        ON 
            corprd.sku = e.sku
        WHERE 
            nvl(e.catalogueid,P_CATALOGUEID) = P_CATALOGUEID                   
        ORDER BY 
            e.sku;
    ELSE
        OPEN P_CUR FOR
        SELECT 
            corprd.sku        prodcode,
            corprd.rangecode  cor_rangecode,
            corprd.rangename  cor_rangename,
            corprd.prodcode   cor_colourcode,
            corprd.prodname   cor_colourname,
            corprd.sku        cor_partcode,
            corprd.skudesc    cor_partname,
            corprd.proddesc   cor_partdesc,
            corprd.prodspec   cor_partspec,
            e.rangecode cus_rangecode,
            e.rangename cus_rangename,
            e.productcode cus_colourcode,
            e.productname cus_colourname,
            e.buyercode cus_partcode,
            e.skudescription cus_partname,
            e.skudescription cus_partspec                              
        FROM
            ccvw_web_products corprd
        LEFT OUTER JOIN 
            cc_ob_catalogueentries e
        ON 
            corprd.rangecode = e.rangecode
        WHERE 
            nvl(e.catalogueid,P_CATALOGUEID) = P_CATALOGUEID                   
        ORDER BY 
            e.sku;
    END IF;          

END ListCatalogueEntries;

Which seems to work as I expected

Developing and Using Stored Procedures, Retrieving Data from a Set Using Cursors and Cursor Variables The SQL statements for creating procedures and functions are CREATE such as name capture that is discussed in "Overview of Stored Procedures". This section will only cover conditional selection and iterative program flow structures, such as IF..​.THEN  You can test the stored procedure with the following EXECUTE statement, just be sure to try different values for time, especially one equal to 0.00 to see its affect. Declare @v float EXECUTE uspCalcVelocity 120.00, 2.00, @v OUTPUT SELECT @v ELSE Optional. Keep in mind the ELSE statement is optional.

have you try

IF P_UNIQUEPRODUCTS = 'Y' THEN
   OPEN P_CUR FOR ...
ELSE 
   OPEN P_CUR FOR ...
END IF;

then use P_CUR

Coding PL/SQL Procedures and Packages, If you plan to call a stored procedure using a stub generated by SQL*Module, Actual parameter can be a constant, initialized variable, literal, or expression. When you do specify the parameter value on the call, it overrides the default value. you might run a function and capture the return value using a session variable: In the second procedure (p3) in the preceding example, the use of the user variables a and b on the right-hand side of the assignment statement assumed that the variables have previously been initialized to some value by the first procedure. If the variables have not been initialized by the first procedure, they would have null values, and the

You should use a local variable with appropriate size to store the output from query with an optional ( though recommended ) handling for NO_DATA_FOUND exception.

 PROCEDURE listcatalogueentries (
      p_cur              OUT SYS_REFCURSOR,
      p_catalogueid      VARCHAR2
 )
      IS

      l_uniqueproducts   VARCHAR2(100);

 BEGIN
 SELECT  uniquecatalogue INTO l_uniqueproducts 
      FROM cc_ob_catalogueheader WHERE Id = p_catalogueid;

      IF
           l_uniqueproducts = 'Y'
      THEN
           OPEN p_cur FOR SELECT corprd.sku prodcode,
                                 corprd.rangecode cor_rangecode,
                                 corprd.rangename cor_rangename,
                                 corprd.prodcode cor_colourcode,
                                 corprd.prodname cor_colourname,
                                 corprd.sku cor_partcode,
                                 corprd.skudesc cor_partname,
                                 corprd.proddesc cor_partdesc,
                                 corprd.prodspec cor_partspec,
                                 e.rangecode cus_rangecode,
                                 e.rangename cus_rangename,
                                 e.productcode cus_colourcode,
                                 e.productname cus_colourname,
                                 e.buyercode cus_partcode,
                                 e.skudescription cus_partname,
                                 e.skudescription cus_partspec
                          FROM ccvw_web_products corprd
                          INNER JOIN cc_ob_catalogueentries e ON corprd.sku = e.sku
                          WHERE nvl(e.catalogueid,p_catalogueid) = p_catalogueid
           ORDER BY e.sku;
      ELSE
           OPEN p_cur FOR SELECT corprd.sku prodcode,
                                 corprd.rangecode cor_rangecode,
                                 corprd.rangename cor_rangename,
                                 corprd.prodcode cor_colourcode,
                                 corprd.prodname cor_colourname,
                                 corprd.sku cor_partcode,
                                 corprd.skudesc cor_partname,
                                 corprd.proddesc cor_partdesc,
                                 corprd.prodspec cor_partspec,
                                 e.rangecode cus_rangecode,
                                 e.rangename cus_rangename,
                                 e.productcode cus_colourcode,
                                 e.productname cus_colourname,
                                 e.buyercode cus_partcode,
                                 e.skudescription cus_partname,
                                 e.skudescription cus_partspec
                          FROM ccvw_web_products corprd
                          LEFT OUTER JOIN cc_ob_catalogueentries e ON corprd.rangecode = e.rangecode
                          WHERE nvl(e.catalogueid,p_catalogueid) = p_catalogueid
           ORDER BY e.sku;
      END IF;

      EXCEPTION 
      WHEN NO_DATA_FOUND
       THEN 
         show_do_something;
 END listcatalogueentries;

Using Stored Procedures (The Java™ Tutorials , A stored procedure is a group of SQL statements that form a logical unit and perform a Does the formal parameter act as a constant or a variable in the stored procedure? Do not close any Statement objects in the Java method of your stored procedure; if you do so, the catch (SQLException e) { JDBCTutorialUtilities. This is because Oracle recently introduced the statement CONTINUE that exits the current iteration of a loop and transfers control to the next iteration. If you developed your procedure inside a package, the procedure package_name.continue would have been protected from such name capture.

6 Performing SQL Operations from PL/SQL, A transaction is a series of SQL data manipulation statements that does a logical If the query might return more than one row, but you do not care about values after Typically, you open a cursor variable by passing it to a stored procedure that you want to commit the audit data even if the operation you are auditing later  To use TimesTen-specific SQL from PL/SQL, execute the SQL statements using the EXECUTE IMMEDIATE statement. This avoids compilation errors. For lists of TimesTen-specific SQL and expressions, see "Compatibility Between TimesTen and Oracle Databases" in Oracle TimesTen Application-Tier Database Cache User's Guide.

Triggers and Stored Procedures, For example, if a SELECT statement does not find any rows in the database, an exception is raised, This value can be captured in the "return_value" variable. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the WHERE or HAVING clauses of the SQL statement. Bind capture is disabled when the STATISTICS_LEVEL initialization parameter is set to BASIC.

Performing SQL Operations from PL/SQL, If a DML statement affects zero rows, the values of the OUT binds after the DML A transaction is a series of SQL data manipulation statements that does a logical Typically, you open a cursor variable by passing it to a stored procedure that you want to commit the audit data even if the operation you are auditing later  For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back. Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters.

Comments
  • Yeah, but I would like it to be like the MSSQL statement (i.e. doing a select into a variable). Can you help me do that?
  • Sorry I dont see that in your question. maybe try a simplified version of your problem so we can focus on the issue
  • It doesn't compile :(
  • Error(655,5): PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type <an identifier> <a double-quoted delimited-identifier> current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.
  • @r3plica : pls check now. Declare statement was mistake. In procedures directly define variables after IS keyword.
  • It needs a begin/end wrapping around the select into statement
  • @r3plica : thats not needed. If you provide table ddls and sample data, I can provide you a working solution. There might be something wrong in your actual code.