GyuHa.com

Working with LOBs in Oracle and PHP
by Harry Fuecks

Hitting the 4,000-byte limit? Enter LOBs...

Downloads for this article:
 Oracle Database 10g
 Zend Core for Oracle
 Apache HTTP Server 1.3 and later

Working with Oracle types like VARCHAR2 is fine, but what if you need to be able to store more than its 4,000-byte limit in one go? For this task, you need one of Oracle's Long Object (LOB) types, which in turn requires that you learn how to use the PHP API for working with LOBs. That in itself can be daunting for those unfamiliar with it.

In this "Oracle+PHP Cookbook" HowTo, you will learn the available LOB types and issues related to them, then explore examples of common LOB operations in PHP.Long Objects in OracleOracle provides the following LOB types:
  • BLOB, used to store binary data
  • CLOB, used to store character data using the database character set encoding
  • NCLOB, used to store Unicode character data using the national character set. Note that NCLOBs are, currently, not supported by the PHP OCI8 extension, which you'll be using here.
  • BFILE, used to reference external files under the operating system's filesystem
A further subcategory of LOB is the temporary LOB, which can be either a BLOB, CLOB, or NCLOB but is stored in the temporary tablespace until you free it.Note that older versions of Oracle provided the LONG and LONG RAW types for character and binary data, respectively. With Oracle9i these were deprecated in favor of LOBs.LOB storage. For the BLOB, CLOB, and NCLOB types, Oracle Database 10g is capable of storing up to 128TB in a single value, depending on your database block size and the "chunk" setting, defined for the LOB.A LOB itself comprises two elements: the LOB content and the LOB locator, which is a "pointer" to the LOB content. This separation is required to allow Oracle to store and manage LOBs efficiently and it is reflected in the PHP APIs you use to INSERT, UPDATE, and SELECT LOBs (see below).For the internal LOB types (i.e. not BFILEs) Oracle will store the content of the LOB "in-line" in the table, with the rest of the row, if the size of the LOB is less than 4KB. LOBs larger than 4KB are stored "out-of-line," by default in the table's tablespace. This approach allows small LOBs to be retrieved quickly while, for large LOBs, access times will be slower but overall performance, when scanning the table, is preserved.There are further options for LOB storage and access—such as memory caching and buffering—which may improve performance, depending on the specifics of your application. For further information see the LOB Performance Guidelines and the Oracle Database Application Developer's Guide - Large Objects in the Oracle documentation.Restrictions on LOBs. A number of restrictions apply to the use of LOB types, the most important being their use in SQL statements. You cannot use a LOB type in any of the following queries.
SELECT DISTINCT <lob_type>
ORDER BY <lob_type>
GROUP BY <lob_col>
It is also illegal to use a LOB type column for table joins, UNION, INTERSECTION, and MINUS statements.Further restrictions apply to other aspects of the use of LOBs, such as you cannot use LOB as a primary key column. Again, see Oracle Database Application Developer's Guide - Large Objects for details.CLOBs and Character SetsThe default character set for your database is defined by the parameter NLS_CHARACTERSET and text placed in a CLOB is expected to be encoded using this character set. Use this SQL to determine your databases character set encoding:
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'
Given lack of support for NCLOBs in PHP, you may want to consider using Unicode encoding as the database character, such as UTF-8, which can be done (given sufficient privileges) using the statement:
ALTER DATABASE CHARACTER SET UTF8
Note: do not attempt this without understanding the impact, especially if you have existing data or application code using a different character set. See the Oracle Globalization Support Guide and An Overview on Globalizing Oracle PHP Applications for more information.Working with LOBsThe discussion here will focus on PHP's OCI8 extension. It's also worth noting that Oracle provides the DBMS_LOB package, containing parallel procedures and functions for working with LOBs using PL/SQL.The PHP OCI8 extension registers a PHP class called "OCI-Lob" in the global PHP namespace. When you execute a SELECT statement, for example, where one of columns is a LOB type, PHP will bind this automatically to an OCI-Lob object instance. Once you have a reference to an OCI-Lob object, you can then call methods like load() and save() to access or modify the contents of the LOB.The available OCI-Lob methods will depend on your PHP version, PHP5 in particular having gained methods like read(), seek(), and append(). The PHP Manual is a little unclear, in this case, on the version numbers so if in doubt you can verify using the following script.
<?php
foreach (get_class_methods('OCI-Lob') as $method ) {
print "OCI-Lob::$method()\n";
}
?>
On my system, running PHP 5.0.5, I get the following list of methods:
OCI-Lob::load()
OCI-Lob::tell()
OCI-Lob::truncate()
OCI-Lob::erase()
OCI-Lob::flush()
OCI-Lob::setbuffering()
OCI-Lob::getbuffering()
OCI-Lob::rewind()
OCI-Lob::read()
OCI-Lob::eof()
OCI-Lob::seek()
OCI-Lob::write()
OCI-Lob::append()
OCI-Lob::size()
OCI-Lob::writetofile()
OCI-Lob::writetemporary()
OCI-Lob::close()
OCI-Lob::save()
OCI-Lob::savefile()
OCI-Lob::free()
In practice, the PHP 4.x OCI8 extension supports reading or writing of complete LOBs only, which is the most common use case in a Web application. PHP5 extends this to allow reading and writing of "chunks" of a LOB as well as supporting LOB buffering with the methods setBuffering() and getBuffering(). PHP5 also provides the stand-alone functions oci_lob_is_equal() and oci_lob_copy().The examples here will use the new PHP5 OCI function names (e.g. oci_parse instead of OCIParse). Examples use the following sequence and table:
CREATE SEQUENCE mylobs_id_seq
NOMINVALUE
NOMAXVALUE
NOCYCLE
CACHE 20
NOORDER
INCREMENT BY 1;

CREATE TABLE mylobs (
id NUMBER PRIMARY KEY,
mylob CLOB
)
Note that most of the examples here use CLOBs but the same logic can be applied almost exactly to BLOBs as well.Inserting a LOBTo INSERT an internal LOB, you first need to initialize the LOB using the respective Oracle EMPTY_BLOB or EMPTY_CLOB functions—you cannot update a LOB that contains a NULL value.Once initialized, you then bind the column to a PHP OCI-Lob object and update the LOB content via the object's save() method.The following script provides an example, returning the LOB type from the INSERT query:
<?php
// connect to DB etc...

$sql = "INSERT INTO
mylobs
(
id,
mylob
)
VALUES
(
mylobs_id_seq.NEXTVAL,
--Initialize as an empty CLOB
EMPTY_CLOB()
)
RETURNING
--Return the LOB locator
mylob INTO :mylob_loc";

$stmt = oci_parse($conn, $sql);

// Creates an "empty" OCI-Lob object to bind to the locator
$myLOB = oci_new_descriptor($conn, OCI_D_LOB);

// Bind the returned Oracle LOB locator to the PHP LOB object
oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_CLOB);

// Execute the statement using , OCI_DEFAULT - as a transaction
oci_execute($stmt, OCI_DEFAULT)
or die ("Unable to execute query\n");

// Now save a value to the LOB
if ( !$myLOB->save('INSERT: '.date('H:i:s',time())) ) {

// On error, rollback the transaction
oci_rollback($conn);

} else {

// On success, commit the transaction
oci_commit($conn);

}

// Free resources
oci_free_statement($stmt);
$myLOB->free();


// disconnect from DB etc.
?>
Notice how this example uses a transaction, instructing oci_execute with OCI_DEFAULT constant to wait for an oci_commit or an oci_rollback. This is important as I have two stages taking place in the INSERT—first create the row and second update the LOB.Note that if I was working with a BLOB type, the only change needed (assuming a BLOB column) is to the oci_bind_by_name call:
oci_bind_by_name($stmt, ":mylob_loc", $myLOB, -1, OCI_B_BLOB);
Alternatively, you can bind a string directly without specifying a LOB type;
<?php
// etc.

$sql = "INSERT INTO
mylobs
(
id,
mylob
)
VALUES
(
mylobs_id_seq.NEXTVAL,
:string
)
";

$stmt = oci_parse($conn, $sql);

$string = 'INSERT: '.date('H:i:s',time());

oci_bind_by_name($stmt, ':string', $string);

oci_execute($stmt)
or die ("Unable to execute query\n");

// etc.
?>
This approach simplifies the code significantly and is suitable when the data your want to write to the LOB is relatively small. In contrast, if you wished to stream the contents of the large file into a LOB, you might loop through the contents of the file callingwrite() and flush() on the PHP LOB object to write smaller chunks rather than having the entire file held in memory at a single instance.Selecting a LOBWhen a SELECT query contains a LOB column, PHP will automatically bind the column to an OCI-Lob object. For example:
<?php
// etc.

$sql = "SELECT
*
FROM
mylobs
ORDER BY
Id
";

$stmt = oci_parse($conn, $sql);

oci_execute($stmt)
or die ("Unable to execute query\n");

while ( $row = oci_fetch_assoc($stmt) ) {
print "ID: {$row['ID']}, ";

// Call the load() method to get the contents of the LOB
print $row['MYLOB']->load()."\n";
}

// etc.
?>
This can be further simplified using the OCI_RETURN_LOBS constant, used with oci_fetch_array(), instructing it to replace LOB objects with their values:
while ( $row = oci_fetch_array($stmt, OCI_ASSOC+OCI_RETURN_LOBS) ) {
print "ID: {$row['ID']}, {$row['MYLOB']}\n";
}
Updating a LOBTo UPDATE a LOB, it's also possible to use the "RETURNING" command in the SQL, as with the above INSERT example, but a simpler approach is to SELECT ... FOR UPDATE:
<?php
// etc.

$sql = "SELECT
mylob
FROM
mylobs
WHERE
id = 3
FOR UPDATE /* locks the row */
";

$stmt = oci_parse($conn, $sql);

// Execute the statement using OCI_DEFAULT (begin a transaction)
oci_execute($stmt, OCI_DEFAULT)
or die ("Unable to execute query\n");

// Fetch the SELECTed row
if ( FALSE === ($row = oci_fetch_assoc($stmt) ) ) {
oci_rollback($conn);
die ("Unable to fetch row\n");
}

// Discard the existing LOB contents
if ( !$row['MYLOB']->truncate() ) {
oci_rollback($conn);
die ("Failed to truncate LOB\n");
}

// Now save a value to the LOB
if ( !$row['MYLOB']->save('UPDATE: '.date('H:i:s',time()) ) ) {

// On error, rollback the transaction
oci_rollback($conn);

} else {

// On success, commit the transaction
oci_commit($conn);

}

// Free resources
oci_free_statement($stmt);
$row['MYLOB']->free();


// etc.
?>
As with the INSERT, I need to perform the UPDATE using a transaction. An important additional step is the call to truncate(). When updating a LOB with save(), it will replace the contents of the LOB beginning from the start up to the length of the new data. That means older content (if it was longer than the new content) may still be left in the LOB.For PHP 4.x, where truncate() is unavailable, the following alternative solution uses Oracle's EMPTY_CLOB() function to erase any existing contents in the LOB before saving new data to it.
$sql = "UPDATE
mylobs
SET
mylob = EMPTY_CLOB()
WHERE
id = 2403
RETURNING
mylob INTO :mylob
";

$stmt = OCIParse($conn, $sql);

$mylob = OCINewDescriptor($conn,OCI_D_LOB);

OCIBindByName($stmt,':mylob',$mylob, -1, OCI_B_CLOB);

// Execute the statement using OCI_DEFAULT (begin a transaction)
OCIExecute($stmt, OCI_DEFAULT)
or die ("Unable to execute query\n");

if ( !$mylob->save( 'UPDATE: '.date('H:i:s',time()) ) ) {

OCIRollback($conn);
die("Unable to update lob\n");

}

OCICommit($conn);
$mylob->free();
OCIFreeStatement($stmt);
Working with BFILESWhen using the BFILE type, INSERTs and UPDATEs mean telling Oracle where the file is located within the filesystem of the database server (which may not be the same machine as the Web server), rather than passing the file content. Using a SELECT statement, you can read the contents of the BFILE through Oracle, should you so desire, or call functions and procedures from the DBMS_LOB package to get information about the file.The main advantage of BFILEs is being able to access the original files directly from the filesystem while still being able to locate files using SQL. This means, for example, images can be served directly by the Web server while I can keep track of the relationship between the table containing the BFILES and, say, a "users" table, telling me who uploaded the files.As an example, I first need to update the table schema used above;
ALTER TABLE mylobs ADD( mybfile BFILE )
Next I need to register a directory alias with Oracle (this requires administrative privileges) and grant permissions to read it:
CREATE DIRECTORY IMAGES_DIR AS '/home/harryf/public_html/images'
GRANT READ ON DIRECTORY IMAGES_DIR TO scott
I can now INSERT some BFILE names like:
<?php
// etc.

// Build an INSERT for the BFILE names
$sql = "INSERT INTO
mylobs
(
id,
mybfile
)
VALUES
(
mylobs_id_seq.NEXTVAL,
/*
Pass the file name using the Oracle directory reference
I created called IMAGES_DIR
*/
BFILENAME('IMAGES_DIR',:filename)
)";

$stmt = oci_parse($conn, $sql);

// Open the directory
$dir = '/home/harryf/public_html/images';
$dh = opendir($dir)
or die("Unable to open $dir");

// Loop through the contents of the directory
while (false !== ( $entry = readdir($dh) ) ) {

// Match only files with the extension .jpg, .gif or .png
if ( is_file($dir.'/'.$entry) && preg_match('/\.(jpg|gif|png)$/',$entry) ) {

// Bind the filename of the statement
oci_bind_by_name($stmt, ":filename", $entry);

// Execute the statement
if ( oci_execute($stmt) ) {
print "$entry added\n";
}
}

}
If I need to, I can read the BFILE content through Oracle using the same approach as above where I selected CLOBs. Alternatively, if I need to get the filenames back so I can access them directly from the filesystem, I can call the DBMS_LOB.FILEGETNAMEprocedure like:
<?php
// etc.

$sql = "SELECT
id
FROM
mylobs
WHERE
-- Select only BFILES which are not null
mybfile IS NOT NULL;

$stmt1 = oci_parse($conn, $sql);

oci_execute($stmt1)
or die ("Unable to execute query\n");

$sql = "DECLARE
locator BFILE;
diralias VARCHAR2(30);
filename VARCHAR2(30);

BEGIN

SELECT
mybfile INTO locator
FROM
mylobs
WHERE
id = :id;

-- Get the filename from the BFILE
DBMS_LOB.FILEGETNAME(locator, diralias, filename);

-- Assign OUT params to bind parameters
:diralias:=diralias;
:filename:=filename;

END;";

$stmt2 = oci_parse($conn, $sql);

while ( $row = oci_fetch_assoc ($stmt1) ) {

oci_bind_by_name($stmt2, ":id", $row['ID']);
oci_bind_by_name ($stmt2, ":diralias", $diralias,30);
oci_bind_by_name ($stmt2, ":filename", $filename,30);

oci_execute($stmt2);
print "{$row['ID']}: $diralias/$filename\n";

}
// etc.
?>
Furthermore, you can use the DBMS_LOB.FILEEXISTS function to discover which files have been deleted via the operating system but are still referenced in the database.ConclusionIn this HowTo you have been introduced to the different types of LOBs available in Oracle Database 10g and hopefully now understand their role in allowing large data entities to be stored efficiently in the database. You have also learned how to work with LOBs using PHP's OCI8 API, covering the common use cases you will encounter while developing with Oracle and PHP.

Comment +0

..

Comment +0

1. PHP 설치

1.1 최신 버젼인 PHP 5.1.2 버젼을 다운로드 : http://www.php.net/downloads.php

다운로드를 할때, 버젼은 "Collection of PECL modules for PHP 5.1.2 " 을 다운로드를 한다. PECL은 PHP의 광범위한 확장 라이브러리 모듈을 제공한다 한다.

msi 인스톨러보다는, zip 압축을 다운받기를 바람..

확장라이브러리는 http://pear.php.net/packages.php 에서 필요한 수많은 모듈을 다운로드 할수있다.

블로그중에, PECL 관련 포스트도 참고해 보기 바란다.
http://blog.naver.com/jegom?Redirect=Log&logNo=120004156186

1.2 php.ini-recommand 를 php.ini 로 변경

압축을 푼 PHP 디렉토리에서, php.ini* 로 되어 있는 파일은, PHP에서 권장하는 설정값이 적용되어 있는 파일이다.

2. PHP 환경설정

2.1 php.ini에서 cgi.force_redirect 값이 0 인지 확인.

2.2 browscap.ini 파일 설치하기

꼭 설치할 필요는 없다. 다만, PHP에서 브라우져 특성에 따라 최적의 스크립트 작동을 하게 된다.

php_browscap.ini는 http://www.garykeith.com/browsers/downloads.asp 에서 다운로드가 가능하며,  PHP 설치디렉토리의 extras에 복사해 넣고, php.ini에서 browscap 에 다음과 같이 경로를 지정해 준다.

browscap=C:\PHP\extras\browscap.ini

2.3 php.ini 에서 세션파일 저장 경로 설정.

session.save_path=C:\PHP\Sessions

2.4 PHP 확장 라이브러리 설정

추가적인 확장모듈을 설치하고자 한다면 다음 웹사이트를 방문해 보기 바란다.
http://ca3.php.net/manual/en/install.windows.extensions.php

php.ini 에서 extension_dir 확장모듈 경로를 지정해준다.

 "C:\PHP\extension"

extension=php_mbstring.dll
extension=php_bz2.dll
extension=php_cpdf.dll
extension=php_curl.dll
extension=php_dba.dll
extension=php_dbase.dll
extension=php_dbx.dll
;extension=php_exif.dll
extension=php_fdf.dll
extension=php_filepro.dll
extension=php_gd2.dll
extension=php_gettext.dll
;extension=php_ifx.dll
extension=php_iisfunc.dll
extension=php_imap.dll
extension=php_interbase.dll
extension=php_java.dll
extension=php_ldap.dll
;extension=php_mcrypt.dll
extension=php_mhash.dll
extension=php_mime_magic.dll
extension=php_ming.dll
extension=php_mssql.dll
extension=php_msql.dll
extension=php_mysql.dll
extension=php_mysqli.dll
;extension=php_oci8.dll
extension=php_openssl.dll
;extension=php_oracle.dll
extension=php_pdf.dll
extension=php_pgsql.dll
extension=php_shmop.dll
extension=php_snmp.dll
extension=php_sockets.dll
;extension=php_sybase_ct.dll
extension=php_tidy.dll
extension=php_ffi.dll
extension=php_xmlrpc.dll
extension=php_xsl.dll
;extension=php_yaz.dll
extension=php_zip.dll

위와 같이 on 인 확장모듈은 굳이 on을 하지 않아도 된다. 그러나 on을 해도 대부분 오류가 없다. 다 off 해도 좋은나, 예로 mssql을 이용한다면 php_mssql.dll 은 on을 해야 한다.

2.5 php dll 파일 경로설정.

다음 2가지 방법중 1가지를 하면 된다. 대부분 첫번째 a 방법을 선택한다.

a. C:\PHP의 php-dll 이 아닌 모든 dll 파일을 System32에 복사한다. 또는,

b. Windows PATH 경로에서 "C:\PHP" 를 추가해 준다.
이 방법은 추후 업그레이드나 삭제를 훨씬 쉽게 한다. System32에 복사해서 여기저기 퍼져 있으면 추후 업그레이드나 삭제시 잊어버리게 된다.

2.6 레지스트리에 PHP 설치경로 추가. 

이 설정은 php의 환경설정을 지정된 경로에서 찾게 한다. 굳이 php.ini를 Windows 디렉토리에 복사해 넣을 필요가 없다.

HKEY_LOCAL_MACHINE\SOFTWARE\PHP\IniFilePath = C:\PHP

다음을 텍스트파일에 .reg 확장자로 저장후 실행하면 쉽게 적용된다.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\PHP]
"IniFilePath"="C:\\PHP"

3. IIS에 PHP 설정하기

3.1 IIS의 웹서비스 확장에 추가한다.

