locked
How to declare a local variable in Oracle?

    Question

  • Hi,

     

    I want to declare a local variable in Oracle DataSet in reporting service 2005. This is a simple task in SQL Queries

     

    DECLARE @PREV_RUN_DATE_I INT
    IF (@LastMonths = 1)
    BEGIN
    SET @PREV_RUN_DATE_I = @OneMonthsBackDate
    END

     

    How to write this in Oracle.PLEASE NOTE THAT ( @PREV_RUN_DATE_I) is not input parameter

    I tried following but no sucess. Can someone please guide me..

     

    DECLARE Stick out tongueREV_RUN_DATE_I NUMBER

    IF (:LastMonths = 1)
    BEGIN
    SET Stick out tongueREV_RUN_DATE_I := SurpriseneMonthsBackDate
    END

     

     


     

     

    Monday, August 25, 2008 11:35 AM

Answers

  • I have no idea, but I suggest you look at an Oracle forum similar to the one I suggested above. This discussion is very off-topic.

     

    Friday, September 05, 2008 5:43 PM

All replies

  • If you were to "write this in SQL" you would need to use a stored procedure. Is there something similar to stored procedures in Oracle?

     

     

    Code Snippet

    USE [mydatabase]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description: <Description,,>

    -- =============================================

    CREATE PROCEDURE [dbo].[usp_mystoredprocedure]

    -- Add the parameters for the stored procedure here

    @MYPARAMETER varchar(100)

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    SELECT * FROM MYTABLE where MYFIELD = @MYPARAMETER;

    END

     

     

    I would refer you to an Oracle forum similar to this: http://wiki.oracle.com/page/Oracle+Discussion+Forums+FAQ?t=anon

    They can probably give you the proper syntax for what you desire.

     

    Monday, August 25, 2008 2:21 PM
  • Well Thanks for the reply Greg!!! but unfortunately, We don't have permission to create temp table, Sp in Oracle database.

    I must write adhoc queries for this reports

     

    -Thanks,

    Digant

     

    Thursday, August 28, 2008 11:33 AM
  • A stored procedure is not a temporary table. It's a stored query.

     

    Thursday, August 28, 2008 1:00 PM
  •  

    Yes Greg, but We don't have permission to write stored procedure in third party database. We have only readonly access to Database

     

    -Thank You,

    Digant

     

     

    Thursday, September 04, 2008 12:51 PM
  • Well In Sql Reporting you have to use @MyNum1 but for Oracle Reporting you don't have to use :MyNum1  but MyNum1 will be sufficient to declare a local variable.

     

     

     

    Public Function GetSum(ByVal MyNum As Integer,ByVal MyNumRight As Integer) As Integer
    Dim MyNum1 AS Integer = 0

    MyNum1 = MyNum
    MyNum1 = MyNum1 + MyNumRight

    Return MyNum1
    End Function

    Thursday, September 04, 2008 1:40 PM
  • I have no idea, but I suggest you look at an Oracle forum similar to the one I suggested above. This discussion is very off-topic.

     

    Friday, September 05, 2008 5:43 PM