千日夢의 Tistory

--//Oracle 11.2.0 for Windows 버전 기준
--//SYSDBA로 접속 ex) C:\>SQLPLUS "/as sysdba"
--//이하 SQL-Plus : SQL>

@"C:\oracle\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql"

grant plustrace to [사용자];

--//=======================================

conn [사용자]/[패스워드];
@"C:\oracle\product\11.2.0\dbhome_1\rdbms\admin\utlxplan.sql"

--//PLAN 확인
set linesize 120;
set autot on;
select * from tab where rownum=1;

======================================================

AUTOTRACE 주요 명령어

참조 : https://coding-factory.tistory.com/745

// variable to store whether document has been opened already or not
var bAlreadyOpened;

function docOpened()
{

	if(bAlreadyOpened != "true")
	{
		// document has just been opened
		var d = new Date();
		var sDate = util.printd("mm/dd/yyyy", d);

                // set date now
                app.alert("About to insert date into field now");
		this.getField("todaysDate").value = sDate;

		// now set bAlreadyOpened to true so it doesn’t
		// run again
bAlreadyOpened = "true";
	}
	else
	{
		// document has already been opened
	}
}

// call the docOpened() function
docOpened();

출처 : Example Acrobat JavaScripts – Planet PDF

/********************************
RSCivilTools by Andrew Binning
Version 01, 2-15-2012
********************************/
//Some code by:
// InDesign Fixups by Dave Merchant  - Creative Commons Share-alike license
// version 02, November 2010
// http://www.uvsar.com/go/indesignfixups

//create new submenu for the Acrobat 8/9 or X menus
//Determine version, assign menu location
var menuParent = (app.viewerVersion<10)? "DocumentProcessing":"Edit";
//Add the menu
app.addSubMenu({ cName:"RSCivilTools", cUser:"RSCivil Tools", cParent:menuParent, nPos:((app.viewerVersion<10)? 0:7) });
//Create a nested layer (tailored code for my specific Document.
app.addMenuItem({ cName:"RSCcreateNest", cUser:"Create Nest", cParent:"RSCivilTools", 
          cExec:"createNest();",
          cEnable:"event.rc = (event.target != null);", nPos:0 });
//Promote sub layers - Unravel the nests
app.addMenuItem({ cName:"RSCpromote", cUser:"Undo Nest", cParent:"RSCivilTools",
          cExec:"promoteOCG_handler(event.target);",
          cEnable:"event.rc = (event.target != null);", nPos:1 });
//Unlist Guides and Grids layer (from Adobe IN-Design)
app.addMenuItem({ cName:"RSCremGAG", cUser:"Unlist 'Guides and Grids'", cParent:"RSCivilTools",
          cExec:"removeGAG(event.target);",
          cEnable:"event.rc = (event.target != null);", nPos:2 });
//Set up layers ( this is tailored code for my specific Document)
app.addMenuItem({ cName:"RSCsetStates", cUser:"Set Layers", cParent:"RSCivilTools",
          cExec:"setStates();",
          cEnable:"event.rc = (event.target != null);", nPos:3 });
//Toggle this list of layers on and off (tailored code for my specific Document.
app.addMenuItem({ cName:"RSCtoggleCityLimits", cUser:"Toggle City Limits", cParent:"RSCivilTools",
          cExec:"toggleCityLimits();",
          cEnable:"event.rc = (event.target != null);", nPos:4 });
//Link to the company website         
app.addMenuItem({ cName:"RSCsite", cUser:"Website", cParent:"RSCivilTools", 
          cExec:"app.launchURL('http://rscivil.com');", nPos:5 });

//Add a button for this function - Add from Quick Tools 3rd party addons
app.addToolButton({
        cName: "RSCcreateNestButton",
        cExec: "createNest();",
        cTooltext: "Create Nest",
        cEnable: true,
        nPos: 0,
        cLabel: "Create Nest"
        })
//Add a button for this function - Add from Quick Tools 3rd party addons
app.addToolButton({
        cName: "promoteLayers",
        cExec: "promoteOCG_handler(event.target);",
        cTooltext: "Undo Nest",
        cEnable: true,
        nPos: 1,
        cLabel: "Undo Nest"

        })

//Creates a nested layer from a predetermined list
function createNest(){
    var layers = this.getOCGs();
    var newOrder = new Array();
    var cityLimits = new Array();
    var comps = new Array();

    /*******************************************************
    This is where I need help.
    By not setting the first element to a String it does not name the group. Instead it seems to attach it as a child to the previous element in the array.
    This is fine, except that when you turn off said parent it does not turn off the sub layers/OCGs.
    If I could figure out how to create an object and set it up as an element before my inserted array it might help.
    *******************************************************/
    //Commented for testing - Set first element of the array to a descriptive string (this will be the name of the nested group) 
    //cityLimits[0] = "City Limits";
    //Set first element of the array to a descriptive string (this will be the name of the nested group)
    comps[0] = "Comps";

    //Separate all layers/OCGs containing "CityLimits|" or "COMP" from the original list of layers/OCGs
    for (var i=0,j=0,k=0,l=1; i<layers.length; i++){
        if(layers[i].name.substr(0,11)==="CityLimits|"){
            cityLimits[j] = layers[i];  //separate CityLimits OCG
            j++;
        }
        else if(layers[i].name.substr(0,4)==="COMP"){
            comps[l] = layers[i];   //separate COMP OCG
            l++;
        }
        else{
            newOrder[k] = layers[i];    //cram everything else into a new array
            k++;
        }
    }
    //Insert the cityLimits array into the newOrder array at position 5, do not remove any elements (the element before this arbitrarily becomes the parent, but does not work correctly)
    newOrder.splice(5,0,cityLimits);
    //Append the comps array to the end of the newOrder array
    newOrder[newOrder.length] = comps;
    //set the newOrder array as the OCGOrder.
    this.setOCGOrder(newOrder);
}
//Code by Dave Merchant
//Handler for promoting OCGs out of nested layers
function promoteOCG_handler(oDoc) {

  var ocgOrder = oDoc.getOCGOrder();
  var hasNest = false;

  if (ocgOrder==null) {
    app.alert( "No layers in current file", 0, 0, "Cannot proceed");
  } else {
    for (var i=0; i<ocgOrder.length; i++) {
       if ((typeof(ocgOrder[i]) == "object") && (ocgOrder[i].length > 0)) hasNest = true;
    }
    if (hasNest)  {
      promoteOCGs(oDoc,ocgOrder);
    } else app.alert( "No nested layers in current file", 0, 0, "Cannot proceed");
  }
}
//Code by Dave Merchant
//Promote/unravel layers/OCG out of nests
function promoteOCGs(oDoc,ocgOrder) {

  // Removes the top-level OCG nest structure from a PDF, promoting all sub-OCGs to the top level.
  // Used to remove the nesting created when a PDF is exported from InDesign with "Create Acrobat Layers" checked.


  var oChk = { cMsg:"Unlist the 'Guides and Grids' layer?", bInitialValue:true, bAfterValue:false};

  //var cMesg = "This action will ungroup all nested layers. IT CANNOT BE UNDONE.\n\nDo you want to continue?";
  //var nRtn = app.alert({ cMsg:cMesg, nIcon:2, nType:2, cTitle:"Promote nested layers", oCheckbox:oChk});
  //if (nRtn == 4) {

    var newOrder = new Array();

    for (var i=0; i<ocgOrder.length; i++) {

     var oType = typeof(ocgOrder[i]);
     var oLeng = ocgOrder[i].length
     if ((oType == "object") && (oLeng > 0)) {      // it's a nest, do the promotions

        for (var j=0; j<oLeng; j++) {
           if ((typeof(ocgOrder[i][j]) == "object") && 
              (!oChk.bAfterValue || (ocgOrder[i][j].name != "Guides and Grids"))) newOrder.push(ocgOrder[i][j]);
        }

     } else if (!oChk.bAfterValue || (ocgOrder[i].name != "Guides and Grids")) newOrder.push(ocgOrder[i]);
    }
    oDoc.setOCGOrder( newOrder );
  //}
}


//Code by Dave Merchant
// Removes the listing for (sub)OCG named "Guides and Grids".
// Does NOT delete the layer, simply hides it from the sidebar display.
function removeGAG(oDoc) {
  var cMesg = "This action will unlist the 'Guides and Grids' layer from the sidebar but will NOT delete the ";
  cMesg += "layer itself. IT CANNOT BE UNDONE.\n\nDo you want to continue?";
  var nRtn = app.alert(cMesg, 2, 2, "Unlist 'Guides and Grids'");
  if (nRtn == 4) {

    var ocgOrder = oDoc.getOCGOrder();
    var newOrder = new Array();

    for (var i=0; i<ocgOrder.length; i++) {

     var oType = typeof(ocgOrder[i]);
     var oLeng = ocgOrder[i].length
     if ((oType == "object") && (oLeng > 0)) {      // it's a nest

        var subObj = new Array();
        for (var j=0; j<oLeng; j++) {
           if ((typeof(ocgOrder[i][j]) == "string") || (ocgOrder[i][j].name != "Guides and Grids")) subObj.push(ocgOrder[i][j]);
        }
        newOrder.push(subObj);

     } else if (ocgOrder[i].name != "Guides and Grids") newOrder.push(ocgOrder[i]);
    }
    oDoc.setOCGOrder( newOrder );
  }
}

//Just a list of layers I want to toggle on or off (document specific)
function togList(name){
    if(name.substr(0,11)==="CityLimits|")
        return true;
    return false;
}
//Just a list of layers I want to set an initial state to off (document specific)
function offList(name){
    var lOff =  new Array();
    lOff[0] = "ADT";
    lOff[1] = "CityLimits|1900";
    lOff[2] = "CityLimits|1910";
    lOff[3] = "CityLimits|1920";
    lOff[4] = "CityLimits|1930";
    lOff[5] = "CityLimits|1940";
    lOff[6] = "CityLimits|1950";
    lOff[7] = "CityLimits|1960";
    lOff[8] = "CityLimits|1970";
    lOff[9] = "CityLimits|1975";
    lOff[10] = "CityLimits|1980";
    lOff[11] = "CityLimits|1985";
    lOff[12] = "CityLimits|1990";
    lOff[13] = "CityLimits|1995";
    lOff[14] = "CityLimits|2000";
    lOff[15] = "CityLimits|2005";
    lOff[16] = "CityLimits|2006";
    lOff[17] = "CityLimits|2007";
    lOff[18] = "CityLimits|2008";
    lOff[19] = "CityLimits|2009";
    lOff[20] = "CityLimits|2010";
    lOff[21] = "CityLimits|2011";
    lOff[29] = "Distance Circles 1";
    lOff[30] = "Distance Circles 2";
    lOff[31] = "landuse|Agriculture";
    lOff[32] = "landuse|Industrial";
    lOff[33] = "Landmark Labels Locations";
    lOff[34] = "landmarks|Locations";

    for (var i=0; i<lOff.length; i++){
        if(lOff[i] === name)
            return true;
    }
    return false;
}

//Checks all layers against a list and toggles them off or on (document specific)
function toggleCityLimits(){
   var layers = this.getOCGs();
    for (var i=0; i<layers.length; i++){
        var tog = true;
        if(togList(layers[i].name)){
            if(layers[i].state)
                tog = false;
            layers[i].state = tog; //toggle layer
        }
    }
}

//Checks all layers against a list and sets the initial state and current state to off (state=visibility) (document specific)
function setStates(){
   var layers = this.getOCGs();
    for (var i=0; i<layers.length; i++){
        var tog = true;
        if(offList(layers[i].name))
            tog = false;
        layers[i].state = tog;      //turn off layer
        layers[i].initState = tog;  //set initial visibilty to off
    }
}

출처 : http://www.uvsar.com/go/indesignfixups

syncAnnotScan();

var strFDF = exportAsFDFStr({bAllFields: 'true', bFlags: 'true', bAnnotations: 'true'});
//exportAsXFDFStr(true, true, null, true, 1);";

var strXFDF = exportAsXFDFStr(true, true, true, true, 1);
//exportAsXFDFStr(true, true, null, true, 1);

exportAsFDF({ bAnnotations: 'true', cPath: '/d/export.fdf'});


importAnFDF("/d/import.fdf");
importAnXFDF("/d/import.xfdf");

// Add navigation buttons to the page
// This script puts 3 buttons on top of every page (except the first one that has one button)
// First button "<" : takes to the previous page
// Second button: "1" : takes to the first page of the document
// Third button: ">" : takes to the next page in the document (does not exists on the last page)

var inch = 72;

try 
{

    nLastPage = this.numPages - 1;

    for (var p = 0; p < this.numPages; p++)
    {
        var x = 0.5;
 
        if (p > 0)
        {
            AddButton(p,x,0.5,0.25,0.25,"PrevPage","<","Previous Page","this.pageNum--;"); // left arrow, previous page
            x += 0.3;
        }
    
        if (p != 0)
        {
            AddButton(p,x,0.5,0.25,0.25,"StartPage","1","Go To First Page","this.pageNum=0;"); // "1", takes to the first page
            x += 0.3;
        }
    
        if (p < nLastPage)
        {
            AddButton(p,x,0.5,0.25,0.25,"NextPage",">","Next Page","this.pageNum++;"); // right arrow, next page
            x += 0.3;
        }

        AddButton(p,x,0.5,0.25,0.25,"Back","<<","Go Back","app.execMenuItem(\"GoBack\");"); // right arrow, next page
        x += 0.3;
        
    }

}


catch (e)
{
app.alert(e);
}

// AddButton function creates a button with given parameters and action

function AddButton(nPageNum, x, y, width, height, strText, strCaption, strToolTip, strAction)
{
    var aRect = this.getPageBox( { nPage: nPageNum} );
    aRect[0] += x * inch;
    aRect[1] -= y * inch;
    aRect[2] = aRect[0] + width * inch;
    aRect[3] = aRect[1] - height * inch;

    var f = this.addField(strText,"button", nPageNum, aRect);
    f.setAction("MouseUp",strAction);
    f.userName = strToolTip;
    f.delay = true;
    f.borderStyle = border.s;
    f.highlight = "push";
    f.textSize = 0; // autosized
    f.textColor = color.blue;
    f.strokeColor = color.blue;
    f.fillColor = color.white;
    // you can specify a different font here, otherwise it uses a default one
    //f.textFont = font.ZapfD;
    f.buttonSetCaption(strCaption); 
    f.delay = false;
}

출처 : https://www.evermap.com/javascript.asp

출처 : ADOBE

var annots = this.getAnnots();
console.clear();
for (var i = 0; i < annots.length; i++) 
{
	//console.println(annots[i].toString());
	//if (annots[i].type == "PolyLine") 
	{  
		var a = annots[i].getProps();
		for(var o in a)
		{
			console.println( "annots." + o + "=" + a[o]);
		}
		console.println( "==========================")
		++counter;   
	}
}

app.alert("Total comments count: " + annots.length + " / : " + counter);

var annotText = this.addAnnot({
	page: 0,
	type: "FreeText",
	textFont: "Arial", // or, textFont: "Viva-Regular",
	textSize: 7,
	rect: [57.806640625,730.9102172851562,75.806640625,744.916015625],
	width: 0,
	alignment: 1,
	contents: "D",
	lineEnding : "None",
	refType : "R",
	seqNum : 1
});

var annotPoly = this.addAnnot({
	page: 0,
	type: "PolyLine",
	rect: [55.78631591796875,730.4441528320312,73.8094482421875,747.8836059570312], // height for three lines
	reftype : "R",
	rotation: -270,
	vertices: [[73.25311279296875,738.8373413085938],[56.49212646484375,747.6712036132812],[56.367919921875,730.8611450195312],[73.25311279296875,738.8373413085938]],
	with: 0.75,
	seqNum : 2
});

var annot = this.addAnnot({
	page: 0,
	type: "Text",
	point: [72,500],
	popupRect: [72, 500,6*72,500-2*72],
	popupOpen: true,
	noteIcon: "Help"
});

var spans = new Array();
spans[0] = new Object();
spans[0].text = "Attention:\r";
spans[0].textColor = color.blue;
spans[0].textSize = 18;