확장 이름에는 "PHP ISAPI Extension" 를 추가하고 확장모듈 dll을 PHP 설치디렉토리의 php5isapi.dll 파일을 지정한다. 그런다음에 허용설정을 해준다.

php를 실행하는 방법은 지금과 같이 ISAPI 모듈을 이용하는 것과 php-cgi.exe 를 이용하는 방법이 있다. 되도록이면, ISAPI 모듈을 이용하는 것이 좋다.

*php를 적용하는 방법에는 2가지가 있다. 웹 루트에 적용하는 것과 특정 웹사이트에 적용하는 방법이 있으니 적용코자 하는 경우에 따라 선택하면 된다.

3.2 .php 확장자를 추가해 준다.

역시 .php 확장에 대한 실행경로는 php5isapi.dll 을 지정해 주며, 동사에는 GET, POST, HEAD 만 허용해 준다. 굳이 많이 허용을 해줘봤자 보안에 구멍만 만들어 줄 뿐이다.

3.3 IIS를 재시작 한다. 만약 Window PATH 에 적용했다면 리붓팅을 해준다.

4. 웹페이지 테스트

<html>
 <head>
  <title>PHP Test</title>
 </head>
 <body>
  <!-- testing sessions -->
  <?php session_start(); ?>
 
  <!-- testing browscap.ini -->
  <?php
   echo $_SERVER['HTTP_USER_AGENT'] . "<br/><br/>";
   $browser = get_browser(null, true);
   print_r($browser);
   echo "<br/><br/>";
  ?>
 
  <!-- a general PHP test -->
  <?php phpinfo(); ?>
 </body>
</html>

test.php 파일에 다음과 같은 코드로 php 실행 여부 및 환경설정값을 확인할 수 있다.

5. 트러블슈팅 

5.1 작동하지 않는다면,..

해당 웹사이트의 ISAPI 필터에 필터이름 "PHP" 에 실행파일을 php5isapi.dll 을 지정해 줘 본다. 물론 등록후에는 IIS를 재시작 해야 만이 필터가 로드된다.

ISAPI 필터에 등록되면, 웹서버의 성능상 그리 권장사항은 아니다. 필터가 많을수록 웹요청에 대한 필터 처리를 통과해야 하기 때문이다.

5.2 browscap.ini 에서 오류 발생시...,

php.ini에서 browscap의 실제 경로를 다시 확인하여 지정해줘 본다.

5.3 401 권한오류 또는 403 퍼미션 거부 오류.

이 부분은 ISAPI 모듈이 아니라, CGI(php-cgi.exe)를 이용한 경우가 그렇다. cgi를 이용한다면 해당 웹사이트를 실행하는 어플리케이션 풀의 실행 계정이 PHP 설치경로 권한이 있는지 확인해 본다.

5.4 세션이 저장되지 않는다.,,,

세션은 앞서 지정했던 php.ini 파일에 지정한 경로에 인터넷게스트계정(IUSR_) 이 쓰기권한 정도의 권한을 지정해 준다.

Comment +0

일단 새텍스트 파일을 하나 열어서 아래 내용을 붙여넣으세요

X5O!P%@AP[4\PZX54(P^)7CC)7}$EICAR-STANDARD-ANTIVIRUS-TEST-FILE!$H+H*

그런 다음 파일이름을 test 확장자는 com,
그러니까 파일이름을 test.com으로 바꾸세요.
그런후 그 파일을 압축해서 이메일로 자기자신한테 보내고 받아서
마우스 오른쪽 클릭해서 자신의 바이러스 백신으로 스캔하세요.

만약 이 파일을 바이러스로 판정하면 님의 바이러스 백신은 제대로 작동중인거고
바이러스라고 경고가 안나오면 지금 당장 컴터 코드 뽑고 창문 열어서 컴터 밖으로 집어던지세요.
바이러스도 하나 못잡으면 그게 쑤뤠기지 백신이 아녜효 -_-;;;;;

p.s. 그리고 바이러스라고 해도 절대 무해한 파일이니까 걱정은 안하셔도 됩니다.
EICAR test라고 유명한 테스트입니다.

Comment +0