SQL PL SQL - Yash-777/oracle-aq-samples GitHub Wiki

PLSQL

XML Input UTC Java UTC
(Platform in CET)
dateFormat.setLenient(true);		 DB UTC
(Convert to CET)		 XML Output CET
1,2 1,2 -> 1,3 1,3 -> 3,5 3,5
2,3 2,3 -> 3,3 3,3 -> 5,5 5,5

timezoneConverter()

--,v_Start_Date_CET out date, v_End_Date_CET out date)
--set serveroutput on; -- View -> Dbms Output : set serveroutput on 
create or replace FUNCTION ShortDay_Interval(v_Start_Date_UTC in date, v_End_Date_UTC in date)
    return number AS
retcode number := 1;

v_start_month VARCHAR2 (10);  v_end_month VARCHAR2 (10);
v_last_sunday_date VARCHAR2 (10);
v_start_date_of_mont VARCHAR2 (10); v_end_date_of_mont VARCHAR2 (10);
v_start_year VARCHAR2 (10); v_end_year VARCHAR2 (10);
v_start_hour VARCHAR2 (10); v_end_hour VARCHAR2 (10);
v_Start_Date_CET date; v_End_Date_CET date; v_DayLight_Date_CET date;
BEGIN
v_Start_Date_CET := v_Start_Date_UTC;
v_End_Date_CET := v_End_Date_UTC;

v_start_month := to_char(trunc( v_Start_Date_UTC ), 'MM');
v_end_month := to_char(trunc( v_End_Date_UTC ), 'MM');
v_start_year := to_char(trunc( v_Start_Date_UTC ), 'YYYY');
v_end_year := to_char(trunc( v_End_Date_UTC ), 'YYYY');
DBMS_OUTPUT.put_line('Year1:' || v_start_year || ', Year2:' || v_end_year );
DBMS_OUTPUT.put_line('Month1:' || v_start_month || ', Month2:' || v_end_month );

-- Month = March(3)
IF ( (v_start_month = '03' AND v_start_month = v_end_month) AND (v_start_year = v_end_year) ) -- Check Year also
  THEN
    DBMS_OUTPUT.put_line ('CET Short Month March');   
    
v_last_sunday_date   := to_char(trunc( next_day(LAST_DAY(v_Start_Date_UTC) - 7, 'sun') ), 'DD');
v_start_date_of_mont := to_char(trunc( v_Start_Date_UTC ), 'DD');
v_end_date_of_mont   := to_char(trunc( v_End_Date_UTC ), 'DD');
DBMS_OUTPUT.put_line('Date1:' || v_start_date_of_mont || ', Date2:' || v_end_date_of_mont || ', Months Last Sunday:' || v_last_sunday_date);

   IF (v_start_date_of_mont = v_end_date_of_mont AND v_end_date_of_mont = v_last_sunday_date) AND (v_start_year = v_end_year) THEN
      DBMS_OUTPUT.put_line ('CET Short Month March - Last Sunday '|| v_last_sunday_date);
      
      v_start_hour := to_char(v_Start_Date_UTC, 'HH24');
      v_end_hour := to_char(v_End_Date_UTC, 'HH24');
      DBMS_OUTPUT.put_line('H1:' || v_start_hour || ', H2:' || v_end_hour );
      
      IF (v_start_hour = '01' AND v_end_hour = '03') then
        DBMS_OUTPUT.put_line ('Change end-hour to 2nd hour by -1 hour:: ' || v_End_Date_CET);
        v_End_Date_CET := v_End_Date_UTC  - 1/24;
        DBMS_OUTPUT.put_line ('Change end-hour to 2nd hour by -1 hour: ' || v_End_Date_CET);
        
        SELECT timezoneConverter(v_End_Date_CET, 'CET', 'UTC') into v_DayLight_Date_CET FROM dual;
        DBMS_OUTPUT.put_line ('v_DayLight_Date_CET End 2nd hour : ' || v_DayLight_Date_CET);
        
        IF (v_DayLight_Date_CET is NULL) then
            retcode := 1;
        ELSE
            retcode := 0;
        END IF;
        
      ELSIF (v_start_hour = '03' AND v_end_hour = '03') then
        DBMS_OUTPUT.put_line ('Change first-hour to 2nd hour by -1 hour:: ' || v_Start_Date_CET);
        v_Start_Date_CET := v_Start_Date_UTC  - 1/24;
        DBMS_OUTPUT.put_line ('Change first-hour to 2nd hour by -1 hour: ' || v_Start_Date_CET);
        
        SELECT timezoneConverter(v_Start_Date_CET, 'CET', 'UTC') into v_DayLight_Date_CET FROM dual;
        DBMS_OUTPUT.put_line ('v_DayLight_Date_CET Start 2nd hour : ' || v_DayLight_Date_CET);
        
        IF (v_DayLight_Date_CET is NULL) then
            retcode := 1;
        ELSE
            retcode := 0;
        END IF;
        
      ELSE 
     
      DBMS_OUTPUT.put_line ('No change in Hours');
      END IF;
      
    ELSE
      DBMS_OUTPUT.put_line ('CET Short Month March - Last Sunday is ' || v_last_sunday_date);
    END IF;
  
  ELSE
    DBMS_OUTPUT.put_line ('Not Equal');
  END IF;

