Date Variable in Oracle
-
Hello,
I need help to write the following query in Oracle. It seems straightforward, however, I am having a tough time. I am using SQLPlus.
In SQL 2005 I would write it as:
DECLARE @date DatetimeSELECT @date = MAX(CreateDate) FROM tbl1SELECT * FROM tbl2WHERE CreationDate = @dateSo basically, I am trying to get maximum date from tbl1 and use it in tbl2.
Thanks for your time!
-
-- Please not that I can't test because I do not have Oracle installed
-- but this is basically how you do it.
-- This is any anonymous block
DECLARE v_date DATE
-- Don't used reserved word "Date"
SELECT MAX(SomeDate) FROM tbl1
INTO v_Date
SELECT * FROM tbl2
WHERE CreationDate = v_Date
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thanks Welsh.
-
Building on Welsh's post, block on sqlplus should look like..
set serverout ondeclare mydate date;beginselect max(CreateDate) into mydate from tbl1;-- do whateve you have to do with variable mydate --end;/Please note, if you want to return values from inside the block you have to put those values in some place, probably a cursor then show them resorting to dbms_output.put_line() function.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
You are correct for I only included an anonymous block as opposed to a procedure.
A Procedure would In include DECLARE PROCEDURE MyProc
AS...
etc...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Welsh Corgi (4/28/2011)A Procedure would In include DECLARE PROCEDURE MyProc
I'm sure you mean "create or replace procedure MyProc as..."
Having said that, script I shared earlier runs in sqlplus as poster asked for.
_____________________________________
Pablo (Paul) BerzukovAuthor of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice. -
Yes, Thank you for correcting me.
CREATE OR REPLACE PROCEDURE procedure_name
[(parameter1 [mode] datatype1.
parameter2 [mode] datatype2,...)]
IS|AS
[local_variable_declarations; ...]
BEGIN
-- actions;
END [procedure_name];
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply