잼있는거 하나 더!!!

쿼리 결과를 HTML로 만든다.. 한방에~~ ㅋㅋ

sp_makewebtask

실행된 쿼리에서 반환된 데이터가 들어 있는 HTML 문서를 작성하는 작업을 만듭니다.

을 이용하라~~  MSDN에서 sp_makewebtask 가믄 자세히 나온다...


그럼 한번 빠져 봅시다!!


사전 작업

1. 첨부한 파일을 다운 받아 C:\에 넣으세요.. 물론 다른곳에 하고 싶으면 알아서 ㅋㅋ

2. 만약 경로가 다르다면 쿼리상에서도 당근 변경해 주셔야 겠죠???


예제 시작

USE pubs


-- A. 템플릿 파일을 사용하여 여러 쿼리 만들기

EXECUTE sp_makewebtask 
 @outputfile = 'C:\sp_makewebtask\MULTIPLE.HTM'
 , @query = ' SELECT title, price FROM titles 
   SELECT au_lname, au_fname FROM authors 
   SELECT pub_id, pub_name FROM publishers 
   SELECT au_lname, au_fname FROM authors
'
 , @templatefile = 'C:\sp_makewebtask\MULTIPLE.TPL'
 , @dbname = 'pubs', 
 @rowcnt = 5, 
 @whentype = 9
GO

-- 그럼 확인해 볼까요?? C:\sp_makewebtask로 이동하시면 MULTIPLE.HTM 파일 생겼죠?  신기 신기 ㅋㅋㅋ

 

-- B. 하이퍼링크 만들기
-- 테이블 생성
CREATE TABLE my_favorite_web_sites(url_def varchar(255), display_text varchar(255) NULL)
GO


-- 등록
INSERT my_favorite_web_sites(url_def, display_text) 
VALUES ('http://www.microsoft.com', 'Microsoft Home Page')
INSERT my_favorite_web_sites(url_def) VALUES ('http://www.widgets.microsoft.com')
GO


-- 실행
EXECUTE sp_makewebtask 
 @outputfile = 'C:\sp_makewebtask\URL.HTM'
 ,  @query='SELECT title, price FROM titles ORDER BY price desc'
 ,  @table_urls = 1
 , @tabborder = 0
 , @lastupdated=0
 , @colheaders = 0
 , @url_query= 'SELECT url_def, display_text FROM my_favorite_web_sites'
 , @whentype = 9
GO


-- 확인

-- URL.HTM 이라는 파일이 생겼죠? 실행 결과가 나오고... 링크도 걸린거 있죠???

@url_query  부분에 들어간 쿼리는 링크가 걸리는 거예용... 잘만 응용하믄 좋은 결과가 있을듯~~

 

-- C. 단일 행 모드로 여러 쿼리 실행
EXECUTE sp_makewebtask 
   @outputfile = 'C:\sp_makewebtask\SROW.HTM'
 , @query = 'SELECT title, price FROM titles ORDER BY price desc 
   SELECT au_lname, au_fname FROM authors WHERE state = ''CA'' 
'
 , @fixedfont = 0
 , @webpagetitle = 'Single row SQL Web Assistant'
 , @resultstitle = 'One row per page results'
 , @singlerow = 1
 , @rowcnt = 4
 , @URL = 'http://www.microsoft.com'
 , @reftext = 'Microsoft Home Page'
GO

 

-- 이번엔 Row 단위로 파일이 생겼습니다..

-- 참!! @rowcnt = 4로 주어기 때문에.. 파일이 8개 생겼죠?? 셀렉트문당 4개씩.. ^^

-- 이전 다음 버튼도 잇고 url로 추가해서 링크 걸수 있구용~~ 조아 조아~~

 

-- D. 데이터 삽입 표식 및 템플릿을 사용하여 여러 쿼리 실행
EXECUTE sp_makewebtask

 @outputfile = 'C:\sp_makewebtask\DATAINS.HTM'
 , @query = 'SELECT title, price FROM titles'
 , @templatefile = 'C:\sp_makewebtask\DATAINS.TPL'
 , @dbname = 'pubs'
 , @rowcnt = 10
 , @whentype = 9
 , @singlerow = 1
GO
-- @singlerow 를 0으로 하거나 지우면 한 파일에 생성되구용... 첫번째 결과랑 비슷하나.. 더욱 좋은 UI를 가져가고 싶다면 이번 방법이 좋겠죠???


MSDN에 나온 예제이구용...

잘만 응용하면 유용하게 쓰일듯 한뎁!! ㅋㅋ

신고
Posted by 나비 나비:D

ORACLE 내장함수

 

* abs() - 절대값구하기
array(배열) - 배열을 반환
asc() - 문자의 ANSI문자값을 반환
atn() - 아크 탄젠트로 반환
cbool() - boolean형식으로 반환
cbyte() - byte형식으로 반환
ccur() - currency형식으로 반환
cdate() - date형식으로 반환
cdbl() - double형식으로 반환
* chr() - asc()함수의 반대, ANSI문자를 일반문자로 변환
* cint() - interger형식으로 반환
clng() - long형식으로 반환
cos(각도) - 주어진 각도의 코사인 값을 반환
** createobject() - 자동화 인터페이스를 통해 다른 응용프로그램이나
프로그래밍 도구에 나타나는 객체에 대한 참조를 만들어 반환
cstr() - string형식으로 반환
** date() - 현재의 날자를 반환
dateserial(년,월,일) - 주어진 년,월,일에 대한 date형식을 반환
exp() - 주어진 수에대한 자연로그(승수로 거듭제곱하는 수) 밑인 e를 반환
filter(inputstring,값[,include[,비교]])) - inputstring는 검색할 문자열의 1차원 배열
include는 true나 false값을 가지는데, true로 설정되면 값발견시 그값을 가진
배열을 반환하고,flase로 설정되면 검색에 맞지않는 부분의 배열을 반환
비교는 비교형식을 나타내는 숫자값입니다
fix() - 주어진 수의 정수를 반환
* formatcurrency() - 원화 형식으로 반환 예) \1,000
formatdatetime() - 날자와 시간으로 형식화하여 반환
* formatnumber(숫자,소수자릿수) - 숫자로 형식화된 식을 반환 소숫점 몇째자리...
* formatpercent(수,소수자리수) - 퍼센트형식으로 반환 기본 소수2째자리까지.

hex() - 주어진 수를 16진수로 변환
hour() - 0과23 사이의 시간을 나타낸다
inputbox(프롬프트[,제목][,기본값][,xpos][,ypos][,helpfile,context]) - 대화상자
* instr([시작,]문자열1,문자열2[,비교]) - 시작은 문자열 검색 시작위치, 문자열1은 검색할
문자이며, 문자열2는 찾아야할 문자, 비교는 비교상수를 나타냄
* instrrev() - instr()과 반대로 문자 끝에서부터 검색
* int() - 주어진 수의 정수부분을 반환

isarray() - 주어진 변수가 배열인지 판단하는 boolean값을 반환
isdate() - 주어진 식이 날짜로 전환 될수 있는지 나타내는 boolean값을 반환
isempty() - 주어진 변수가 초기화 되었는지 나타내는 boolean값을 반환
isnull() - 주어진 식이 유효한 데이터가 있는지 없는지 boolean값을 반환
isnumberic() - 주어진 식이 숫자로 값이 정해질 수 있는지 나타내는 boolean값 반환
* isobject() - 자동화 객체를 참조하는지 여부를 나타내는 boolean값을 반환
join(목록[,구분기호]) - 배열에 포함되어 있는 여러 문자열을 결합하여 만든 문자열을 반환
모록은 1차원 배열이름, 구분기호는 문자열을 구분하기위해 사용하고 기본값은 "" 공백
lbound(배열이름[,차원]) - 주어진 배열에서 사용할 수 있는 가장 작은 첨자를 반환
배열이름은 배열 변수의 이름, 차원은 배열의 차원중 가장 작은 값을 나타냄
* lcase() - 주어진 문자를 소문자로 반환
** left(문자열,수) - 주어진 문자를 왼쪽으로부터 주어진 수만큼의 문자열을 반환
* len() - 주어진 문자열의 문자 개수를 세는 함수

loadpicture(그림파일의이름) - 그림객체를 반환
log() - 주어진수의 자연 로그를 반환
ltime() - 앞에 공백이 없는 문자열을 반환
** mid(문자열,시작[,길이]) - 주어진 문자열에서 주어진 위치에서부터의 길이만큼의 문자를 반환
minute(시간) - 시간에서 분값(0~59)을 반환
month(날짜) - 달의 값(1~12)을 반환
* monthname(달) - 주어진달의 숫자표현을 문자표현으로 바꾸어준다
msgbox(프롬프트[,단추][,제목][,helpfile,context]) - 프롬프트는 대화상자 안의 내용
단추는 단추모양,제목은 제목표시줄이름, helpfile은 도움말위치,context는 도움말 문맥번호
* now() - 현재 날자와 시간을 반환
oct() - 주어진 숫자의 8진수 값을 나타내는 문자를 반환
** replace(문자열,찾을문자열,바꿀문자열[,시작[,횟수[,비교]]]) - 문자열은 바꿀 대상, 찾을문자열은
문자열에서 바꾸어야 할 문자, 바꿀문자열은 찾은 문자열을 대치할 문자열, 시작은 검색시작위치
횟수는 바꾸는 횟수, 비교는 바꿀 때의 비교형식
** right(문자열,수) - 주어진 문자의 오른쪽으로 부터 주어진 수만큼의 문자를 반환
rnd(수) - 난수를 반환 수는 생략가능
round(수) - 반올림한 수를 반환, 소수 첫째자리 반올림
rtrim(문자열) - 뒤에 공백이 없는 문자열을 반환
scriptengine() - 사용중인 스크립트 언어를 나타낼때 사용
scriptenginebuildversion() - 사용중인 스크립트 엔진의 버전 수를 반환
scriptenginemajorversion() - 사용중인 스크립트 엔진의 주 버전 수를 반환
scriptengineminorversion() - 사용중인 스크립트 언어의 보조 버전 수를 반환
* second(시간) - 시스템 시간중 초값(0~59)을 반환
sgn(수) - 주어진 숫자의 부호를 나타내는 정수를 반환 0보다크면 1, 같으면 0, 작으면 -1을 반환
sin() - 주어진 라디안 값의 사인값을 반환
space(수) - 주어진 수만큼의 공백이 포함된 문자열을 반환
* split(문자열[,구분기호[,수[,비교]]]) - 문자열은 배열에 포함될 문자열, 구분기호는 문자열 구분하기
위한기호를 표시. 생략시 "" 공백, 수는 반환될 문자열의 수를 나타냄, 비교는 바꿀때의 비교형식
sqr(수) - 주어진 수의 제곱근을 반환
strcomp(문자열1,문자열2[,비교]) - 두개의 문자열을 비교하여 결과값을 반환
strreverse(문자열) - 주어진 문자열을 뒤에서부터 읽은 문자열을 반환
* string(수,문자) - 주어진 문자가 주어진 수만큼의 반복된 문자열을 반환
tan(수) - 주어진 라디안 값의 수 만큼의 탄젠트 값을 반환
* time() - 현재 시간을 반환
timeserial(시, 분, 초) - 주어진 시간,분,초에 대한 시간 값을 반환
timeval!!ue(시간) - 시간이 포함된 date형식을 반환해주는 함수
trim(문자열) - 앞뒤 공백이 없는 문자열을 반환해 줄 때 사용
typename(변수이름) - 주어진 변수의 정보를 나타내는 문자열 반환
ubound(배열이름[,차원]) - 배열의 차원으로 가장 큰 첨자를 반환
* ucase(문자열) - 주어진 문자를 대문자로 바꾸어 줍니다
vartype(변수) - 변수의 형식을 나타내는 값을 반환
weekday(날짜[,firstday of week]) - 요일을 나타내는 정수를 반환, firstday for week는 첫재요일을 숫자로
weekdayname(요일,약어, firsrday of week) - 요일은 숫자, 약어는 약어의 유무의 부울값
* year() - 현재의 년도를 나타낼때 쓰임

신고
Posted by 나비 나비:D

오라클에서는 왜 MSSQL에서 사용하는 임시 테이블을 만들수없을까
고민했던 적이 있었습니다. 임시테이블 만들수 있습니다. 있구요.
 
임시 테이블을 생성하되 duration은 transaction단위로 한다는 것을 기억해야 합니다.
아래의 순서를 따라 하시면 임시 테이블을 만드실 수 있습니다.
 
1. 임시 테이블생성
SQL> create global temporary table temp_tab (col1 number, col2 char(15))
     on commit delete rows ;
    
--> on commit delete rows 를 잘 기억해 두세요.
 [Commit 을 하면 삭제작업을 하는것으로 설정한다는 말입니다.]


2. 임시 테이블에 인덱스 만들기
SQL> create index temp_tab_n1 on  temp_tab(col1);
 
3. 임시 테이블에 데이터 인서트
SQL> insert into temp_tab values ( 1 , '구미지엔') ;
 
 ★ 주의 : Insert/Update 했다고 Commit 하시면 안됩니다.
 
4. 임시 테이블에서 데이터 가져오기
SQL> select * from temp_tab ;
     COL1 COL2
--------- ------------
              1 구미지엔
 
5. 임시 테이블 관찰
생김새를 자세히 보시려면 토드(Toad)에서 desc temp_tab 하시고

스크립트를 살펴보시면 기존에는 있던 Tablespace 구절이 빠져 있는것을 알 수 있습니다.
이는 임시테이블이나 인덱스가 메모리에 생성된다는 것을 말합니다.
DBA 업무 하시는 분들은 다이나믹 퍼포먼스 뷰 보시면(user_objects 등) 알수 있습니다.



6. 임시 테이블의 데이터를 메모리에서 내리기
SQL> commit ;
Commit complete.
 
7. 임시 테이블을 다시 보면
   duration이 transaction단위이기 때문에 commit을 수행하면 모든 data가 사라집니다.


일반적으로, Customizing Tablespace 에 만들어 둔 영구테이블들은
레포트가 실행 될때마다 테이블에 데이터가 계속 쌓이거나
테이블의 내용을 모두 지우거나 Truncate 한후
데이터를 다시 입력하는 번거러움이 있는 줄로 압니다.
 
그때 마다의 물리적인 I/O(삭제작업 시) 발생은 서버에 상당한 부하를 주겠죠

(물론 데이터 수십건 처리하는 트랜잭션이라면 부하를 주진 않겠죠. )

그래서 메모리를 사용하면 물리적인 I/O 가 메모리에서 발생하므로

다른 사용자 트랜잭션에 영향을 거의 주지 않겠죠.
또하나 , Disk 보다는 메모리의 속도가 빠르다.
 
위의 일반적인 방법과 임시테이블 사용에는 서로 장단점이 있겠습니다만,
실행 속도는 비교가 안되겠죠.(제 생각)

<임시테이블 관련 자료 출처 : 한국오라클>

신고
Posted by 나비 나비:D
출처 :http://cafe.naver.com/2007jsp.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=580

-- 먼저 로그인을 합니다.

% sqlplus /as sysdba

-- 오라클 시작 및 종료

SQL>startup restrict;    open까지 실행하고 일반사용자는 사용할 수 없음
SQL>startup force;       자동종료후 다시시작
SQL>startup nomount;
SQL>alter database mount;
SQL>alter database open;

-- 간단하게 하고 싶다!!!!
SQL>startup
SQL>shutdown abort

-- 디비에 뭐가 있나??

SQL>select * from v$database;

SQL>select * from all_users; 현재 생성된 사용자
SQL>select * from v$sga;

SQL>show sga   메모리 사용보기
SQL>show parameter;
SQL>select * from dba_users;

-- 사용자 생성, 변경, 삭제
%sqlplus system/manager
SQL>create user 사용자indetified by 암호
     2>default tablespace system  ( <- 별로 안좋은 설정. tablespace 만들고 한다. )
     3>temporary tablespace temp

     4>quota 1M on system;

변경
SQL>alter user 사용자 identified by 암호;

삭제
SQL>drop user 사용자 cascade; (가지고 있는 모든객체화 함께 삭제)

권한과 룰
create session, table, index, view, sequence, cluster
drop  " 
alter  "

grant [시스템권한] to [사용자명] with admin option;


보통은 사용자 추가후...

grant resource, connect to 사용자;  로 사용한다고 하더라

-- 전체테이블보기
select table_name from dba_tables

--제약조건 확인
SQL>select * from user_constrainsts where table_name='EMP';

-- 각테이블의 컬럼내용보기
SQL>desc [table_name];

-- 똑같은 구조의 테이블작성
SQL>create table emp_temp as select * from emp;

-- user 테이블스페이스

테이블스페이스 생성
SQL>create tablespace insa datafile '/disk2/oracle/insa_01.dbf'(절대경로/파일이름) size 1M;

테이블스페이스 크기추가
SQL>alter tablespace insa add datafile '/disk2/oracle/insa_02.dbf' size 1M;

데이터 파일 크기 변경
SQL>alter tablespace insa datafile '/disk2/oracle/insa_01.dbf' resize 3M;

데이터 파일 자동변경
SQL>alter tablespace insta datafile '/disk2/oracle/insa_02.dbf'
     2>autoextend on next 1M maxsize 10M;

테이블 스페이스삭제
SQL>drop tablespace insa including contents;
SQL>exit 한후에 디렉토리의 화일을 직접삭제...

-- 기본테이블스페이스
SQL>create user gracian identified by gracian1234 default tablespace system;

현재 기본테이블스페이스확인
SQL>select username, default_tablespace from dba_users;

 로컬리 테이블스페이스
SQL>create tablespace account datafile '/disk1/oracle/account_01.dbf' size 1M
     2>extent management local uniform size 50k;

신고
Posted by 나비 나비:D
CLOB을 VARCHAR로 변환
dbms_lob.substr을 사용한다.

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte );

select dbms_lob.substr( x, 4000, 1 ) from T;

PLSQL에서는 long형 변수에 할당하여 변환할 수 있다.


declare
    my_var long;
begin
    for x in ( select X from t )
    loop
       my_var := dbms_lob.substr( x.X, 32000, 1 );
       ....
신고
Posted by 나비 나비:D
출처 : http://gipsi.egloos.com/968963
Oracle Package 사용법-VB
CREATE OR REPLACE PACKAGE pk_test AS
TYPE t_cur IS REF CURSOR RETURN test%ROWTYPE;
PROCEDURE sp_test(v_cur OUT t_cur);
END pk_test;
/

CREATE OR REPLACE PACKAGE BODY pk_test AS
PROCEDURE sp_test(v_cur OUT t_cur) IS
BEGIN
OPEN v_cur FOR SELECT * FROM test;
END sp_test;
END pk_test;
/



Set Spcmd = New ADODB.Command
Spcmd.ActiveConnection = cnn
Spcmd.CommandType = adCmdStoredProc
Spcmd.CommandText = "pk_test.sp_test" '패키지명.프로시져명
Spcmd.Parameters(0).Direction = adParamOutput '받는 파라미터명
Set rst = Spcmd.Execute



* 다른 방법
-- 패키지머리 생성(정의)
CREATE OR REPLACE PACKAGE pk_test AS
TYPE t_cur IS REF CURSOR; --리턴받을 변수에 타입선언
PROCEDURE sp_test(i_table in varchar2,v_cur OUT t_cur); --수행할 프로시져 선언
END pk_test;
/

-- 패키지몸체 생성
CREATE OR REPLACE PACKAGE BODY pk_test AS
PROCEDURE sp_test(i_table in varchar2,v_cur OUT t_cur) IS
BEGIN
OPEN v_cur FOR 'SELECT * FROM '||i_table; --다이내믹 커서사용
END sp_test;
END pk_test;
/


VB사용시 예제>>
Private cnn As ADODB.Connection
Private rst As New ADODB.Recordset

Set Spcmd = New ADODB.Command '새 객체 생성
Spcmd.ActiveConnection = cnn '연결
Spcmd.CommandType = adCmdStoredProc '프로시져형태
Spcmd.CommandText = "pk_test.sp_test" '패키지프로시져명
Spcmd.Parameters.Append Spcmd.CreateParameter("i_table", adVarChar,
adParamInput, 10, "emp") '입력파라미터
Spcmd.Parameters(1).Direction = adParamOutput '출력파라미터
Set rst = Spcmd.Execute '수행
신고
Posted by 나비 나비:D
출처 : http://www.planetsourcecode.com/URLSEO/vb/Discussion/AskAProShowPost!asp/lngTopicId!33566/TopicCategory!XML_XSL+Questions/Flag!2/lngWId!1/blnURLSEO!true/Posting.htm

This isn't a question but a solution I found something that is worth a share here at this forum.

It had been a tedious job to handle CLOB data returned from Oracle 8i Stored Procedure into VB6 \ Visual Basic 6 using Microsoft ODBC. The support provided for handling CLOB is ONLY for SQL QUERIES where you fire to SELECT\INSERT\DELETE\UPDATE a CLOB COLUMN from a table, but NOBODY has really bothered about a RETURN VARIABLE as CLOB from ORACLE STORED PROCEDURE. Search on internet but it's useless.

I had this particular Stored Proc that was accepting a Key and depending on that Key, it would FETCH Data, and Make an XML string. This XML would extend upto 40-50K characters which is really large for VARCHAR2 \ LONG datatype to single handedly manage in oracle. Hence I had to use CLOB, but in VB (ADO 2.8) there was no associated DATATYPE or data-structre support that would accept this return value from my stored proc. (adVariant was most perfect but that is useless too)

.... (Poor Me I would bang my head against PC monitor that would show me sites providing help on CLOB Table, Oracle ODBC for CLOB or even CLOB JDBC support in JAVA but they would make me more helpless)

This is what I was doing

# Oracle Code


PROCEDURE GetXML
(temp IN NUMBER,
OutputXML OUT CLOB)
IS
--.....Variable Declaration
BEGIN
OutputXML :=EMPTY_CLOB();
DBMS_LOB.CREATETEMPORARY(OutputXML,TRUE);

stCurrentXMLNode := ''; --Load XML Denotion
--Code to get data, make XML and store into CLOB variable goes here....
DBMS_LOB.WRITEAPPEND(OutputXML, LENGTH(stCurrentXMLNode),stCurrentXMLNode); --Finally complete Loading Data in OUTPUT CLOB
COMMIT;
END GetXML;

But VB 6 was not supporting this OUT CLOB parameter OutputXML and would throw Microsoft ODBC inconsistent Datatype or Access Violation Error for every datatype I tried (VB adDataypes)

Then what I did is divided this collected CLOB in distinct ARRAY (Table) of VARCHAR2 variables of some fixed size(say 4000) Using this code

CREATE OR REPLACE PACKAGE BODY MYPACKEGE
AS
TYPE tblXML IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER; --VRACHAR2 ARRAY Table

PROCEDURE GetXML
(temp IN NUMBER,
OutputXML OUT tblXML)
IS
--.....Variable Declaration
BEGIN
OutputXML :=EMPTY_CLOB();
DBMS_LOB.CREATETEMPORARY(OutputXML,TRUE);

stCurrentXMLNode := ''; --Load XML Denotion
--Code to get data, make XML and store into CLOB variable goes here....
DBMS_LOB.WRITEAPPEND(OutputXML, LENGTH(stCurrentXMLNode),stCurrentXMLNode); --Finally complete Loading Data in OUTPUT CLOB

--********************************************************---
output_var := ' '; -- NonEmpty String
offset_var := 0; -- Offset To start chopping with
amount_var := 4000; -- Fixed SIze Varchar2

nCount := 0;
WHILE output_var <> ''
LOOP
DBMS_LOB.READ(OutputXML,amount_var,offset_var,output_var);
--Load character Chunk 'output_var' Cut from previous statement into Array element
OutputXML(nCount) := output_var;
nCount := nCount + 1;
offset_var:= amount_var + offset_var + 1;
END LOOP;
--********************************************************---
COMMIT;
END GetXML;
End MYPACKAGE;

Now use following VB 6 code to load this into a Recordset, then loope thru recordset and load it into a String (max size 65K characters) or variant(max size 2G Characaters)

# VB Code

Dim objCmd As New ADODB.Command
Dim objRS As New ADODB.Recordset

Dim vXML As Variant
With objCmd
.ActiveConnection = "{Microsoft ODBC DSN \ ConnectionString}"
.CommandType = adCmdText
.CommandTimeout = 30
.CommandText = "{call MYPACKAGE.GetXML(?, {resultset 10000, OutputXML})}"
.Parameters.Append .CreateParameter("temp", adNumeric, adParamInput, , 1234) 'Input Paramneter

Set objRS = .Execute 'Load Output VARCHAR2 Array into RecordSet

vXML = ""
While Not objRS.EOF
vXML = vXML & objRS(0).Value 'Loading Large CLOB (by looped appending) into a Variant
objRS.MoveNext
Wend
End With

vXML variable has your CLOB now ....

What Say ...
신고
Posted by 나비 나비:D

Build an Online Reporting Application Using Oracle XML Publisher


by Mark Rittman

Learn how to create an online reporting environment using XML Publisher technology, step by step.

Published June 2006

Part of the Oracle Fusion Middleware family of products, Oracle XML Publisher 5.6.2 is a Java-based product that gives you the ability to define, publish, secure and schedule reports built using SQL and XML data sources. Originally developed as a technology within E-Business Suite, Oracle XML Publisher is now available for download as a stand-alone product, and can be deployed using J2EE application servers such as Oracle Application Server Containers for J2EE (OC4J) or Apache Tomcat. Best of all, XML Publisher uses familiar desktop tools such as Microsoft Word and Adobe Acrobat to define report layouts, meaning that users can design their reports in a familiar environment without having to install and learn a whole new toolset.

XML Publisher consists of a server-based reporting engine that brings together report templates, the data that you wish to report on and any language translations, and then distributes the output in standard formats such as Adobe PDF, HTML, XML, RTF or Microsoft Excel.

Figure 1

Creating a clear separation between data, layout, and interface makes it easier to report the same data in different formats, easier to maintain a library of report definitions, and more straightforward to introduce features such as multiple language translation. XML Publisher is a standalone product that, like the rest of Oracle Fusion Middleware architecture, is “hot-swappable” and integrates with any standards-based database or Java application server.

New with the 5.6.2 release of XML Publisher is XML Publisher Enterprise, a complete environment for deploying, scheduling, and securing your reports. XML Publisher Enterprise provides users with a Web portal where they can upload their report templates, publish them, and then make them available to other users and groups within the organization.

XML Publisher uses a scheduling engine based on the OpenSymphony Quartz scheduler to run and then deliver reports that can then be accessed online, via email or via a WebDAV-enabled server.

So, now that XML Publisher 5.6.2 Enterprise is available, how can you use it to build an online reporting application?

Background

In this example, you will create an online reporting environment for your organization. You will create the initial set of reports that your colleagues will use, and you users will then maintain them and create ones of their own. Key requirements are that the reporting environment is Web-based, secure, and easy for users to navigate and use, reports can be run on demand or scheduled for regular execution, and users can export the data from their reports into familiar desktop application formats such as Microsoft Excel and Adobe Acrobat.

Your initial audience for reports consists of purchasing department users, who wish to view reports on vendor invoices, and a sales manager, who wishes to analyze sales data within his Web browser and through Microsoft Excel.

In these examples, the sales data that you will report on is held in the SH sample schema shipped with Oracle9i Database and Oracle Database 10g, whilst the vendor invoices data will be provided via an XML feed from your transactional application. To create your own report layout, download the SQL query and sample of the XML file here.

Defining an SQL-based Sales Analysis Report

The first report you will create will be the sales report, using data from the SH Sample Schema. To begin the process, you open up your Web browser and log on to XML Publisher Enterprise, so that you start to put together your report definition. After entering your username and password successfully, you view the XML Publisher Enterprise home page.

Figure 2

Down the left-hand side of the Web page is a tasks pane that contains links to create a new folder or report, or upload an existing report. On the right-hand side of the page is a set of sample reports shipped with XML Publisher Enterprise.

You click on the Create a new folder link to create a folder for the reports you are about to define, and call it “Sample Reports.” Grouping reports like this makes it easy to separate them out into subject areas, and you can later on specify which users and groups are able to access individual folders and reports.

Figure 3

You then navigate to the folder that you have just created, and click on the link to create a new report, which you call “sales report.” Once the report is created, you are presented with options to view, schedule, edit or view the execution history of the report.

Figure 4

Now that you have created the report, you need to edit the definition in order to specify the data model, any parameters the report might use and the report layout. To do this, you click on the Edit link under the report title. XML Publisher Enterprise then brings up a dynamic HTML interface that presents the report definition elements in a tree view.

Figure 5

An XML Publisher report definition comprises several elements:

  • The data model, which refers to either a database query or an XML document
  • The report template, which you will define later using XML Publisher Desktop
  • Any parameters used by the report, and
  • Any lists of values for the parameter drop-down lists

These four elements collectively form the report definition, which XML Publisher then holds in XML format on the application server mid-tier.

Next, you click on the Data Model node on the report tree view, click on the New button to create a new data model, and then select the JDBC data source that connects to your database. If one does not already exist, you will need to click on the Admin link at the top of the page to define a JDBC connection.

Figure 6

Once the data source is selected, you then press the New Query Builder button to choose the data items that will be returned from your database connection. The Query Builder gives you the ability to make a selection of database tables and columns using a graphical user interface.

Figure 7

Once you have made your data selection, you then add the join conditions and any other elements you would like to add to your query and then save it to XML Publisher Enterprise.

Figure 8

Now that you have defined your data model, it is time to layout your report template.

Report templates define how your data elements are arranged on the page, and unlike ad hoc query tools such as OracleBI Discoverer your template can be completely free form and include data from multiple queries. With XML Publisher, you define your report templates using standard desktop tools such as Microsoft Word and Adobe Acrobat, and the standard download of XML Publisher 5.6.2 includes an add-in to Microsoft Word called XML Publisher Desktop that automates much of the process of setting up the template. In these examples, you will use Microsoft Word and XML Publisher Desktop, an add-in to Microsoft Word, to define your templates. (A Flash demo that illustrates this process for a Word template is available here.)

Once you have installed XML Publisher Desktop, you will see an additional toolbar menu that provides access to XML Publisher functionality. As your first report is based on data accessed via an SQL query, you access the Data menu and then select Report Wizard.

Figure 9

You then step through the wizard, specifying the SQL query used to retrieve the data, the JDBC connection details of your database, and the name of the data source that you used when working in XML Publisher Enterprise

Figure 10

Once the wizard has completed, you are then presented with the default layout for your data items.

Figure 11