DBMS_OUTPUT.put_line('v_Start_Date_CET:' || v_Start_Date_CET || ', v_End_Date_CET:' || v_End_Date_CET ); 
    RETURN retcode;
EXCEPTION
when others then
    retcode := 0;
    return retcode;
END;
/
DECLARE
  D1 DATE; D2 DATE;
  D3 DATE; D4 DATE;
  v_Return NUMBER;
  l_company_name   VARCHAR2 (25) := 'oraCLE corporatION';
BEGIN
  D1 := to_date('2020-03-29T01:01Z', 'yyyy-mm-dd"T"hh24:mi"Z"'); -- 1,3 -> 1,2
  D2 := to_date('2020-03-29T03:50Z', 'yyyy-mm-dd"T"hh24:mi"Z"');
  D3 := to_date('2020-03-29T03:15Z', 'yyyy-mm-dd"T"hh24:mi"Z"'); -- 3,3 -> 2,3 CET
  D4 := to_date('2020-03-29T03:00Z', 'yyyy-mm-dd"T"hh24:mi"Z"'); -- 3,3 -> 2,3 CET

  v_Return := ShortDay_Interval(v_Start_Date_UTC => D1, v_End_Date_UTC => D2 );
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
  
  v_Return := ShortDay_Interval(v_Start_Date_UTC => D3, v_End_Date_UTC => D4 );
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
  
  DBMS_OUTPUT.put_line (UPPER (l_company_name));
END;

Log Output:

Year1:2020, Year2:2020
Month1:03, Month2:03
CET Short Month March
Date1:29, Date2:29, Months Last Sunday:29
CET Short Month March - Last Sunday 29
H1:01, H2:03
Change end-hour to 2nd hour by -1 hour:: 29-MAR-2020 03:50:00
Change end-hour to 2nd hour by -1 hour: 29-MAR-2020 02:50:00
v_DayLight_Date_CET End 2nd hour : 
v_Start_Date_CET:29-MAR-2020 01:01:00, v_End_Date_CET:29-MAR-2020 02:50:00
v_Return = 1

Year1:2020, Year2:2020
Month1:03, Month2:03
CET Short Month March
Date1:29, Date2:29, Months Last Sunday:29
CET Short Month March - Last Sunday 29
H1:03, H2:03
Change first-hour to 2nd hour by -1 hour:: 29-MAR-2020 03:15:00
Change first-hour to 2nd hour by -1 hour: 29-MAR-2020 02:15:00
v_DayLight_Date_CET Start 2nd hour : 
v_Start_Date_CET:29-MAR-2020 02:15:00, v_End_Date_CET:29-MAR-2020 03:00:00
v_Return = 1

Oracle Basics

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
-- ORA-01810: format code appears twice:: MM is for a month, MI is for a minute

-- DB_Name/SERVICE NAME/instance_name [XE]
-- https://docs.oracle.com/cd/B12037_01/java.101/b10979/urls.htm
-- jdbc:oracle:thin:@<server_host>:1521:<instance_name>
select name from V$database;  -- DB Name
select instance_name from v$instance; --
select sys_context('userenv','instance_name') from dual; -- Current Instance DB Name
select sys_context('userenv','db_name') from dual;

