For oracle SQL to create function, tip package or function in the invalid state-CodePudding

- create function
The create or replace function dateconvert (flowdate in varchar)
Return a varchar
Is saledate varchar.

The begin

Str1 varchar (6);
Str2 varchar (6);
Str3 varchar (6);
Lenstr1 number (5);
Lenstr2 number (5);
Lenstr3 number (5);

If instr (flowdate, '-', 1, 1) & gt; 1 then

Str1=substr (flowdate, 1, instr (flowdate, '-', 1, 1) - 1);
Str2:=replace (substr (flowdate, instr (flowdate, '-', 1, 1) + 1, 2), '-', ');
Str3:=substr (flowdate, instr (flowdate, '-', 1, 2) + 1, 2);
Lenstr1:=length (str1);
Lenstr2:=length (str2);
Lenstr3:=length (str3);

The case when lenstr3=4 and lenstr2=1 and lenstr1=1
Then saledate:=str3 | | '0' | | str2 | | '0' | | str1;
The when lenstr3=4 and lenstr2=1 and lenstr1=2
Then flowdate:=str3 | | '0' | | str2 | | str1;
The when lenstr3=4 and lenstr2=2 and lenstr1=1
Then saledate:=str3 | | str2 | | '0' | | str1;
The when lenstr1=4 and lenstr2=1 and lenstr1=1
Then flowdate: str1=| | '0' | | str2 | | '0' | | str3;
The when lenstr1=4 and lenstr2=1 and lenstr3=2
Then saledate: str1=| | '0' | | str2 | | str3;
The when lenstr1=4 and lenstr2=2 and lenstr3=1
Then saledate:=str3 | | str2 | | '0' | | str3;
The when lenstr1=2 and lenstr2=2 and lenstr3=1
Then saledate:=str3 | | str2 | | '0' | | str3;
The when lenstr1=2 and lenstr2=1 and lenstr3=1
Then saledate:=str3 | | '0' | | str2 | | '0' | | str3;
The when lenstr1=2 and lenstr2=1 and lenstr3=2
Then saledate:=str3 | | '0' | | str2 | | str3;
The else saledate:=flowdate;
End a case;
Return (saledate);
The else
Saledate:=substr (flowdate, 1, 4) | | '-' | | substr (flowdate, 5, 2) | | '-' substr (flowdate, 7, 2);
Return (saledate);
end if;
End dateconvert;

CodePudding user response:

 
- the problem was a lot of, now give you change the 

The create or replace function dateconvert (p_flowdate in varchar) 
Return a varchar 
Is 

Saledate varchar (30); 
Flowdate varchar (30); 
Str1 varchar (6); 
Str2 varchar (6); 
Str3 varchar (6); 
Lenstr1 number (5); 
Lenstr2 number (5); 
Lenstr3 number (5); 
The begin 



If instr (flowdate, '-', 1, 1) & gt; 1 then 

Str1=substr (flowdate, 1, instr (flowdate, '-', 1, 1) - 1); 
Str2:=replace (substr (flowdate, instr (flowdate, '-', 1, 1) + 1, 2), '-', '); 
Str3:=substr (flowdate, instr (flowdate, '-', 1, 2) + 1, 2); 
Lenstr1:=length (str1); 
Lenstr2:=length (str2); 
Lenstr3:=length (str3); 

The case when lenstr3=4 and lenstr2=1 and lenstr1=1 
Then saledate:=str3 | | '0' | | str2 | | '0' | | str1; 
The when lenstr3=4 and lenstr2=1 and lenstr1=2 
Then flowdate:=str3 | | '0' | | str2 | | str1; 
The when lenstr3=4 and lenstr2=2 and lenstr1=1 
Then saledate:=str3 | | str2 | | '0' | | str1; 
The when lenstr1=4 and lenstr2=1 and lenstr1=1 
Then flowdate: str1=| | '0' | | str2 | | '0' | | str3; 
The when lenstr1=4 and lenstr2=1 and lenstr3=2 
Then saledate: str1=| | '0' | | str2 | | str3; 
The when lenstr1=4 and lenstr2=2 and lenstr3=1 
Then saledate:=str3 | | str2 | | '0' | | str3; 
The when lenstr1=2 and lenstr2=2 and lenstr3=1 
Then saledate:=str3 | | str2 | | '0' | | str3; 
The when lenstr1=2 and lenstr2=1 and lenstr3=1 
Then saledate:=str3 | | '0' | | str2 | | '0' | | str3; 
The when lenstr1=2 and lenstr2=1 and lenstr3=2 
Then saledate:=str3 | | '0' | | str2 | | str3; 
The else saledate:=flowdate; 
End a case; 
Return (saledate); 
The else 
Saledate:=substr (flowdate, 1, 4) | | '-' | | substr (flowdate, 5, 2) | | '-' | | substr (flowdate, 7, 2); 
Return (saledate); 
end if; 
End dateconvert;

CodePudding user response:

Pl/SQL developer, click the invalid objects, you create it will prompt you where wrong,
That's ok, who love will find out compilation errors for you!