You can now use the formatting features in Microsoft Word to change the format of your table, add report headers and footers, change the fonts that are used, and add your organization’s branding. Although you can remove and reorder columns within the default report layout, for this report you decide to keep with the full set of columns as you intend to export the dataset later on into Microsoft Excel for further analysis.

You decide that this report would be even more effective with a chart to accompany it. Using XML Publisher Desktop this is a simple operation that is started via the Chart menu item on the Insert menu, which will then bring up the Chart dialog.

Figure 12

Using the Chart dialog, you specify a Vertical Bar Chart with Amount Sold grouped by Product Category.

Figure 13

Now, when you view your report template, a picture of a sample chart that represents your bar chart accompanies your table.

Figure 14

After previewing this template using the Preview menu item, you then save it to your PC as an RTF file ready for uploading to XML Publisher Enterprise.

To do this, you first of all click on the Layouts node of the report tree view, then click on New, and name your report layout.

Figure 15

Then, after clicking back on the Layouts node, you select the RTF file that contains your template and upload it to XML Publisher Enterprise.

Figure 16

Finally, just to test that your report works, you save the report definition, return to the report options and click on View to run the report.

Figure 17

Defining a Vendor Invoices Template Based On XML Data

The second report you wish to produce will take its data from an XML feed from your transactional system. Like your SQL-based report, the first step in creating this report is to log on to XML Publisher Enterprise to create a new report definition, which you name “Vendor Invoices.”

Figure 18

After entering the name of the report, as with the previous report you are then presented with options to view the report, schedule it, view the history of previous executions or edit its definition.

Figure 19

Next, as before, you click on the Edit link and begin the report definition process.

Using the Web interface, you first define the location of the feed that will provide your XML data, which in this case will be accessed via HTTP from your transactional application Web server.

Figure 20

If your report uses any parameters and any lists of values, you can add them at this stage. However your report does not use any parameters and so you click on the Layouts menu item and upload the report template you defined earlier.

Then, as with the sales report, you start XML Publisher Desktop so that you can define your report template. Unlike the sales report that was based on an SQL query, the vendor invoices report is based on an XML feed, and you therefore obtain an extract of this data feed as an XML document. As an alternative to an extract XML document, which may not contain all of the data elements that you might want to include in your template, you can instead base your template on an XML schema that fully defines the elements in your report, although of course this will not contain actual data and any previews you generate will contain data structures only.

For this template, you therefore initially load in your data using the XML Document entry in the XML Publisher Desktop Data menu.

Figure 21

Once XML Publisher Desktop confirms that the document is loaded, you can use the Insert menu to layout your template. Unlike the Report Wizard you used earlier, with XML data you are initially presented with a blank page, and it is up to you to add data items to the template.

For this template, you wish to list out the invoices grouped by the vendors that your organization deals with. To do this, you select Table/Form and then Advanced… from the Insert menu, and then individually select grouping items and table rows using the Insert Table/Form dialog.

Figure 22

Once you have selected your data items and grouped by vendor details, you can then format the resulting table, add your company branding, and then preview the results as a PDF file.

Figure 23

Then, once you are happy with the layout, you upload the template file to XML Publisher Enterprise, and run the report. This time, the report will take its data from the XML feed from your transactional application. Notice how the user can change the output format from HTML to PDF, XML, Microsoft Excel and XML.

Figure 24

Analyzing Your Sales Data

Some of your users may wish to further analyze their sales data to spot opportunities and uncover hidden trends. XML Publisher 5.6.2 Enterprise comes with an online analysis facility that gives your users the ability to analyze their data using just their Web browser.

To demonstrate this functionality, you navigate back to your sales report, run the report and then click on the Analyze button at the top right-hand side of your report. XML Publisher 5.6.2 Enterprise then presents you with a pivot table style interface that lets you drag and drop data elements onto a crosstab, in a similar style to an OracleBI Discover worksheet.

Figure 25

You drag the channel description field onto the Y-axis of the crosstab, and then drop the channel class item on top of it to nest the first item within the second. You do the same with Product Subcategory and Product Category, and drop Calendar Year into the Page Items area to allow users to “page through” years of data. Finally, you add Quantity Sold to the Data Items area and view the resulting page.

Figure 26

XML Publisher indexes and “rotates” the data from your report on the application mid-tier, and therefore there’s no extra load on your database or transactional system whilst you do this online analysis.

For users who would rather do their analysis using the familiar spreadsheet tool Microsoft Excel, the Excel Analyzer button starts your local copy of Microsoft Excel, installs a plug-in if it’s not already been downloaded, and then loads your report into an Excel worksheet. Using this plug-in, you can re-query the report using new parameter values, refresh the report with new data, and base additional worksheets off this data and include it in other calculations and graphs.

Report Scheduling and Distribution

Sometimes you might wish to schedule a report for later execution, or you might wish it to execute on a regular basis and distribute the results to a group of users. You wish to distribute your vendor invoice report on a weekly basis, and so you press the Schedule button at the top of the report to set this up. Before you do this though, you will need to have defined one or more delivery options using the Admin tab of XML Publisher 5.6.2 Enterprise.

To setup your schedule, you select one or more delivery destinations, which in the case of this report is an email message to the purchasing managers in your organization.

Figure 27

Using the Web interface, you give the scheduled job a name, and an email address to send notification through if the job should fail or complete with errors. The scheduler within XML Publisher 5.6.2 Enterprise gives you the option to run the job now, once in the future, or on a regular daily, weekly or monthly basis; you set the job up to run each Monday at 07:00 hours, with the report sent to an email distribution list and to yourself as a copy.

Granting Access to Users and Groups

The requirement from your organization is that only staff within the Purchasing Department should view the Vendor Invoices report, and other reports based on purchasing information, while sales reports should be viewable only by members of the Sales Department. Your online reporting application must reflect these security choices.

Within XML Publisher 5.6.2 Enterprise, individual users can belong to one or more roles, and roles can be granted access to one or more folders. This security can be made more granular by creating subfolders within other folders, and then using the copy report and paste report feature in XML Publisher 5.6.2 Enterprise to move your reports to the relevant subfolder.

Figure 28

Once your reports are in the correct folder, you then grant access to these folders to the relevant XML Publisher groups.

Figure 29

These roles, users and permissions are held internally within XML Publisher 5.6.2 Enterprise, but can be integrated with other LDAP servers.

Enhancing Your Templates