select ora_database_name from dual;
select * from global_name;

-- server_host
select sys_context('userenv', 'server_host') from dual;

-- User Instance
select user from dual;  -- User Name of Current User Instance

-- port : (ADDRESS=(PROTOCOL=TCP)(HOST=10.55.72.14)(PORT=1521))
select * from v$listener_network;

SELECT sys_context('USERENV', 'SID') FROM DUAL;

Procedures and Functions:

variable i number;
variable dbname varchar2(30);
begin
    :i:=dbms_utility.get_parameter_value('db_name',:i,:dbname);
  end;
-- PL/SQL procedure successfully completed.
print dbname;
-- Package Interface
create or replace
PACKAGE PA_SAMPLE
IS
TYPE ref_cur IS  REF  CURSOR;

FUNCTION function_test( cur_ref IN OUT ref_cur )   RETURN NUMBER;
PROCEDURE procedure_test(id IN NUMBER, cur_ref IN OUT ref_cur);	
END PA_SAMPLE;

-- Package Implementation of Interface
create or replace
PACKAGE BODY PA_SAMPLE
IS
FUNCTION function_test( cur_ref  IN OUT ref_cur) RETURN NUMBER
IS
  returnVal PLS_INTEGER         := 1;
  duplicateCount number;
    select count(*) into duplicateCount from YASH_INFO where name like %test%;
    
	if duplicateCount<>0 then 
	  OPEN cur_ref FOR SELECT ID, MSG FROM TABLE_TIME;
	else 
	  OPEN cur_ref for select null, null from dual;
	end if;
  
  RETURN returnVal;           
EXCEPTION
WHEN OTHERS THEN
  RETURN 0;  
END;

PROCEDURE procedure_test ( id IN NUMBER, cur_ref IN OUT ref_cur) 
AS
BEGIN
  OPEN test_OUT FOR SELECT ID, MSG FROM TABLE_TIME;
  -- CLOSE test_OUT;
END;

END PA_SAMPLE;  -- Package spec

Load File form Local Path [C:/Yash/MyServer.p12]

update YASH_INFO t set t.name = 'MyServer.p12', t.BLOBFILE = empty_blob() where ID = 3;
commit;

-- PROCEDURE
DECLARE
   p_parameter_name   VARCHAR2 (100 BYTE) := 'MyServer.p12';
   src_file           BFILE;
   dst_temp_file           BLOB;
   lgh_file           BINARY_INTEGER;
BEGIN
  -- ORA-22285: non-existent directory or file for FILEOPEN operation
   src_file := BFILENAME ('C:/Yash/', p_parameter_name);
   SELECT t.BLOBFILE INTO dst_temp_file FROM YASH_INFO t WHERE t.name = p_parameter_name FOR UPDATE;
   -- open the file
   DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   -- determine length
   lgh_file := DBMS_LOB.getlength (src_file);
   -- read the file
   DBMS_LOB.loadfromfile (dst_temp_file, src_file, lgh_file);
   -- update the blob field
   UPDATE YASH_INFO t SET t.BLOBFILE = dst_temp_file WHERE t.name = p_parameter_name;
   -- close file
   DBMS_LOB.fileclose (src_file);
   COMMIT;
END;
/

JPublisher

This section illustrates a sample JPublisher translation of a simple object type. At this point, do not worry about the details of the code JPublisher generates. You can find more information about JPublisher input and output files, options, datatype mappings, and translation later in this chapter.

Create the object type employee:

CREATE TYPE employee AS OBJECT
(
    name       VARCHAR2(30),
    empno      INTEGER,
    deptno     NUMBER,
    hiredate   DATE,
    salary     REAL
);

The integer, number, and real types are all stored in the database as NUMBER types, but after translation, they have different representations in the Java program, based on your choice for the value of the -numbertypes option.

Oracle / PLSQL: Creating a View:

CREATE OR REPLACE FORCE EDITIONABLE VIEW emp_view AS 
   SELECT last_name, salary*12 annual_salary
   FROM employees 
   WHERE department_id = 20;
   
DROP VIEW "emp_view";

What is a VIEW in Oracle? An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables.

Granting Object Privileges