spans[1] = new Object();
spans[1].text = "Adobe Acrobat 6.0\r";
spans[1].textColor = color.red;
spans[1].textSize = 20;
spans[1].alignment = "center";

spans[2] = new Object();
spans[2].text = "will soon be here!";
spans[2].textColor = color.green;
spans[2].fontStyle = "italic";
spans[2].underline = true;
spans[2].alignment = "right";

// Now give the rich field a rich value
annot.richContents = spans;

출처 : https://www.gdpicture.com/forum/viewtopic.php?t=6250#p19870 

<!-- 출처: https://bumcrush.tistory.com/182 [맑음때때로 여름] -->
<!-- 코드 하이라이트 -->
<script src="./images/highlight.pack.js"></script>
<link rel="stylesheet" href="./images/vs2015.css">
<script>hljs.initHighlightingOnLoad();</script>
<!-- 코드 블럭 라인 넘버 표시 -->
<script src="./images/highlightjs-line-numbers.js"></script>
<script> hljs.initLineNumbersOnLoad();
$(document).ready(function() {
$('code.hljs').each(function(i, block) {
hljs.lineNumbersBlock(block);
});
});
</script>

출처: https://bumcrush.tistory.com/182 [맑음때때로 여름]

//===========================================

다른 방법 참조 출처 : https://bcp0109.tistory.com/254

<link rel="stylesheet"
      href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/11.0.1/styles/atom-one-dark.min.css">
<script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/11.0.1/highlight.min.js"></script>
<script>hljs.initHighlightingOnLoad();</script>
<script src="//cdnjs.cloudflare.com/ajax/libs/highlightjs-line-numbers.js/2.8.0/highlightjs-line-numbers.min.js"></script>
<script>
hljs.initHighlightingOnLoad();
hljs.initLineNumbersOnLoad();
</script>

/* Line Number CSS */
/* for block of numbers */
.hljs-ln-numbers {
-webkit-touch-callout: none;
-webkit-user-select: none;
-khtml-user-select: none;
-moz-user-select: none;
-ms-user-select: none;
user-select: none;
 
text-align: center;
color: #ccc;
border-right: 1px solid #CCC;
vertical-align: top;
width: 25px;
}
 
/* your custom style here */
.hljs-ln td.hljs-ln-code {
padding-left: 5px;

https://highlightjs.org/

https://github.com/wcoder/highlightjs-line-numbers.js

 문자열 값(통화, 자리수 구분)을 숫자타입으로 변경

※ 자세한 설명은 생략 (정규식은 10g 이상 가능)

SELECT TO_NUMBER( REGEXP_REPLACE('$-1,234,567.89', '[^0-9.-]', '') ) NUM FROM DUAL

오라클의 여러행을 하나의 컬럼으로 합치는 쿼리를 메모하고져 글을 남깁니다.

자세한 설명은 하지 않겠습니다.

(XMLAGG 및 정규식은 10g부터 사용 가능)

• WM_CONCAT

SELECT
    WM_CONCAT(job) AS WM_JOBS
  FROM emp

• LISTAGG

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
FROM emp

• LISTAGG (deptno 기준)

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) OVER(PARTITION BY deptno) AS AGG_JOBS2
FROM emp

• LISTAGG (중복제거)

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
    , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,\1)*(,|$)', '\1\3') AS AGG_JOBS3
FROM emp

• XMLAGG, XMLELEMENT (CLOB타입 - WM_CONCAT, LISTAGG : VARCHAR2라 최대 4000byte)

SELECT
    XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS
FROM emp A

• XMLAGG, XMLELEMENT (CLOB타입, 중복제거, 시작/끝 구분자 삭제)

WITH A AS
(
SELECT
    XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
    , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT 
    SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
    , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
    , SUBSTR(REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,\1)*(,|$)', '\1\3'), 2) AS SUB_JOB3
FROM A

• 통합 (LISTAGG -PARTITION 제외)

WITH A AS
(
SELECT
    WM_CONCAT(job) AS WM_JOBS
    , LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
    , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,\1)*(,|$)', '\1\3') AS AGG_JOBS3
    , XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
    , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT
    A.*
    , SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
    , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
    , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS1, '([^,]+)(,\1)*(,|$)', '\1\3'), '(,)$', '') AS SUB_JOB3
    , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,\1)*(,|$)', '\1\3'), '^(,)', '') AS SUB_JOB4
FROM A

※ ORA-01489 문자열 연결의 결과가 너무 깁니다.

 : 오류 발생시 XMLAGG를 이용하여 CLOB 사용.

ORA-12541 : TNS 리스너가 없습니다.

갑자기 "ORA-12541"이라는 오류가 발생하였다.

기존에 접속자는 이상이 없는데, 신규로 접속할려고 하니 위와 같은 오류를 발생시킨다.

클라이언트인 내 컴퓨터도 꺼보고, 서버도 리스타트 해보고 신규 접속을 할려고하니,

서버를 리부팅해서 그런지 모두 접속이 되지 않는다.

서버에서 "C:\>lsnrctl status"를 실행시켜 보니......멍청히 있다.

그래서 구글링을 해본 결과, 간혹 "listener.log" 파일이 4GB를 넘으면 접속이 안된다는 글을 본다.

그래서 위치를 찾는다.

난 윈도우버전을 이용하므로 윈도우 버전으로 위치를 기입하겠다.

