KLast date of month 4

[Cretin]

I am trying to create a function. When a record comes in it and is processed We need to calculate the last date of the previous month. That works but when I try to put it in a function so I can pass that date to another program on compile I get the message: ORA-00922 missing or invalid option. I have tried numerous things. I am not a programmer however all of our developers are buried so they gave this to me. Any help I get is appreciated. Below is my code:

set serveroutput on

create or replace
function date_function(v_out in VARCHAR2, v_date in DATE)
return CHAR




-- 1
select trunc(sysdate, 'mon') -1 from dual;

-- 2

declare
v_date date;
v_out varchar2(10);
begin

v_date := to_date('20100820', 'yyyymmdd');
v_out := to_char(trunc(v_date, 'mon') -1, 'mm/dd/yyyy');
dbms_output.put_line(to_char(v_date, 'mm/dd/yyyy'));
dbms_output.put_line(v_out);
return v_out;
end;



Cretin

 

[LKBrwnDBA]

Try this:

CREATE OR REPLACE FUNCTION date_function (p_date IN DATE)
   RETURN DATE IS
BEGIN
   RETURN LAST_DAY (ADD_MONTHS (p_date, -1));
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Cretin]

That works like a champ Thank you , however now comes the hard part.

Let me start off by apologizing I don't pick the languages I only do what I am told.

I need to take the date from the sql and pass it to a .bat program. Basically I define a variable in a .bat that is called %enddate%. I need to call this SQL to find out the last date of the previous month using the SQL and send that date back to the .bat. Thank you for any help you can give me.

Cretin

 

[SantaMufasa]

...and send that date back to the .bat.

By what method do you intend to send the date back to the .bat?...By writing out a new copy of the .bat file?...By using a bind variable?...Some other means?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Cretin]

Is what we intend to do is the following

We have a .bat that has code. Then at some point we will do some basic if logic

That would be as follows:
If account_number = 1111
Execute SQL
Update file
then it will move on. That particular customer needs an end date of the last day of the previous month. The SQL will get the date within the .bat. pass the date to the .bat so it can update the file. It would pass the date to the .bat by means of a variable in the .bat which will use that variable to update the file.



Cretin

 

[taupirho]

This is quite close to what you want I think. Example below shows some sql being called from a DOS batch file and the output of the SQL (i.e a date) is echo'd to the screen.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\WINDOWS\system32>cd c:\tom\test

C:\tom\test>type tom.sql
set timing off
set feedback off
set pages 0
select last_day(add_months(sysdate,-1)) from dual;
exit

C:\tom\test>type tom.bat
FOR /F "usebackq delims=!" %%i IN (`sqlplus -s user/pass@db @tom`) DO set xr
esult=%%i
echo %xresult%

C:\tom\test>tom

C:\tom\test>FOR /F "usebackq delims=!" %i IN (`sqlplus -s user/pass@db`) DO set xresult=%i

C:\tom\test>set xresult=31-AUG-10

C:\tom\test>echo 31-AUG-10
31-AUG-10

C:\tom\test>


In order to understand recursion, you must first understand recursion.

 

[Cretin]

When I do this it
C:\tom\test>type tom.batFOR /F "usebackq delims=!" %%i IN (`sqlplus -s user/pass@db @tom`) DO set xresult=%%iecho %xresult%

It echos on the screen: sqlplus -s user/pass@db @tom

When I do this C:\tom\test>FOR /F "usebackq delims=!" %i IN (`sqlplus -s user/pass@db`) DO set xresult=%i
I get an error message that the i is unexpected.

Is what I am trying to do is run a .bat file. Within that file it runs an SQL that gets me my date (The SQL I have working) then to pass the date back to the .bat the SQL is in so it can pass it to another program later.

Cretin

 

[Cretin]

This is what I got I guess I can't find what I am doing wrong.

FOR /F "usebackq delims=!" %%i IN ('sqlplus -s user/password@database @DATE_FUNCTION.sql') DO set result=%%i

echo %result%

FOR /F "usebackq delims=!" %%i IN (`sqlplus -s user/passwored@database`) DO set result=%%i



echo %result%

The first section only echos the code in the parenthesis the 2nd tells me the i is unexpected.

Cretin

 

[Cretin]

sorry I was not entirely accurate ther 2nd part with 2 % hangs there with 1 % it gives me the i is unexpected.

Cretin

 

[LKBrwnDBA]

Please use the [ code ] [ /code ] tags to format your scripts.

The sqlplus (the whole statement) that is inside the parenthesis must be quoted with BACK ticks! (`` not '').

usebackq =     Specify `back quotes`
               the command_to_process is placed in `BACK quotes`
               instead of 'straight' quotes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Cretin]

Ok thank you I did get it to work. Is what I was doing is I had set the SQL to be a function within the database. Is what I needed to do is set it up as a procedure on the server.

Next step it to get the main .bat file to call this .bat file in.

Cretin

 

[Cretin]

I got the call to work as well that was easy, thanks to everyone for their help. I am sure you will hear from me again.

Cretin

 

[KenCunningham]

Cretin - I'm sure you're welcome but running the risk of advocacy for my friends, perhaps one or two of them deserve recognition for their guidance in the form of a little purple star as is customary in these fora?

The internet - allowing those who don't know what they're talking about to have their say.

 

[Beilstwh]

If he didn't give a star, i will. Nice job and good to know.

Bill
Lead Application Developer
New York State, USA

 

[Cretin]

I agree that there are Kudos involved with all who helped on this. It was much appreciated. I know it's not easy helping on this kind of older stuff for someone like me who lacks experience. I still have a little bit to go to get this where I need it to be.

Cretin

 

[Cretin]

The two things I am still working on is an if statement to check for multiple conditions ex:

 If cust_id == 1111 or cust_id = 2222 
    then goto date_calc

I have been googling but as of yet have not found an example of the above.

Also once I run the SQL I need to retrieve it into the batch file field to use it ex:

 IF %v_customer_id:`0,4% = 1111 or 2222 (
  echo run last_date>> %V_INPUT_PATH%\UH_%V_LOG_DATE_TIME%.log
  sqlplus  -s user/password@db@last_date
  )

echo exec program(%V_RUN_DATE%,%v_termdate%,'%v_customer%')> %V_SQL_program%
  echo exit>> %V_SQL_program%

I need to get the date into the field %termdate%

Cretin

 

[SantaMufasa]

I am still working on...an if statement to check for multiple conditions...

Do you mean like:

If cust_id in (1111,2222) then...

I need to retrieve it into the batch file field

It sounds like you want to use a sys_refcursor. You can see an example at this link.



Let us know if these leads resolve your needs. (Ooooh, that rhymes. <grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Cretin]

This is what I have so far

 sqlplus user/password@database @last_date.sql >_query.tmp
set /p termdate =< _query.tmp

The file _query.tmp is put out to the directory however it does not appear to be picked up by termdate.

I am trying to output the results of the query to the file _query.tmp and then setting termdate to equal that.

Cretin

 

[Cretin]

I actually may have it.

 sqlplus user/password@database @last_date.sql > _query.tmpset /p termdate = < _query.tmp

darn spaces.

Cretin

 

[Cretin]

It tests well I am planning to put into production next week, thanks for the help i let you know

Cretin