f:(DB server)
cd export_reportsmove *.pdf z:\finance (DMS Server)
move *.csv z:\finance
Exit
Monday, 3 October 2022
file movement from one server to another server
move *.pdf z:\finance (DMS Server)
move *.csv z:\finance
Exit
Friday, 16 September 2022
AS OF TIMESTAMP
SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';
Wednesday, 14 September 2022
decr encr
FUNCTION decr (var1 varchar) RETURN CHAR IS
LEN Number(2) ;
i Number(2) ;
CONV_LET Varchar2(1) ;
CONV_STR Varchar2(30) ;
incr number;
Begin
LEN:=To_number(Length(Var1));
incr:=20;
For i in 1..Len Loop
conv_let:=chr(Ascii(Substr(Var1, i,1)) - (incr+i+len));
conv_str := conv_str||conv_let;
End Loop ;
Return Conv_str;
END;
/
FUNCTION encr (var1 varchar) RETURN CHAR IS
LEN Number(2) ;
i Number(2) ;
CONV_LET Varchar2(1) ;
CONV_STR Varchar2(30) ;
incr number;
Begin
LEN:=To_number(Length(Var1));
incr:=20;
For i in 1..Len Loop
conv_let:=chr(Ascii(Substr(upper(Var1), i,1)) + (incr+i+len));
conv_str := conv_str||conv_let;
End Loop ;
Return Conv_str;
END;
Sunday, 19 June 2022
Number2word in pl/sql conversion
PACKAGE Pkg_number2word is
Function Num2Word_million (pCompCode varchar2, pAmount Number) return varchar2 ;
Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 ;
Function Interprt (pAmount Number) return Varchar2 ;
Function Numbertext (pAmount Number) return Varchar2 ;
End;
/
PACKAGE BODY pkg_number2word is
Function Num2Word_million(pCompCode varchar2, pAmount Number) return Varchar2 is
vWord Varchar2(200) ;
vAmount Number(15,2) ;
vBillionHundred Number(15) Default 0 ;
vBillion Number(15) Default 0 ;
vMillionHundred Number(15) Default 0 ;
vMillion Number(10) Default 0 ;
vHundredThousand Number(12) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
-- vCurrMainUnit Cm001_currency_mst.mainunit%type ;
vCurrMainUnit varchar2(500);
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;
If vBillionHundred >= 1 then
vAmount := vAmount-(vBillionHundred*100000000000) ;
End if;
Select Floor(vAmount/1000000000) Into vBillion From Dual ;
If vBillion >= 1 then
vAmount := vAmount-(vBillion*1000000000) ;
End if;
Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;
If vMillionHundred >= 1 then
vAmount := vAmount-(vMillionHundred*100000000) ;
End if;
Select Floor(vAmount/1000000) Into vMillion From Dual ;
If vMillion >= 1 then
vAmount := vAmount-(vMillion*1000000) ;
End if;
Select Floor(vAmount/100000) Into vHundredThousand From Dual ;
If vHundredThousand >= 1 then
vAmount := vAmount-(vHundredThousand*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vBillionHundred >= 1 then
vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;
If vBillion = 0 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
End if ;
If vBillion >= 1 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
If vMillionHundred >= 1 then
vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;
-----if million is 0 then appends the million
If vMillion = 0 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
End if ;
If vMillion >= 1 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
---------prints the hundred thousand
If vHundredThousand >= 1 then
vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;
-----if thousand is 0 then appends the thousand
If vThousand = 0 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
---------
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'DOLLARS') ;
If vDecimal > 0 then
If vWord is not null then
-- vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
If trim(vWord) ='DOLLARS' then -- Naren added on 20/06/2022
dbms_output.put_line('entered in if dollars');
vWord :=pkg_number2word.Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
Else
vWord := vWord ||' AND '|| pkg_number2word.Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
End If;
Else
vWord := Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;
End if;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return (vWord);
Exception
When Value_Error then
return null ;
End;
Function Num2Word_crore (pCompCode varchar2, pAmount Number) return Varchar2 IS
vWord Varchar2(200) ;
vAmount Number(15,2) ;
vBillionHundred Number(15) Default 0 ;
vBillion Number(15) Default 0 ;
vMillionHundred Number(15) Default 0 ;
vMillion Number(10) Default 0 ;
vHundredThousand Number(12) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
-- vCurrMainUnit Cm001_currency_mst.mainunit%type ;
vCurrMainUnit varchar2(500);
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/100000000000) Into vBillionHundred From Dual ;
If vBillionHundred >= 1 then
vAmount := vAmount-(vBillionHundred*100000000000) ;
End if;
Select Floor(vAmount/1000000000) Into vBillion From Dual ;
If vBillion >= 1 then
vAmount := vAmount-(vBillion*1000000000) ;
End if;
Select Floor(vAmount/100000000) Into vMillionHundred From Dual ;
If vMillionHundred >= 1 then
vAmount := vAmount-(vMillionHundred*100000000) ;
End if;
Select Floor(vAmount/1000000) Into vMillion From Dual ;
If vMillion >= 1 then
vAmount := vAmount-(vMillion*1000000) ;
End if;
Select Floor(vAmount/100000) Into vHundredThousand From Dual ;
If vHundredThousand >= 1 then
vAmount := vAmount-(vHundredThousand*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vBillionHundred >= 1 then
vWord := vWord || Interprt(vBillionHundred)||' HUNDRED ' ;
If vBillion = 0 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
End if ;
If vBillion >= 1 then
vWord := vWord || Interprt(vBillion)||' BILLION ' ;
End if ;
If vMillionHundred >= 1 then
vWord := vWord || Interprt(vMillionHundred)||' HUNDRED ' ;
-----if million is 0 then appends the million
If vMillion = 0 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
End if ;
If vMillion >= 1 then
vWord := vWord || Interprt(vMillion)||' MILLION ' ;
End if ;
---------prints the hundred thousand
If vHundredThousand >= 1 then
vWord := vWord || Interprt(vHundredThousand)||' HUNDRED ' ;
-----if thousand is 0 then appends the thousand
If vThousand = 0 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
---------
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' THOUSAND ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' HUNDRED ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'DOLLARS') ;
If vDecimal > 0 then
If vWord is not null then
vWord := vWord ||' AND '|| Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS') || ' ONLY' ;
Else
vWord := Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'CENTS')||' ONLY' ;
End if;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return (vWord);
Exception
When Value_Error then
return null ;
/* vWord Varchar2(100) ;
vAmount Number(13,2) ;
vCrore Number(10) Default 0 ;
vLakhs Number(10) Default 0 ;
vThousand Number(10) Default 0 ;
vHundred Number(10) Default 0 ;
vOnes Number(2) Default 0 ;
vDecimal Number(2) Default 0 ;
vCurrCode Cm004_comp_mst.curr_code%type ;
vCurrSubUnit Cm001_currency_mst.subunit%type ;
vCurrMainUnit Cm001_currency_mst.mainunit%type ;
BEGIN
vAmount := Abs(pAmount) ;
Select Floor(vAmount/10000000) Into vCrore From Dual ;
If vCrore >= 1 then
vAmount := vAmount-(vCrore*10000000) ;
End if;
Select Floor(vAmount/100000) Into vLakhs From Dual ;
If vLakhs >= 1 then
vAmount := vAmount-(vLakhs*100000) ;
End if;
Select Floor(vAmount/1000) Into vThousand From Dual ;
If vThousand >= 1 then
vAmount := vAmount-(vThousand*1000) ;
End if;
Select Floor(vAmount/100) Into vHundred From Dual ;
If vHundred >= 1 then
vAmount := vAmount-(vHundred*100) ;
End if;
Select (pAmount - trunc(pAmount,0)) * 100 Into vDecimal From Dual ;
vOnes := vAmount - (vDecimal/100) ;
vWord := '' ;
If vCrore >= 1 then
vWord := vWord || Interprt(vCrore)||' Crores ' ;
End if ;
If vLakhs >= 1 then
vWord := vWord || Interprt(vLakhs)||' Lakhs ' ;
End if ;
If vThousand >= 1 then
vWord := vWord || Interprt(vThousand)||' Thousand ' ;
End if ;
If vHundred >= 1 then
vWord := vWord || Interprt(vHundred)||' Hundred ' ;
End if ;
Begin
SELECT a.curr_code INTO vCurrCode
FROM Cm004_comp_mst a
WHERE a.comp_code = pCompCode ;
Exception
When Others Then
vCurrCode := null;
End;
If vCurrCode is not null Then
Begin
SELECT a.subunit, a.mainunit INTO vCurrSubUnit, vCurrMainUnit
FROM Cm001_currency_mst a
WHERE a.curr_code = vCurrCode ;
Exception
When Others Then
vCurrMainUnit := null ;
vCurrSubUnit := null ;
End ;
End If;
vWord := vWord || Interprt(vOnes)||' '||Nvl(vCurrMainUnit, 'Rupees') ;
If vDecimal > 0 then
vWord := vWord || ' And ' || Interprt(vDecimal)||' '||Nvl(vCurrSubUnit, 'Paise') || ' Only' ;
Else
If vWord is not null Then
vWord := vWord || ' ONLY' ;
End If;
End if ;
Return(vWord);
Exception
When Value_Error then
return null ;*/
END;
Function Interprt(pAmount Number) return Varchar2 is
vText Varchar2(1000);
BEGIN
vText := '' ;
If pAmount > 20 then
vText := NumberText(trunc(pAmount,-1)) ;
vText := vText || ' ' || NumberText(pAmount - trunc(pAmount,-1)) ;
Else
vText := NumberText(pAmount) ;
End if ;
Return vText ;
END;
Function Numbertext(pAmount Number) return Varchar2 IS
vText Varchar2(100) ;
BEGIN
Select Decode(pAmount,
1, 'ONE',
2, 'TWO',
3, 'THREE',
4, 'FOUR',
5, 'FIVE',
6, 'SIX',
7, 'SEVEN',
8, 'EIGHT',
9, 'NINE',
10,'TEN',
11,'ELEVEN',
12,'TWELVE',
13,'THIRTEEN',
14,'FOURTEEN',
15,'FIFTEEN',
16,'SIXTEEN',
17,'SEVENTEEN',
18,'EIGHTEEN',
19,'NINETEEN',
20,'TWENTY',
30,'THIRTY',
40,'FORTY',
50,'FIFTY',
60,'SIXTY',
70,'SEVENTY',
80,'EIGHTY',
90,'NINETY',100,'HUNDRED' ) Into vText From Dual ;
Return vText ;
END;
End;
Friday, 27 May 2022
How to call webservice from PLSQL Procedure
create or replace PROCEDURE call_rest_from_plsql_with_params_pwd(p_username varchar2,p_password varchar2)
is
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
value VARCHAR2(1024); -- URL to post to
v_url VARCHAR2(200) := 'http://140.238.163.115:9898/alfupload/services/user/passwordUpdtInst';
-- Post Parameters
v_param VARCHAR2(500) := 'username='||p_username||'&password='||p_password;
--not working '{"username":"123","password":"TEJA123#"}';
--'username=123\&password=TEJA123#'; --'pwd=password123\&core_id=12223\&type=PK\&reason=Test reason';
-- '{"username":"' || p_username || '","password":"' || p_password || '"}';
-- l_param_list := 'msisdn=somenumbers&amount=-100&PreBalance=&partycode=XXXXXX&BillID=&BillNbr=&AccountID=&AccountCode=&SN=';
v_param_length NUMBER := length(v_param);
BEGIN
-- Set up proxy servers if required
-- UTL_HTTP.SET_PROXY('proxy.my-company.com', 'corp.my-company.com');
req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => 'POST');
-- UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Type',
value => 'application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER (r => req,
name => 'Content-Length',
value => v_param_length);
UTL_HTTP.WRITE_TEXT (r => req,
data => v_param); resp := UTL_HTTP.GET_RESPONSE(req);
LOOP
UTL_HTTP.READ_LINE(resp, value, TRUE);
DBMS_OUTPUT.PUT_LINE(value);
END LOOP;
UTL_HTTP.END_RESPONSE(resp);
EXCEPTION
WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(resp);
END call_rest_from_plsql_with_params_pwd;
/
PROCEDURE einvoice_json_response(p_inv_json varchar2,p_inv_cntrl_no number,p_ugandatime varchar2) --,p_data out varchar2)
is
v_responce VARCHAR2(10000);
v_returnCode varchar2(10);
v_req utl_http.req;
v_res utl_http.resp;
v_url varchar2(4000) := 'http://20.20.20.6:9880/efristcs/ws/tcsapp/getInformation';
--v_url varchar2(4000) := 'http://172.16.1.23:9880/efristcs/ws/tcsapp/getInformation';
--v_url varchar2(4000) := 'http://127.0.0.1:9880/efristcs/ws/tcsapp/getInformation';
v_name varchar2(4000);
v_buffer varchar2(4000);
v_data VARCHAR2(20024);
v_einv_no VARCHAR2(50);
v_einv_id VARCHAR2(100);
--v_content varchar2(4000) := '{"comp_code":"'||p_comp||'", "main_code":"'||p_main||'", "doc_no":"'||p_doc_no||'","deviceId":"'||p_deviceId||'", "Name":"'||'vijay-MSG'||'","topicbodytitlemsg":"'
-- ||p_bodytitle||'","topicbodymsg":"'||p_bodymsg||'"}';
v_encoded_content varchar2(32000):= to_base64_encode(p_inv_json) ;--(einvoice_json_content_obj(p_inv_cntrl_no)) ;
/**
latest device no updated --"deviceNo": "TCSd0a2ca1134269031",
*/
v_content varchar2(30000):= '{
"data": {
"content": ''"'||v_encoded_content||'"'',
"dataDescription": {
"codeType": "0",
"encryptCode": "1",
"zipCode": "0"
}
},
"globalInfo": {
"appId": "AP01",
"version": "1.1.20191201",
"dataExchangeId": "72738743847307",
"interfaceCode": "T109",
"requestCode": "TP",
"requestTime":"'||p_ugandatime||'",
"responseCode": "TA",
"userName": "admin",
"deviceMAC": "Ffffffff",
"deviceNo": "TCS179f59889740109",
"tin": "1000072627",
"brn": "",
"taxpayerID": "489774308230155677",
"longitude": "116.397128",
"latitude": "39.916527",
"extendField": {
"responseDateFormat": "dd\/MM\/yyyy",
"responseTimeFormat": "dd\/MM\/yyyy HH:mm:ss"
}
},
"returnStateInfo": {
"returnCode": "",
"returnMessage": ""
}
}';
-- "requestTime": "2021-08-03 04:34:00",
--"'||p_ugandatime||'"'',
v_result varchar2(20);
v_res_cnt number := 0;
c number := 0;
v_error varchar2(1000);
v_cnt number;
v_loopcnt number;
http_failure exception;
pragma exception_init(http_failure, -29273);
v_dummy varchar2(32000);
v_decoded_content varchar2(32000):=null;
Begin
dbms_output.put_line('v_content@@'||v_content);
--v_dummy:=einvoice_json_content_obj(p_inv_cntrl_no);
--dbms_output.put_line(v_dummy);
--for i in 1..12 loop
Begin
v_req := utl_http.begin_request(v_url, 'POST',' HTTP/1.1');
utl_http.set_transfer_timeout(15);
utl_http.set_header(v_req, 'content-type', 'application/json');
utl_http.set_header(v_req, 'Content-Length', length(v_content));
utl_http.write_text(v_req, v_content);
v_res := utl_http.get_response(v_req);
utl_http.read_text(v_res, v_data);
utl_http.end_response(v_res);
--dbms_output.put_line(i);
dbms_output.put_line(v_data);
v_decoded_content := from_base64_decode(v_data);
--dbms_output.put_line(from_base64_decode(v_data));
--dbms_output.put_line(v_decoded_content);
--p_data :=v_decoded_content;
EXCEPTION
WHEN UTL_HTTP.transfer_timeout
THEN
DBMS_OUTPUT.put_line ('Transfer timeout');
WHEN http_failure
THEN
DBMS_OUTPUT.put_line ('HTTP failure - timeout?');
-- RAISE;
END;
--end loop;
Begin
INSERT INTO E_invoice_error_log(UNIT_CODE,DOC_NO,ERR_STR,CRTD_DT,JSON_STR)
VALUES('INV', p_inv_cntrl_no, v_data, sysdate,p_inv_json);
Exception When Others Then
null;
End;
Begin
select to_char(regexp_substr(v_data,'"returnCode":\s*"([^"]*?)"',1,1,'',1))
into v_returnCode
from dual;
Exception when others then
null;
End;
If v_returnCode ='00' then
Begin
--select to_char(regexp_substr(inv_doc,'"invoiceNo":\s*"([^"]*?)"',1,1,'',1)) as einv_no
--,to_char(regexp_substr(inv_doc,'"invoiceId":\s*"([^"]*?)"',1,1,'',1)) as einv_id from Pos_Einv_Doc;
insert into Pos_Einv_Doc (einv_doc_id,inv_cntrl_no,inv_doc,crtd_by,crtd_dt)
values (Pos_Einv_Doc_seq.nextval,p_inv_cntrl_no,v_decoded_content,'SATHGURU',sysdate);
Exception When Others Then
dbms_output.put_line(' Error occured while insertion '||sqlerrm);
End;
Begin
Begin
/select to_char(regexp_substr(inv_doc,'"invoiceNo":\s"([^"]*?)"',1,1,'',1)) --einv_no
,to_char(regexp_substr(inv_doc,'"invoiceId":\s*"([^"]*?)"',1,1,'',1)) -- einv_id
into v_einv_no,v_einv_id
from Pos_Einv_Doc
where inv_cntrl_no = p_inv_cntrl_no;*/
select to_char(regexp_substr(v_decoded_content,'"invoiceNo":\s*"([^"]*?)"',1,1,'',1))
,to_char(regexp_substr(v_decoded_content,'"invoiceId":\s*"([^"]*?)"',1,1,'',1))
into v_einv_no,v_einv_id
from dual;
Exception when others then
null;
End;
update pos_invoice_hdr a set a.einv_no= v_einv_no ,a.einv_id = v_einv_id
where inv_cntrl_no = p_inv_cntrl_no;
End;
/*Begin
--select to_char(regexp_substr(inv_doc,'"invoiceNo":\s*"([^"]*?)"',1,1,'',1)) as einv_no
--,to_char(regexp_substr(inv_doc,'"invoiceId":\s*"([^"]*?)"',1,1,'',1)) as einv_id from Pos_Einv_Doc;
insert into CE_INV_EINVOICE (einv_doc_id,inv_cntrl_no,inv_doc,crtd_by,crtd_dt)
INSERT INTO CE_INV_EINVOICE(COMP_CODE, MAIN_CODE, UNIT_CODE, FIN_YEAR, INV_NO, INV_DT, CRTD_BY, CRTD_DT, TRNHDR_ID
values (Pos_Einv_Doc_seq.nextval,p_inv_cntrl_no,v_decoded_content,'SATHGURU',sysdate);
Exception When Others Then
dbms_output.put_line(' Error occured while insertion '||sqlerrm);
End;*/
End If; -- return code =00 ends
commit;
End einvoice_json_response;
Sunday, 13 February 2022
dba scheduler jobs
begin
dbms_scheduler.create_job (
job_name => 'Pos_acct_prod_dtl_vu_mv',
job_type => 'PLSQL_BLOCK',
job_action => 'Begin DBMS_MVIEW.REFRESH(''schemaname.Pos_acct_prod_dtl_vu_mv'',atomic_refresh=>false); End;',
start_date => '14-feb-2022 09:00:00 AM',
repeat_interval => 'FREQ=DAILY;INTERVAL=30',
enabled => TRUE);
END;
/
every 30 min
begin
dbms_scheduler.create_job (
job_name => 'Pos_acct_prod_dtl_vu_mv',
job_type => 'PLSQL_BLOCK',
job_action => 'Begin DBMS_MVIEW.REFRESH(''schema_name.Pos_acct_prod_dtl_vu_mv'',atomic_refresh=>false); End;',
start_date => '14-feb-2022 09:07:00 AM',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
enabled => TRUE);
END;
/
-- daily 3AM job
begin
dbms_scheduler.create_job (
job_name => 'run_load_sales',
job_type => 'STORED_PROCEDURE',
job_action => 'system.load_sales',
start_date => '01-MAR-2010 03:00:00 AM',
repeat_interval => 'FREQ=DAILY',
enabled => TRUE);
END;
--- 9pm
Begin
DBMS_SCHEDULER.drop_job (job_name => 'SEND_PRICE_ALERT_9PM');
End;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'SEND_PRICE_ALERT_9PM',
job_type => 'PLSQL_BLOCK',
job_action => 'Begin automail_price_alert_pkg.JREP_SEND_PRICE_ALERT_9PM(''55'',trunc(sysdate));
automail_price_alert_pkg.JREP_SEND_PRICE_ALERT_ALL_UNITS_9PM(''55'',trunc(sysdate));
END;',
start_date => '15-JUL-2022 09:00:00 PM',
repeat_interval => 'FREQ=DAILY',
end_date => NULL,
enabled => TRUE,
comments => '');
END;
----------week---
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'AUTO_POCOSTPIRCE_REP',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SEND_PO_COSTPIRCE_CHANGE_EXCEL; end;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=WEEKLY;byday=MON;BYHOUR=6;byminute=00;bysecond=0;',
enabled => TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'auto_stktrf_req_daily_4pm_octdec22_1',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
auto_stktrf_req_daily_4pm_octdec22;
end;',
start_date => '04-OCT-2022 04:00:00 PM',
repeat_interval => 'FREQ=DAILY',
end_date => '31-DEC-2022 06:00:00 PM',
enabled => TRUE,
comments => '');
END;
/
------ to change time only--
begin
dbms_scheduler.set_attribute (
name => 'AUTO_STKTRF_REQ_DAILY_JOB',
attribute => 'repeat_interval',
value => 'freq=daily; byhour=22');
end;
/
exec dbms_scheduler.enable('SEND_PRICE_ALERT_4PM');
Monday, 7 February 2022
unique constraint on a column with duplicate values
CREATE INDEX PIPURINV_COMPSUPINVNO_IN ON oa_jk_purinv.pi_pur_invoice_hdr ( comp_code,sup_code,purinv_no)
/
ALTER TABLE oa_jk_purinv.pi_pur_invoice_hdr
ADD CONSTRAINT PIPURINV_COMPSUPINVNO_UK UNIQUE ( comp_code,sup_code,purinv_no) ENABLE NOVALIDATE
Subscribe to:
Posts (Atom)
-
Cursors And Bulk Collect CURSORS Cursor is a pointer to memory location which is called as context area which contains the informatio...
-
What is Collection? A Collection is an ordered group of elements of particular data types. It can be a collection of simple data type or...
-
https://download.cnet.com/Excel-to-Oracle/3000-2077_4-10537733.html