"{드라이브명}:\{ORACLE 설치 ROOT 폴더}\diag\tnslsnr\{서버명}\listener\trace\listener.log"

ex) 폴더 위치 : D:\oracle\diag\tnslsnr\TESTSVR\listener\trace\

1. 오라클 스톱 그런거 귀찮고 "lsnrctl stop"을 날려도 함흥 차사이니 그냥 실행명령 "servcies.msc" 또는 제어판 "서비스"를 실행하여 서비스를 제어하는 Dialog를 실행한다.
   (혹시 모르니 오라클 서비스는 먼저 정지 시켜놓고 하자~)
   
   
2. 이름 중 "OracleOraDb11g_home1TNSListener"를 찾아서 서비스를 중지 시킨다.
   
   
   
   
3. 설정 폴더로 이동하여 기존 4GB가 넘은 "listener.log"의 이름을 변경 또는 삭제한다.
   (신규 파일은 안만들어도 나중에 lsnrctl이 정상 가동 되면서 시스템이 알아서 새로 만든다.)
   
   
   
   
4. 다시 시작하고 그런거 귀찮고 서비스가 꼬일질 모르니 이번 참에 서버를 한번 리부팅해준다.
   
   
5. 모든 서비스가 정상적으로 접속되는지 확인한다.

※ 오라클 버전 11.2.0 x64bit 기준

자작이며, PHP Library중 하나인 ADOdb의 crypt.inc.php의 MD5Crypt Class를 C#으로 변경 후 Oracle용 PL/SQL에 맞게 변경하였습니다.

MS-SQL, SQLite는 C#을 이용하여 DLL로 제작하여 로딩하시면 사용 가능합니다.

패키지명은 HxCrypt라고 하였습니다.