Use the GRANT statement to grant:

  • System privileges to users and roles.

  • CREATE USER and CREATE ROLE for definitions of local, global, and external privileges

  • Oracle Database Security Guide for information about other authorization methods and for information about privileges
    There are six major categories of privileges, some with significant subcategories:

    • System, Schema Object, Table, View, Procedure, Type Privileges

  • REVOKE for information on revoking grants

SQL>GRANT GRANT ANY OBJECT PRIVILEGE TO u1; 
SQL> CONNECT u1/u1 
Connected. 
SQL> GRANT SELECT ON scott.t2 TO u2; 
SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS 
 WHERE TABLE_NAME = 'employees'; 

GRANTEE                        OWNER 
------------------------------ ------------------------------ 
GRANTOR                        PRIVILEGE                                GRA 
------------------------------ ---------------------------------------- --- 
U2                             SCOTT 
SCOTT                          SELECT                                   NO

We can only 'execute' Functions/Procedures, Views are handled like tables. StackPost ORA-02204

GRANT SELECT on "PMC_TDT_V_APLICACION_EMAIL" to "PMC_TDT_ROL_USR";

GRANT EXECUTE ON SCHEMA.DB_Object_NAME    TO "USER_ROLE";

GRANT DEBUG   ON SCHEMA.PACKAGE_NAME TO "USER_ROLE";
GRANT EXECUTE ON SCHEMA.PACKAGE_NAME TO "USER_ROLE";

-- ORA-02204: ALTER, INDEX and EXECUTE not allowed for views
GRANT SELECT  ON SCHEMA.VIEW_NAME TO "USER_ROLE";

How do I get textual contents from BLOB in Oracle SQL

Note: This is because, Oracle will not be able to handle the conversion of BLOB that is more than length 2000.

SELECT utl_raw.cast_to_varchar2(dbms_lob.substr(<YOUR_BLOB_FIELD>,2000,1)) as Blob2000Chars FROM <YOUR_TABLE>;

CREATE OR REPLACE FORCE EDITIONABLE VIEW <YOUR_TABLE_VIEW> ("Blob2000Chars", "ID")
AS
  SELECT
	UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(<YOUR_BLOB_FIELD>,2000,1)) as Blob2000Chars, ID
  FROM <YOUR_TABLE> where FILE_TYPE = 'xml'; -- txt,xml,

XMLTable

SQL XMLTable with multiple namespaces