So far you have built two reports that are fairly simple to design and understand. You have used the wizards in Microsoft Word to lay out your template, and uploaded them to XML Publisher 5.6.2 Enterprise to be run online. But what if your requirements are more complex?

Most XML Publisher templates are created as RTF files which are then processed by the XML Publisher RTF Template Parser and converted into XSL-FO. When you design your report template using Microsoft Word, the wizards add data fields and other markup to your template using XML Publisher’s simplified tags for XSL expressions. These tags associate the XML report data to your report layout. If you are familiar with XSL and prefer not to use the simplified tags, XML Publisher also supports the use of pure XSL elements in the template.

In addition to Microsoft Word’s formatting features, XML Publisher supports other advanced reporting features such as conditional formatting, dynamic data columns, running totals, and charts. If you wish to include code directly in your template, you can include any XSL element, many FO elements, and a set of SQL expressions extended by XML Publisher. For more details, see the Oracle XML Publisher Enterprise User’s Guide that contains full details on how to extend XML Publisher templates.

Conclusion

XML Publisher 5.6.2 Enterprise is a complete environment for designing, building and running reports against both XML and SQL data. Using XML Publisher 5.6.2 Enterprise, you can design report layouts using familiar tools such as Microsoft Word and Adobe Acrobat, and deliver reports to your organization’s users using a secure, user-friendly Web-based environment.


Mark Rittman is a certified Oracle Database administrator and director of consulting at SolStonePlus, an Oracle partner based in the U.K. that specializes in business intelligence and data warehousing. He is the Oracle Magazine Editors' Choice ACE of the Year 2005, is chair of the U.K. Oracle User Group Business Intelligence & Reporting Tools SIG, and runs a blog at www.rittman.net.

출처 : 오라클

<
신고
Posted by 나비 나비:D

제목

ORACLE - SQL 레코드를 XML로 쉽게 변환하기

작성자

DEV.WON ( http://blog.naver.com/jadin1 )

작성일

2007-06-07

배포

PUBLIC (, 출처는 밝혀 주세요)

들어가기전 잡담.)

날씨가 더워지네요.. 더불어 4학년인 필자도 슬슬 졸업이 다가옵니다. 4학년1학기^^;;;

필자는 요즘 RoR에 빠졌습니다.

프레임워크 XML세팅에 슬슬 짜증이나던 찰나에, 에자일 방법론 이론에 가장 충실할거같다는 생각이 들어 접하게 된

Ruby On Rails .. Ruby의 언어적 이질감이 처음에는 압박이었으나, 모 나름대로 잼있네요..

솔직히 Java의 강력함과 오픈소스...뒤에 은근슬쩍생산성에 대한회의감이 드는 이유는 몰까요?

DB 엔티티 하나 고치게 되면 쭉이어지는 xml 설정값 , 어트리뷰트 들의 코드 수정들..ㅜㅜ

솔직히 그동안 필자는 왠지 인터프리터 방식의 스크립트에 대해 '몬가 부족하다' 혹은 '성능상 안좋을꺼야' 라고 생각했었는데요

스크립트이자, 객체지향 언어인 Ruby의 간결함과 Rails의 강력함에 매료되버렸습니다.

웹2.0 에 나름 충실한 Ajax에 대한 지원도 괜찮았구요.

나중에 시간이 되면 오라클 연동 RoR에 대해 강좌를 만들 생각입니다.

아 한참 다른데로 빠졌네요... 오늘은 MS-SQL의 SELECT ..... FOR XML AUTO 에 상응하는 오라클의 기능을 알아보도록하죠.

이게 무슨기능이냐구요? 바로 SQL 쿼리에 의해 반환된 레코드 집합을 XML로 표현하는겁니다.

요즘 프레임워크 설정부터 , 어플리케이션 인프라 Configuration , 웹서비스는 물론, Ajax,flex 등의 web rich client 등등..

XML의 사용빈도는 점점 증가 하고 있습니다.

이 시점에서 오라클에서 제공하는 XML 지원 패키지에 대해 알아보는 시간을 갖도록하죠.

편의상 짧은 어투 양해바랍니다..^^;

시작)

SELECT 절에 의해 생성된 레코드를 Java 나 pro* c , .net 등의 미들 tier 에서 별도의 XML파싱 작업을 하지않고,

오라클에서 제공하는 패키지를 이용하여 손쉽게 XML으로 표현할수 있다.

9i와 10g에서 XML을 다루는 패키지가 다르니 주의하기바란다.

SELECT * FROM PRT 라는 SQL에 의해 반환되는 레코드는 아래와 같다.

이제 필자가 작성한 10gXML제네릭 함수를 보도록하자.

(9i, 8i 사용자들은 아래의 코드에서 DBMS_XMLQUERY 부분을 XMLGEN으로 바꾸면 된다.

독자중 XMLGEN 패키지가 없는 분들은 oracle.com에서 xsu12.tar.gz 를 다운받아 압축을 푼후, xsu12.jar를

오라클 스키마에 올리도록한다.

이때 사용법은 loadjava -verbose -resolve -user scott/tiger xsu12.jar 이다. (기타 자세한 사용법은 loadjava 을 검색하라.)

(혹은 http://blog.naver.com/jadin1/70018342609이 아티클을 참고하길 바란다.)

함수코드)

-----------------------------------------------------------------------------------------

create or replace function GET_XML_RECORD
(xmlSQL IN VARCHAR2,
encodingType IN VARCHAR2 := 'UTF-8') return CLOB is



Ctx dbms_xmlquery.ctxHandle; -- SQL에서 XML로 컨버팅된 객체 핸들
xmlRecord CLOB; --리턴될 값

begin

--컨텍스트 얻어오기
Ctx := dbms_xmlquery.newContext(xmlSQL);

-- encoding 설정
dbms_xmlquery.setEncodingTag(Ctx,encodingType);

-- xml 생성
xmlRecord := dbms_xmlquery.getXML(Ctx);


RETURN xmlRecord;


end GET_XML_RECORD;
-----------------------------------------------------------------------------------------

사용예)

SELECT GET_XML_RECORD('SELECT * FROM PRT','EUC-KR') FROM DUAL;

결과값)


마지막으로 오라클 도큐먼트에서 도움이 될만한 부분 남깁니다.
DBMS_XMLQUERY패키지..

GETXML