별도로 비트연산용 PKG_UTILS( http://overoid.tistory.com/35 ) / 첨부(PKG_UTILS_Oracle.sql) 참조를 이용하였습니다.

자세한 내용은 첨부파일을 참고하시기 바랍니다.

(ADODB가 GNU 라이센스이므로 공유는 당연하다 판단했습니다. / C#과 Oracle용으로 마이그레이션한 라이블러리를 공개합니다.)

PHP용 소스 출처 : http://adodb.org/

- 암호화 : HxCrypt.Encrypt(문자열, 키값) //호출 할 때마다 다른 값이 리턴됨

- 복호화 : HxCrypt.Decrypt(암호 문자열, 키값)

[CODE]

CREATE OR REPLACE PACKAGE HxCrypt AS
/******************************************************************************
   // 출처 : userpark.net / userpark@userpark.net
   // 배포 라이센스 : GNU
   // 원 소스 출처 : http://adodb.org/ , crypt.inc.php, MD5Crypt
   NAME:       HxCrypt
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2018/05/10      userpark       1. Created this package.
******************************************************************************/

  FUNCTION base64_encode(inputString VARCHAR2) RETURN VARCHAR2;
  FUNCTION base64_decode(inputString VARCHAR2) RETURN VARCHAR2;
  FUNCTION Md5(inputString VARCHAR2) RETURN VARCHAR2;
  FUNCTION keyED(inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2;
  FUNCTION Encrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2;
  FUNCTION Decrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2;
  
END HxCrypt;
/



CREATE OR REPLACE PACKAGE BODY HxCrypt AS
  outputString VARCHAR2(2000);

FUNCTION base64_encode(inputString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    outputString := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(inputString)));
    RETURN outputString;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END base64_encode;
  
  FUNCTION base64_decode(inputString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    outputString := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(inputString)));
    RETURN outputString;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END base64_decode;
  
  FUNCTION Md5(inputString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    --outputString := Md5(inputString);
    outputString := LOWER(RAWTOHEX(UTL_RAW.CAST_TO_RAW(sys.dbms_obfuscation_toolkit.md5(input_string => inputString))));
    RETURN outputString;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END Md5;
  
  FUNCTION KeyED (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2 IS
ctr NUMBER := 0;
i NUMBER := 0;
nInput NUMBER := 0;
nKey NUMBER := 0;
keyValue VARCHAR2(2000);
iTxt NUMBER;
iKey NUMBER;
iVal NUMBER;
BEGIN
    i := 1;
    keyValue := Md5(inputKey);
    nInput := LENGTH(inputValue);
    nKey := LENGTH(keyValue);
    ctr := 1;
    outputString := null;
    WHILE i <= nInput LOOP
        IF ctr = nKey THEN 
           ctr := 1;
        END IF;
        --utl_raw.cast_to_raw(inputString)
        iTxt := ASCII(SUBSTR(TO_CHAR(inputValue), i, 1));
        --iTxt := utl_raw.cast_to_raw(SUBSTR(TO_CHAR(inputString), i + 1, 1));
        iKey := ASCII(SUBSTR(TO_CHAR(keyValue), ctr, 1));
        --iKey := utl_raw.cast_to_raw(SUBSTR(TO_CHAR(keyValue), ctr + 1, 1));
        --iVal := iTxt ^ iKey;
        iVal := PKG_UTILS.BITXOR(iTxt,iKey);
        outputString := outputString || CHR(iVal);
        --outputString := outputString || utl_raw.cast_to_varchar2(TO_CHAR(iVal));
        ctr := ctr + 1;
        i := i + 1;
        EXIT WHEN i > nInput; 
    END LOOP;
    RETURN outputString;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END KeyED;

FUNCTION Encrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2 IS
Result VARCHAR2(2000);
ctr NUMBER := 0;
i NUMBER := 0;
n NUMBER := 0;
keyValue VARCHAR2(2000);
nKey NUMBER := 0;
iTxt NUMBER;
iKey NUMBER;
iVal NUMBER;
cKey VARCHAR2(8);
BEGIN
    i := 1;
    ctr := 1;
    keyValue := Md5(TO_CHAR(CEIL(DBMS_RANDOM.VALUE(0,32000))));
    n := LENGTH(inputValue);
    nKey := LENGTH(keyValue);
    Result := null;
    WHILE i <= n LOOP
        IF ctr = nKey THEN 
           ctr := 1;
        END IF;
        cKey := SUBSTR(TO_CHAR(keyValue), ctr, 1);
        iTxt := ASCII(SUBSTR(TO_CHAR(inputValue), i, 1));
        iKey := ASCII(cKey);
        --iVal := iTxt ^ iKey;
        iVal := PKG_UTILS.BITXOR(iTxt,iKey);
        Result := Result || cKey || CHR(iVal);
        ctr := ctr + 1;
        i := i + 1;
        EXIT WHEN i > n; 
    END LOOP;
    outputString := base64_encode(KeyED(Result, inputKey));
    RETURN outputString;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END Encrypt;

FUNCTION Decrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2 IS
i NUMBER := 0;
n NUMBER := 0;
keyValue VARCHAR2(2000);
iTxt NUMBER;
iKey NUMBER;
iVal NUMBER;
BEGIN
    i := 1;
    keyValue := KeyED(base64_decode(inputValue), inputKey);
    n := LENGTH(keyValue);
    outputString := null;
    WHILE i <= n LOOP
        iKey := ASCII(SUBSTR(TO_CHAR(keyValue), i, 1));
        i := i + 1;
        iTxt := ASCII(SUBSTR(TO_CHAR(keyValue), i, 1));
        --iVal := iTxt ^ iKey;
        iVal := PKG_UTILS.BITXOR(iTxt,iKey);
        outputString := outputString || CHR(iVal);
        i := i + 1;
        EXIT WHEN i > n; 
    END LOOP;
    
    RETURN outputString;
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
           
END Decrypt;


END HxCrypt;
/

HxCrypt_Oracle_userpark.sql

PKG_UTILS_Oracle.sql

[PHP / Crypt / MD5Crypt]암호화, 복호화 Class (ADOdb의 crypt.inc.php

http://userpark.net/124

[C# / .NET / Crypt / HxCrypt]암호화, 복호화 Class

http://userpark.net/125

[ORACLE / PL/SQL / Crypt / HxCrypt]암호화, 복호화 PACKAGE(Class)

http://userpark.net/126

자작이며, PHP Library중 하나인 ADOdb의 crypt.inc.php의 MD5Crypt Class를 C#에 맞게 변경하였습니다.

(ASP.NET, MS-SQL, SQLite에서도 사용 가능)

Class명은 HxCrypt라고 하였습니다.

자세한 내용은 첨부파일을 참고하시기 바랍니다.

(ADODB가 GNU 라이센스이므로 공유는 당연하다 판단했습니다. / C#과 Oracle용으로 마이그레이션한 라이블러리를 공개합니다.)

PHP용 소스 출처 : http://adodb.org/

- 암호화 : HxCrypt.Encrypt(문자열, 키값) //호출 할 때마다 다른 값이 리턴됨

- 복호화 : HxCrypt.Decrypt(암호 문자열, 키값)

암호화 문자열과 키값은 반드시 키보드에 존재하는 영문+숫자+특수키 값만을 정상적으로 사용 가능하며,

기타 다른 문자(특수문자, 한글, …)들은 문자 인코딩 타입과 플랫폼에 따라 상이한 결과가 나오므로 주의가 필요함.

[CODE]

public class HxCrypt
{
    /******************************************************************************************
    // 출처 : userpark.net / userpark@userpark.net
    // 배포 라이센스 : GNU
    // 원 소스 출처 : http://adodb.org/ , crypt.inc.php, MD5Crypt
    // .NET Standard 2.0 기준 제작 / System.Security.Cryptography
    *******************************************************************************************/
    
    //랜덤(난수)값
    // - PHP에서는 랜덤값 중복을 방지하기 위하여 sland를 이용하였으나
    // - C#에서는 단위 변수로 이용시 중복되지 않음
    private static Random sland = new Random(); 
    #region base64 Encode/Decode
    /// 

    /// Base64 Encode
    /// 
    /// 입력값
    /// Encoding Type
    /// Base64 Encode 문자열
    public static string base64_encode(string input, HxEncodingType encodingType = HxEncodingType.ASCII)
    {
        //byte[] inputStringAsBytes = Encoding.ASCII.GetBytes(input);
        byte[] inputStringAsBytes = GetString2Bytes(input, encodingType);
        string Result = Convert.ToBase64String(inputStringAsBytes);
        return Result;
    }

    /// 

    /// Base64 Decode
    /// 
    /// 입력값
    /// Encoding Type
    /// Base64 Decode 문자열
    public static string base64_decode(string input, HxEncodingType encodingType = HxEncodingType.ASCII)
    {
        byte[] inputStringAsBytes = Convert.FromBase64String(input);

        //string Result = Encoding.ASCII.GetString(inputStringAsBytes);
        string Result = GetBytes2String(inputStringAsBytes, encodingType);
        return Result;
    }
    #endregion

    /// 

    /// CryptAPI를 이용한 암호화, 복호화 키 생성
    /// 
    /// 입력 문자
    /// 키 문자
    /// 생성 Key 문자열
    public static string keyED(string inputValue, string keyValue)
    {
        string Result = string.Empty;
        try
        {
            keyValue = Md5(keyValue);
            int ctr = 0;
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < inputValue.Length; i++)
            {
                if (ctr == keyValue.Length)
                    ctr = 0;
                //char cTxt = Convert.ToChar(txt.Substring(i, 1));
                //char cKey = Convert.ToChar(encrypt_key.Substring(ctr, 1));
                //int iVal = Convert.ToInt32(cTxt) ^ Convert.ToInt32(cKey);
                int iTxt = Convert.ToInt32(Convert.ToChar(inputValue.Substring(i, 1)));
                int iKey = Convert.ToInt32(Convert.ToChar(keyValue.Substring(ctr, 1)));
                int iVal = iTxt ^ iKey;
                sb.Append(Convert.ToChar(iVal));
                ctr++;
            }
            Result = sb.ToString();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            Result = string.Empty;
        }
        return Result;
    }

    /// 

    /// CryptAPI를 이용한 암호화
    /// 
    /// 암호화할(일반) 문자열
    /// 키 문자
    /// 암호화된 문자열
    public static string Encrypt(string inputValue, string keyValue)
    {
        string Result = string.Empty;
        try
        {
            if (!String.IsNullOrWhiteSpace(inputValue))
            {
                string encrypt_key = Md5(sland.Next(0, 32000).ToString());
                int ctr = 0;
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < inputValue.Length; i++)
                {
                    if (ctr == encrypt_key.Length)
                        ctr = 0;
                    char cKey = Convert.ToChar(encrypt_key.Substring(ctr, 1));
                    int iTxt = Convert.ToInt32(Convert.ToChar(inputValue.Substring(i, 1)));
                    int iKey = Convert.ToInt32(cKey);
                    int iVal = iTxt ^ iKey;
                    sb.Append(cKey);
                    sb.Append(Convert.ToChar(iVal));
                }
                Result = base64_encode(keyED(sb.ToString(), keyValue));
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            Result = string.Empty;
        }
        return Result;
    }

    /// 

    /// CryptAPI를 이용한 복호화
    /// 
    /// 암호화된 문자열
    /// 키 문자
    /// 복호화된 문자열
    public static string Decrypt(string inputValue, string keyValue = null)
    {
        string Result = string.Empty;
        try
        {
            if (!String.IsNullOrWhiteSpace(inputValue))
            {
                inputValue = keyED(base64_decode(inputValue), keyValue);
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < inputValue.Length; i++)
                {
                    //char cKey = Convert.ToChar(txt.Substring(i, 1));
                    int iKey = Convert.ToInt32(Convert.ToChar(inputValue.Substring(i, 1)));
                    i++;
                    //char cTxt = Convert.ToChar(txt.Substring(i, 1));
                    int iTxt = Convert.ToInt32(Convert.ToChar(inputValue.Substring(i, 1)));
                    //int iVal = Convert.ToInt32(cTxt) ^ Convert.ToInt32(cKey);
                    int iVal = iTxt ^ iKey;
                    sb.Append(Convert.ToChar(iVal));
                }
                Result = sb.ToString();
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            Result = string.Empty;
        }
        return Result;
    }

    /// 

    /// 랜덤으로 요청 자리수 만큼의 문자열 생성
    /// 
    /// 요청 자리수(1 이상, 0일 경우 기본값(8))
    /// 랜덤 문자열
    public static string RandPass(uint maxLength = 8)
    {
        string Result = string.Empty;
        if (maxLength <= 0)
        {
            maxLength = 8;
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < maxLength; i++)
        {
            int randnumber = sland.Next(48, 120);
            while ((randnumber >= 58 && randnumber <= 64) || (randnumber >= 91 && randnumber <= 96))
            {
                randnumber = sland.Next(48, 120);
            }
            sb.Append(Convert.ToChar(randnumber));
        }
        Result = sb.ToString();
        return Result;
    }

    /// 

    /// Byte형을 Encoding Type 문자열로 변환
    /// 
    /// 입력값
    /// Encoding Type
    /// 변환 값
    public static string GetBytes2String(byte[] input, HxEncodingType encodingType)
    {
        string Result;
        switch (encodingType)
        {
            case HxEncodingType.UTF7:
                Result = Encoding.UTF7.GetString(input);
                break;
            case HxEncodingType.UTF32:
                Result = Encoding.UTF32.GetString(input);
                break;
            case HxEncodingType.Unicode:
                Result = Encoding.Unicode.GetString(input);
                break;
            case HxEncodingType.BigEndianUnicode:
                Result = Encoding.BigEndianUnicode.GetString(input);
                break;
            case HxEncodingType.ASCII:
                Result = Encoding.ASCII.GetString(input);
                break;
            case HxEncodingType.Default:
                Result = Encoding.Default.GetString(input);
                break;
            case HxEncodingType.UTF8:
            case HxEncodingType.None:
            default:
                Result = Encoding.UTF8.GetString(input);
                break;
        }
        return Result;
    }

    public static byte[] GetString2Bytes(string input, HxEncodingType encodingType = HxEncodingType.None)
    {
        byte[] Result;
        switch (encodingType)
        {
            case HxEncodingType.UTF7:
                Result = Encoding.UTF7.GetBytes(input);
                break;
            case HxEncodingType.UTF32:
                Result = Encoding.UTF32.GetBytes(input);
                break;
            case HxEncodingType.Unicode:
                Result = Encoding.Unicode.GetBytes(input);
                break;
            case HxEncodingType.BigEndianUnicode:
                Result = Encoding.BigEndianUnicode.GetBytes(input);
                break;
            case HxEncodingType.ASCII:
                Result = Encoding.ASCII.GetBytes(input);
                break;
            case HxEncodingType.Default:
                Result = Encoding.Default.GetBytes(input);
                break;
            case HxEncodingType.UTF8:
            case HxEncodingType.None:
            default:
                Result = Encoding.UTF8.GetBytes(input);
                break;
        }
        return Result;
    }

    /// 

    /// Byte형을 문자열로 변환
    /// 
    /// 입력 값
    /// 문자 포멧
    /// 변환 값
    public static string GetBytes2String(byte[] input, string format = null)
    {
        StringBuilder sBuilder = new StringBuilder();

        // Loop through each byte of the hashed data 
        // and format each one as a hexadecimal string.
        for (int i = 0; i < input.Length; i++)
        {
            sBuilder.Append(input[i].ToString(format));
        }

        // Return the hexadecimal string.
        return sBuilder.ToString();
    }

    #region System.Security.Cryptography
    /// 

    /// MD5 
    /// 
    /// 
    /// 
    /// 
    public static string Md5(string inputValue, HxEncodingType encodingType = HxEncodingType.None)
    {
        string Result = null;
        using (System.Security.Cryptography.MD5 md5Hash = System.Security.Cryptography.MD5.Create())
        {
            Result = GetMd5Hash(md5Hash, inputValue, encodingType);

        }
        //String.IsNullOrWhiteSpace
        return Result;
    }

    private static string GetMd5Hash(System.Security.Cryptography.MD5 md5Hash, string input, HxEncodingType encodingType = HxEncodingType.None)
    {

        // Convert the input string to a byte array and compute the hash.
        //byte[] data = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input));
        //byte[] data = md5Hash.ComputeHash(Encoder.Default.GetBytes(input));
        byte[] data = md5Hash.ComputeHash(GetString2Bytes(input, encodingType));
        return GetBytes2String(data, "x2");

        //byte[] dataEA = md5Hash.ComputeHash(Encoding.ASCII.GetBytes(input));
        //byte[] dataE7 = md5Hash.ComputeHash(Encoding.UTF7.GetBytes(input)); 
        //byte[] dataE8 = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input));
        //byte[] dataEU = md5Hash.ComputeHash(Encoding.Unicode.GetBytes(input));
        //byte[] dataE32 = md5Hash.ComputeHash(Encoding.UTF32.GetBytes(input));
        //byte[] dataED = md5Hash.ComputeHash(Encoding.Default.GetBytes(input));

        //byte[] dataAA = md5Hash.ComputeHash(ASCIIEncoding.ASCII.GetBytes(input));
        //byte[] dataA7 = md5Hash.ComputeHash(ASCIIEncoding.UTF7.GetBytes(input));
        //byte[] dataA8 = md5Hash.ComputeHash(ASCIIEncoding.UTF8.GetBytes(input));
        //byte[] dataAU = md5Hash.ComputeHash(ASCIIEncoding.Unicode.GetBytes(input));
        //byte[] dataA32 = md5Hash.ComputeHash(ASCIIEncoding.UTF32.GetBytes(input));
        //byte[] dataAD = md5Hash.ComputeHash(ASCIIEncoding.Default.GetBytes(input));

        // Create a new Stringbuilder to collect the bytes
        // and create a string.
        /*
        StringBuilder sBuilder = new StringBuilder();

        // Loop through each byte of the hashed data 
        // and format each one as a hexadecimal string.
        for (int i = 0; i < data.Length; i++)
        {
            sBuilder.Append(data[i].ToString("x2"));
        }

        // Return the hexadecimal string.
        return sBuilder.ToString();
        */

    }

    // Verify a hash against a string.
    private static bool VerifyMd5Hash(System.Security.Cryptography.MD5 md5Hash, string input, string hash, HxEncodingType encodingType = HxEncodingType.None)
    {
        // Hash the input.
        string hashOfInput = GetMd5Hash(md5Hash, input, encodingType);

        // Create a StringComparer an compare the hashes.
        StringComparer comparer = StringComparer.OrdinalIgnoreCase;

        if (0 == comparer.Compare(hashOfInput, hash))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    public static string Sha1(string inputValue, HxEncodingType encodingType = HxEncodingType.None)
    {
        string Result = null;
        using (System.Security.Cryptography.SHA1 sha = new System.Security.Cryptography.SHA1CryptoServiceProvider())
        {
            byte[] data = sha.ComputeHash(GetString2Bytes(inputValue, encodingType));
            Result = GetBytes2String(data);
        }
        return Result;
    }

    #endregion
}

public enum HxEncodingType
{
    None = 0,
    Default,
    ASCII,
    UTF7,
    UTF8,
    UTF32,
    Unicode,
    BigEndianUnicode
}

HxCrypt_Userpark.cs

[PHP / Crypt / MD5Crypt]암호화, 복호화 Class (ADOdb의 crypt.inc.php

http://userpark.net/124

[C# / .NET / Crypt / HxCrypt]암호화, 복호화 Class

http://userpark.net/125

[ORACLE / PL/SQL / Crypt / HxCrypt]암호화, 복호화 PACKAGE(Class)

http://userpark.net/126