select x.* from
(select xmltype('<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
   <s:Body>
      <GetGeoLocationIDByWellTagIDResponse xmlns="http://tempuri.org/">
         <GetGeoLocationIDByWellTagIDResult xmlns:a="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:int>9110910</a:int>
         </GetGeoLocationIDByWellTagIDResult>
      </GetGeoLocationIDByWellTagIDResponse>
   </s:Body>
</s:Envelope>') xml from dual) t,
xmltable(
 xmlnamespaces (
 'http://tempuri.org/' as "e",
 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as "a"
 , 'http://schemas.xmlsoap.org/soap/envelope/' as "s"
 ,default 'http://tempuri.org/'
 ),
 --'//e:GetGeoLocationIDByWellTagIDResponse/a:GetGeoLocationIDByWellTagIDResult'
 's:Envelope/s:Body/GetGeoLocationIDByWellTagIDResponse/GetGeoLocationIDByWellTagIDResult'
 passing t.xml
 columns loc_id number
 path 'a:int'
) x;

-- Sample

select x.* from
(select xmltype('<Acknowledgement_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <mRID>XXXXXXXXXXXXXXX</mRID>
   <createdDateTime>2020-06-22T06:53:56Z</createdDateTime>
   <sender_MarketParticipant.mRID codingScheme="A01">XXXXXXXXXXXXXXX</sender_MarketParticipant.mRID>
   <sender_MarketParticipant.marketRole.type>A04</sender_MarketParticipant.marketRole.type>
   <receiver_MarketParticipant.mRID codingScheme="A01">XXXXXXXXXXXXXXX</receiver_MarketParticipant.mRID>
   <receiver_MarketParticipant.marketRole.type>A08</receiver_MarketParticipant.marketRole.type>
   <received_MarketDocument.mRID>XXXXXXXXXXXXXXX</received_MarketDocument.mRID>
   <received_MarketDocument.revisionNumber>3</received_MarketDocument.revisionNumber>
   <received_MarketDocument.type>A01</received_MarketDocument.type>
   <received_MarketDocument.process.processType>A01</received_MarketDocument.process.processType>
   <Rejected_TimeSeries>
      <mRID>1</mRID>
      <version>1</version>
      <Reason>
         <code>Z50</code>
         <text>A nomination for this date and contract number with version "1" has already been received</text>
      </Reason>
   </Rejected_TimeSeries>
   <Reason>
      <code>A03</code>
      <text>Message partially accepted</text>
   </Reason>
   <Reason>
      <code>A01</code>
      <text>Message fully accepted</text>
   </Reason>
</Acknowledgement_MarketDocument>') xml from dual) t,
xmltable(
  xmlnamespaces (default 'urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0'),
  '/Acknowledgement_MarketDocument/Reason'
  passing XMLtype ( REPLACE( REPLACE(t.xml, '<?xml version','<!--<?xml version'), '?>','?>-->') )
  columns
    POSITION FOR ordinality,
    codes    VARCHAR2(10 CHAR)  path '//code',
    codeText VARCHAR2(100 CHAR) path '//text'
 -- path '//Reason[last][substring(code,1,1)="A"]'
 -- path '//Reason/code' -- ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
 -- path '//Acknowledgement_MarketDocument/Reason/code'
) x;

PL/SQL XMLTable stackoverflow.com

XMLTABLE 12c doc: Convert XML Data into Rows and Columns using SQL

SET SERVEROUTPUT ON 
DECLARE
  XML_Namespace_Declaration VARCHAR2(100);
  XQuery_string_Capture_Row VARCHAR2(100);
  XML_XPath_XML_table_column VARCHAR2(100);
  Input_XML VARCHAR2(32767);
  Req_Status VARCHAR2 (10);
  Req_Type VARCHAR2 (10);
BEGIN
  Input_XML := '<Acknowledgement_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <Reason>
      <code>A01</code>
      <text>Message fully accepted</text>
   </Reason>
</Acknowledgement_MarketDocument>';
 XQuery_string_Capture_Row := '//Reason[last()]/ReasonCode/@v';
 XML_XPath_XML_table_column := '.';

IF ((Input_XML) LIKE '%<SendScheduleMessageResponse%' OR (Input_XML) LIKE '%<Acknowledgement_MarketDocument%') 
THEN
  -- AcknowledgementDocument xmlns="http://www.ote-cr.cz/schema/etso/types/ack-v5r0"
  Req_Type := 'ACK';
  XML_Namespace_Declaration := 'urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0';
-- ELSIF IF ((Input_XML) LIKE '%<SendScheduleMessageResponse%' AND (Input_XML) LIKE '%<AcknowledgementDocument%')  
ELSE
  Req_Type := 'XXX';
  DBMS_OUTPUT.put_line ('UnRecognised XML Input');
END IF;


FOR cur_rec IN 
(
  SELECT
    XMLTempTable.* FROM
    XMLTABLE (
	xmlnamespaces(DEFAULT 'urn:iec62325.351:tc57wg16:451-1:acknowledgementdocument:8:0'), -- As Variable not accepting XML_Namespace_Declaration using same string var holds
    '/Acknowledgement_MarketDocument/Reason/code' -- XPATH XQuery_string_Capture_Row
    --PASSING XMLtype ( REPLACE(REPLACE( Input_XML,'<?xml version','<!--<?xml version'),'?>','?>-->') ) -- Input XML
    PASSING XMLtype ( Input_XML )
    COLUMNS Reason_code VARCHAR2 (50 CHAR) PATH '.') -- XML_XPath_XML_table_column  Specify SubElement XML and Save it to XMLTABLE column with given ColumnName.
    XMLTempTable 
)
LOOP DBMS_OUTPUT.put_line('ROW column0=' || cur_rec.Reason_code );
Req_Status := cur_rec.Reason_code;
END LOOP;

DBMS_OUTPUT.put_line('Req_Type= ' || Req_Type || ', Req_Status= ' || Req_Status );
END
;
/

DBMS_OUTPUT

ROW column0=A01
Req_Type= ACK, Req_Status= A01


PL/SQL procedure successfully completed.
⚠️ **GitHub.com Fallback** ⚠️