Posts Tagged ‘ PL/SQL

Passing of BOOLEAN parameters to PL/SQL stored procedures

ปัญหาเกิดจาก function ใน oracle ที่เขียนขึ้นมานั้น มี parameter type เป็น boolean แต่ JDBC ไม่ support การส่งค่าแบบ boolean ไปใน function ถ้าทดลองส่งไปจะเกิด exception ประมาณนี้

java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'FN_TEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:218)

วิธีการแก้ปัญหาก็บ้านๆ มากครับ สร้าง function ที่รับ parameter ที่ไม่ใช่ boolean มาแล้ว ไปเรียก function ที่ต้องส่ง boolean เข้าไปอีกที่ ประมาณนี้

CREATE OR REPLACE FUNCTION boolFunc(x boolean)
RETURN VARCHAR2 AS
BEGIN
	//code 
	return "OK";
END;

CREATE OR REPLACE FUNCTION boolwrap(x int)
RETURN VARCHAR2 AS
BEGIN
	IF (x=1) THEN
	  return boolFunc(TRUE);
	ELSE
	  return boolFunc(FALSE);
	END IF;
END;

เวลาเรียกใช้งานก็ เรียก function boolwrap แทนที่ boolFunc

แหล้งข้อมูล
http://docs.oracle.com/cd/B19306_01/java.102/b14355/apxtblsh.htm#i1005380

ข้อแตกต่างระหว่าง stored procedures และ functions ใน PL/SQL

ข้อแตกต่างระหว่าง stored procedures และ functions ใน PL/SQL มีดังนี้
1. function ต้องมีการคืนค่า แต่ procedure ไม่จำเป็นต้องคืนค่าก็ได้
2. ไม่สามารถใช้คำสั่ง “select functionXX() from dual” ในกรณีที่ ใน function นั้นมีการใช้คำสั่ง DML (คำสั่ง insert,update, delete)
3. แต่ถ้าใน function มีการใช้ “autonomous transaction” ก็สามารถใช้ได้
4. ไม่สามารถเรียกใช้ procedure ผ่าน คำสั่ง SQL Query เช่น “select procedureXX() from dual” ได้

Using dynamic SQL in PL/SQL

เวลาที่ต้องการใช้คำสั่ง SQL ใน PL/SQL แบบ Dynamic นั้นสามารถทำได้โดยใช้คำสั่ง

EXECUTE IMMEDIATE

ดังเช่นตัวอย่างนี้

CREATE OR REPLACE PROCEDURE DELETE_DATA IS
    V_DEL   NUMBER := 0;
    V_I     NUMBER := 0;
    V_SQL   VARCHAR(1000);
    
BEGIN
    --Create sql statement
    V_SQL := 'DELETE FROM TMP WHERE ID = :1';
    
    --Loop for delete data
    FOR V_I IN 1..5 LOOP
        EXECUTE IMMEDIATE V_SQL USING V_I;
        V_DEL := V_DEL+SQL%ROWCOUNT;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Delete1 : '||V_DEL||' Record(s)');    
    
    ROLLBACK;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        NULL;
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END DELETE_DATA;
/

ประเภทของ Cursor ใน PL/SQL

Cursor เป็นโครงสร้างข้อมูลของ PL/SQL หรือชนิดข้อมูล pointer ที่อ้างอิงถึง work area เมื่อมีการรันคำสั่ง SQL ในฐานข้อมูล Oracle จะมีการสร้างพื้นที่ในหน่วยความจำภายในฐานข้อมูลเพื่อใช้ในการทำงานกับคำสั่ง SQL Cursor แบ่่งออกเป็นสองประเภทดังนี้
Implicit cursors :
คือ Cursor ที่ถูกสร้างโดยอัตโนมัติ เมื่อมีการเรียกใช้คำสั่ง DML เช่น INSERT, UPDATE, และ DELETE และ SELECT ที่ส่งคืนค่าเพียงรายการเดียว
Explicit cursors:
คือ Cursor ที่ผู้ใช้งานสร้างขึ้นเอง ซึ่งต้องเขียนคำสั่งต่างๆ เพื่อมาจัดการ Cursor เอง

คำสั่ง หรือ สถานะ ต่างๆที่ Oracle เตรียมไว้ให้สำหรับ Implicit cursors
%FOUND คืนค่าเป็น TRUE เมื่อคำสั่ง DML เช่น INSERT, UPDATE, และ DELETE และ SELECT คืนค่ามาอย่างน้อย หนึ่ง รายการ รูปแบบการใช้งานเช่น

SQL%FOUND

%NOTFOUND การทำงานตรงข้ามกับ %FOUND รูปแบบการใช้งานเช่น

SQL%NOTFOUND

%ROWCOUNT แสดงจำนวนรายการที่เกิดจากคำสั่ง DML รูปแบบการใช้งานเช่น

SQL%ROWCOUNT

ตัวอย่างการใช้งาน

CREATE OR REPLACE PROCEDURE DELETE_DATA IS
BEGIN
    DELETE FROM TMP WHERE ID = '1';    
    DBMS_OUTPUT.PUT_LINE('Delete1 : '||SQL%ROWCOUNT||' Row(s)');
    
    DELETE FROM TMP;    
    DBMS_OUTPUT.PUT_LINE('Delete2 : '||SQL%ROWCOUNT||' Row(s)');
    
    ROLLBACK;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       NULL;
    WHEN OTHERS THEN
       RAISE;
END DELETE_DATA;

ข้อมูลจาก
What are Cursors?

ORA-29280: invalid directory path

ปัญหาที่ผมเจอจากการเขียน PL/SQL ในการ import text file ซึ่งมีการไปอ่านไฟล์จาก directory ที่กำหนดไว้แล้วบันทึกลงฐานข้อมูล (ใช้ package UTL_FILE ของ Oracle) โดยติดปัญหาที่คำสั่ง

in_file := UTL_FILE.fopen ("Path of file", "File name", 'R');

เพราะทดลองรันแล้วจะขึ้น “ORA-29280: invalid directory path” ลองดีบั๊กดูจึงรู้ว่าพอถึงคำสั่งเปิดไฟล์ขึ้นมาอ่านก็ขึ้น error ตามที่กล่าว ตอนแรกผมคิดว่าอาจเป็นเพราะ User oradb นั้นไม่มีสิทธิ์เข้าถึงหรืออ่านข้อมูลจาก Directory ในเครื่องผมหรือเปล่า จัดแจงเพิ่มสิทธิ์เรียบร้อยก็ไม่ได้ สุดท้ายต้องเพิ่งอาจารย์ google ได้ความว่า
1. ในการอ่านเขียน File ผ่าน PL/SQL นั้นจะต้องมีการกำหนด Path ของ Directory ในตัวแปร “utl_file_dir” ก่อน ซึ่งหลายๆที่ก็บอกให้ไปกำหนดใน init.ora เช่น

utl_file_dir = full_path_for_directory

ซึ่งผมลองแล้วก็ไม่ได้
2. ให้ตรวจสอบดูว่า ตัวแปรระบบ ที่ชื่อ “UTL_FILE_DIR” นั้นมีค่าเป็นอะไร ด้วยคำสั่งดังนี้

select name,value,display_value from v$parameter where name = 'utl_file_dir';

แล้วข้อมูลที่ได้คือ ค่า value และ display_value เป็นค่า null คือไม่มีค่าอะไร เมื่อไม่มีค่าอะไรผมก็หาวิีธีในการ set ค่า “UTL_FILE_DIR” แล้วก็ได้คำสั่ง

ALTER SYSTEM SET UTL_FILE_DIR='directory_path' scope=spfile;

และยังได้ความรู้เพิ่มมาอีกว่า สามารถกำหนดหลายๆ Directory ได้โดยใช้ “,” เช่นอยากเปลี่ยนเป็น “C:\tmp,C:\data\tmp” ที่ง่ายกว่านั้นก็คือ สามารถใช้ “.” แทนด้วยทุก Directory ตามแต่ที่เราจะใส่เข้าไป เมื่อแก้ไขเสร็จทำการ stop oracle service และ start service มาใหม่ จากนั้นทดลองใช้

select name,value,display_value from v$parameter where name = 'utl_file_dir';

ทีนี้ค่า value และ display_value ก็เปลี่ยนมาเป็น “*” แล้ว ทดลองรัน PL/SQL ที่เขียนขึ้นมาก็ใช้ได้แล้ว