2

I have a Cursor that gets its data from a script along these lines:

OPEN myCursor FOR
SELECT value1 AS Value1,
  myPackage.function1 (value1, :myId) AS Result1,
  myPackage.function2 (value1, :myId) AS Result2,
  myPackage.function3 (value1, :myId) AS Result3                       
FROM mySchema.myTable
WHERE tpk = :myPk
USING myId, myId, myID

As you can see I use the myId variable 3 times and I have to pass it 3 times in the using statement for it to work. I couldn't find it anywhere, but is there a way I could pass the myId variable only once?

  • 1
    You could use a FROM ... CROSS JOIN (SELECT :myId AS id FROM dual) my and then use my.id in the function calls. Or use a CTE. – ypercubeᵀᴹ Jun 19 '14 at 11:26
  • 2
    @ypercube thanks! So I assume there is no built in way to accomplish this? I tried your suggestion and works great. It is funny how I asked several coworkers and no one suggested doing something like that. I am new with Oracle and your suggestion was of great help, if you post it as an answer I will accept it. – Dzyann Jun 19 '14 at 13:24
  • What you don't say is why you want to do this. Intellectual curiosity or other reason??? You are not really "saving" anything by repeating it or using a different solution to not repeat it. – kevinsky Jun 19 '14 at 16:43
  • @kevinsky, we are working on updating a series of packages. And we are creating procedures to test that after the changes the results are consistent. I have several queries that have Using and receive many parameter repeated. When you have to send several parameters repeated it can be easy to make a mistake, and it is hard to maintain if in the future you have to make changes, like introducing a new parameter in the middle. I work normally with C# and C++ so I reuse code, and I am not used to have to send so many repeated items. That is the reason. – Dzyann Jun 19 '14 at 16:57
6

I don't know of any built-in way to accomplish this but you could use a derived table or a Common Table Expression (CTE or as Oracle likes to call them: subquery_factoring_clause), if your aim is to pass the parameters only once in the USING:

-- derived table
OPEN myCursor FOR
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3                       
    FROM (SELECT :myId AS id, :myPk AS pk FROM dual) my 
      CROSS JOIN 
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId

-- CTE
OPEN myCursor FOR
    WITH my AS
        (SELECT :myId AS id, :myPk AS pk FROM dual)
    SELECT value1 AS Value1,
      myPackage.function1 (value1, my.id) AS Result1,
      myPackage.function2 (value1, my.id) AS Result2,
      myPackage.function3 (value1, my.id) AS Result3                       
    FROM my 
      CROSS JOIN
        mySchema.myTable
    WHERE tpk = my.pk
USING myPk, myId

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.