Creates the new context, executes the query, gets the XML back and closes the context. This is a convenience function. The context doesn't have to be explicitly opened or closed. The options are described in the following table.

Syntax Description
FUNCTION getXML(  sqlQuery IN VARCHAR2,  metaType IN NUMBER := NONE) RETURN CLOB; 

This function uses a SQL query in string form.

FUNCTION getXML(  sqlQuery IN CLOB,  metaType IN NUMBER := NONE) RETURN CLOB; 

This function uses a SQL query in CLOB form.

FUNCTION getXML(  ctxHdl IN ctxType,  metaType IN NUMBER := NONE) RETURN CLOB; 

This function generates the XML document based on a SQL query used to initialize the context.

PROCEDURE getXML(  ctxHdl IN ctxType,  xDoc IN CLOB,  metaType IN NUMBER := NONE); 

This procedure generates the XML document based on the SQL query used to initialize the context.

Parameter IN / OUT Description
ctxHdl 
(IN) 

Context handle.

metaType 
(IN) 

XML metadata type (NONE, DTD, or SCHEMA).

sqlQuery 
(IN) 

SQL query.

xDoc 
(IN) 

CLOB into which to write the generated XML document.

위에서 특히나, GETXML 함수에 metaType을 지정하면 meta 정보 (DTD, Schema)가 포함되어 반환됩니다.
아주 유용하죠..
다시금 오라클의 강력함과 동시에 오라클에서 점점 B2B, EAI 에 적합한 XML Service에 비중을 많이 두는것 같은 기분이 듭니다.
그럼 부족한 강좌 끝까지 읽어주셔서 감사합니다.
<
신고
Posted by 나비 나비:D
오라클 내장 함수
오라클의 함수에는 단일행 함수복수행 함수(그룹함수)가 있다.


숫자 함수(number function)
ABS ACOS ASIN ATAN
ATAN2 BITAND CEIL COS
COSH EXP FLOOR LN
LOG MOD POWER ROUND(number)
SIGN SIN SINH SQRT
TAN TANH TRUNC(number) WIDTH_BUCKET

문자 함수(character function)
CHR CONCAT INITCAP LOWER
LPAD LTRIM NLS_INITCAP NLS_LOWER
NLSSORT NLS_UPPER REPLACE RPAD
RTRIM SOUNDEX SUBSTR TRANSLATE
TREAT TRIM UPPER ASCII
INSTR LENGTH, LENGTHB, LENGTHC, LENGTH2, LENGTH4

날짜와 날짜 처리함수(date, datetime function)
ADD_MONTHS CURRENT_DATE CURRENT_TIMESTAMP DBTIMEZONE
EXTRACT(datetime) FROM_TZ LAST_DAY LOCALTIMESTAMP
MONTHS_BETWEEN NEW_TIME NEXT_DAY NUMTODSINTERVAL
NUMTOYMINTERVAL ROUND(date) SESSIONTIMEZONE SYS_EXTRACT_UTC
SYSDATE SYSTIMESTAMP TO_DSINTERVAL TO_TIMESTAMP
TO_TIMESTAMP_TZ TO_YMINTERVAL TRUNC(date) TZ_OFFSET

데이터 형변환 함수(conversion function)
ASCIISTR BIN_TO_NUM CAST CHARTOROWID
COMPOSE CONVERT DECOMPOSE HEXTORAW
NUMTODSINTERVAL NUMTOYMINTERVAL RAWTOHEX RAWTONHEX
ROWIDTOCHAR ROWIDTONCHAR TO_CHAR(character) TO_CHAR(datetime)
TO_CHAR(number) TO_CLOB TO_DATE TO_DSINTERVAL
TO_LOB TO_MULTI_BYTE TO_NCHAR(character) TO_NCHAR(datetime)
TO_NCHAR(number) TO_NCLOB TO_NUMBER TO_SINGLE_BYTE
TO_YMINTERVAL TRANSLATE ... USING UNISTR
참조로 형변환 관계표를 참조하면 편리하다.
기타함수(miscellaneous single row function)
BFILENAME COALESCE DECODE DEPTH
DUMP EMPTY_BLOB
EMPTY_CLOB
EXISTSNODE EXTRACT(XML)
EXTRACTVALUE GREATEST LEAST NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID NLS_CHARSET_NAME NULLIF NVL
NVL2 PATH SYS_CONNECT_BY_PATH SYS_CONTEXT
SYS_DBURIGEN SYS_EXTRACT_UTC SYS_GUID SYS_TYPEID
SYS_XMLAGG SYS_XMLGEN UID UPDATEXML
USER USERENV VSIZE XMLAGG
XMLCOLATTVAL XMLCONCAT XMLFOREST XMLSEQUENCE
XMLTRANSFORM XMLELEMENT CASE

그룹함수 집계(Aggregate) 함수
AVG CORR COUNT COVAR_POP
COVAR_SAMP CUME_DIST DENSE_RANK FIRST
GROUP_ID GROUPING GROUPING_ID LAST
MAX MIN PERCENTILE_CONT PERCENTILE_DISC
PERCENT_RANK RANK REGR function STDDEV
STDDEV_POP STDDEV_SAMP SUM VAR_POP
VAR_SAMP VARIANCE GROUPING SETS
분석(Analytic) 함수
AVG CORR COUNT COVAR_POP
COVAR_SAMP CUME_DIST DENSE_RANK FIRST
FIRST_VALUE LAG LAST LAST_VALUE
LEAD MAX MIN NTILE
PERCENT_RANK PERCENTILE_CONT PERCENTILE_DISC RANK
RATIO_TO_REPORT REGR_(linear regression) function ROW_NUMBER STDDEV
STDDEV_POP STDDEV_SAMP SUM VAR_POP
VAR_SAMP VARIANCE TOP_N 분석

윈도우(windowing) 분석 함수

윈도우 분석함수부분 합을 계속 더해 나감, sum은 전체 합계를 구함


객체 참조 함수
DEREF MAKE_REF REF REFTOHEX VALUE
PseudoColumn을 의미하는 것
ROWIDEach row in the database has an address
ROWNUM테이블에서 select 되어진 행의 순서번호
LEVEL테이블에서 행(row)의 계층관계를 가리키는 일련번호 순서
신고
Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2017/10   »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        
Total : 882,180
Today : 171 Yesterday : 146