Home | About SQeeL | Document | DownLoad | Mail Japanese
Home > Document > SQeeL Reference

Oracle operation function

  • Built-in function
    • commit()
    Description
    Commits the DML statements (SELECT, INSERT, UPDATE or DELETE)
    The current transaction is rolled back when you execute the logout() or the exit statement
    Syntax
    ret = commit()
    Return Value
    1:Success
    0:Fail
    System Return Value
    ERROR
    0Success
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase message
    DBUSER
    •ΆŽš—ρConnected username to database
    Requirements
    dboralib.slb
    Example
    require 'dboralib.slb'

function run()

	connect( 'scott/tiger' )

	sql insert into DEPT (DEPTNO, DNAME, LOC) values (50, 'LOGISTICS', 'LOS ANGELES');

	if DBERR != 0
		rollback
	else
		commit
	endif

	logout

endfunction


//fetch//
function run2()

local dept
connect scott/tiger
sql select DEPTNO, DNAME, LOC from DEPT;
if DBERR eq 0

	dept = fetch(0)
	print('DEPT table has '&count(dept)&' data',stdout)
else
	print(DBERRMSG,stdout)
endif
logout
endfunction


//sqlformat//
//sqlprint//
function run3()

connect scott/tiger
sql select DEPTNO, DNAME, LOC from DEPT;
if DBERR eq 0
	sqlformat(10,10,10)
	sqlprint(0,stdout)
else
	print(DBERRMSG,stdout)
endif
logout
endfunction


//plsql//
function run4()

connect scott/tiger
plsql dbms_stats.gather_schema_stats('scott');
if DBERR != 0
	print(DBERRMSG,stdout)
endif
logout
endfunction
    Top‚Φ
    connect()
    Description
    Connects a given username to the database
    A username and password are included in a CONNECT command
    You need to enter a slash (/) between a username and password
    If you include a connect identifier, such as the node name or database, you can enter them with a at mark sign (@) after the password.
    Syntax
    ret = connect(connect_string)
    Parameters
    connect_string:Connect character string
    Return Value
    1:Success
    0:Fail
    System Return Value
    ERROR
    0Success
    1Argument error
    100Database error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabese message
    DBUSER
    •ΆŽš—ρConnected username to database
    See Also
    logout()
    Requirements
    dboralib.slb
    Example
    require 'dboralib.slb'

run()
exit

function run()

	connect('scott/tiger')
	logout()

endfunction
    Top‚Φ
    fetch()
    Description
    Fetches a specified number of rows by SIZE from the result of SQL statement by the sql() function
    By calling fetch() sequentially, you can aquire from the rows after the rows previously fetched.
    If the result rows of the function fetch() are smaller than the SIZE, it returns the smaller result rows, instead of the SIZE
    If the SIZE is set to 0, it returns all data.
    Note that it may consume so much system resources if the result rows are too much data.
    The fetch() returns a result as an array
    If the DIRECTION is set to 0, or not specified, it returns one row of data specified in each element.
    One row of data also stores the data in associative array. It stores column data in each element of array.
    If the DIRECTION is set to 1, it returns the result in associative array. It also stores the specified field data in each element of the array.
    To access the data, it uses the field names as keys.
    Syntax
    ret = fetch(count,direction)
    Parameters
    count:Number of rows to be fetched
    direction:Method of storing the return value
    Return Value
    array:Fetched line
    System Return Value
    ERROR
    0Success
    1Argument error
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase message
    DBUSER
    •ΆŽš—ρConnected username to database
    Requirements
    dboralib.slb
    Example
    //[result example]
//
// DEPT table has 5 data
//

require 'dboralib.slb'

run()
exit

function run()

	local dept

	connect scott/tiger
	sql select DEPTNO, DNAME, LOC from DEPT;

	if DBERR eq 0
		dept = fetch(0)
		print('DEPT table has '&count(dept)&' data', stdout)
	else
		print(DBERRMSG, stdout)
	endif

	logout

endfunction
    Top‚Φ
    logout()
    Description
    Disconnects the current username from the database
    A session exists from the execution of the connect() until the logout() is executed
    You do not need to disconnect the current username from the database to reconnect to the database with the different username
    The current user is logged out of the database automatically when you quit the SQeeL
    The current transaction to the database is rolled back. (It does not commit pending changes to the database)
    Syntax
    ret = logout()
    Return Value
    1:Success
    0:Fail
    System Return Value
    ERROR
    0Success
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase message
    DBUSER
    •ΆŽš—ρConnected username to database
    See Also
    connect()
    Requirements
    dboralib.slb
    Example
    require 'dboralib.slb'

run()
exit

function run()

	connect('scott/tiger')
	logout()

endfunction
    Top‚Φ
    plsql()
    Description
    Executes procedures and packages
    If you want to get the value from the package, describe SQeeeL variable in the SQL statement directly and prefix the variable with ':'.
    Syntax
    ret = plsql(sql_statement)
    Parameters
    sql_statement:SQL statement
    Return Value
    1:Success
    0:Error
    System Return Value
    ERROR
    0Success
    1Argument error
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase message
    DBUSER
    •ΆŽš—ρConnected username to database
    Requirements
    dboralib.slb
    Example
    require 'dboralib.slb'

run()
exit

function run()

	connect scott/tiger
	plsql dbms_stats.gather_schema_stats('scott');

	if DBERR != 0
		print(DBERRMSG, stdout)
	endif

	logout

endfunction
    Top‚Φ
    rollback()
    Description
    Undo the any works done by the DML statements (SELECT, INSERT, UPDATE, or DELETE) in the current transaction after the previous commitment.
    You can roll back the current transaction when you execute the logout() or the exit statement
    Syntax
    ret = rollback()
    Return Value
    1:Success
    0:Fail
    System Return Value
    ERROR
    0Success
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase message
    DBUSER
    •ΆŽš—ρConnected username to database
    Requirements
    dboralib.slb
    Example
    require 'dboralib.slb'

function run()

	connect( 'scott/tiger' )

	sql insert into DEPT (DEPTNO, DNAME, LOC) values (50, 'LOGISTICS', 'LOS ANGELES');

	if DBERR != 0
		rollback
	else
		commit
	endif

	logout

endfunction


//fetch//
function run2()

local dept
connect scott/tiger
sql select DEPTNO, DNAME, LOC from DEPT;
if DBERR eq 0

	dept = fetch(0)
	print('DEPT table has '&count(dept)&' data',stdout)
else
	print(DBERRMSG,stdout)
endif
logout
endfunction


//sqlformat//
//sqlprint//
function run3()

connect scott/tiger
sql select DEPTNO, DNAME, LOC from DEPT;
if DBERR eq 0
	sqlformat(10,10,10)
	sqlprint(0,stdout)
else
	print(DBERRMSG,stdout)
endif
logout
endfunction


//plsql//
function run4()

connect scott/tiger
plsql dbms_stats.gather_schema_stats('scott');
if DBERR != 0
	print(DBERRMSG,stdout)
endif
logout
endfunction
    Top‚Φ
    sql()
    Description
    Executes SQL statements(DML and DDL)
    In the select statement, the result data have not been selected until the xfecth() or sqlprint() are executed
    Returns the number of rows processed in the execution of the DELETE, INSERT, and UPDATE statements
    Syntax
    ret = sql(sql_statement)
    Parameters
    sql_statement:Specify the SQL statement
    Return Value
    1 or more:Number of lines processed
    0:Fail
    System Return Value
    ERROR
    0Success
    1Argument error
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase message
    DBUSER
    •ΆŽš—ρConnected username to database
    Requirements
    dboralib.slb
    Example
    //[result example]
//
// DEPT table has 5 data
//

require 'dboralib.slb'

run()
exit

function run()

	local dept

	connect scott/tiger
	sql select DEPTNO, DNAME, LOC from DEPT;

	if DBERR eq 0
		dept = fetch(0)
		print('DEPT table has '&count(dept)&' data', stdout)
	else
		print(DBERRMSG, stdout)
	endif

	logout

endfunction
    Top‚Φ
    sqlformat()
    Description
    Sets the format of the data outputted to the file by the sqlprint()
    Sets the number of letter for each column sequentially
    Syntax
    ret = sqlformat(length,{ c })
    Parameters
    length:Number of strings per column
    Default (maximum column length of the specified table)
    { c }:
    Return Value
    1:Success
    System Return Value
    ERROR
    0Success
    Requirements
    dboralib.slb
    Example
    //[result example]
//
//    DEPTNO DNAME      LOC
//        10 ACCOUNTING NEW YORK
//        20 RESEARCH   DALLAS
//        30 SALES      CHICAGO
//        40 OPERATIONS BOSTON
//        50 LOGISTICS  LOS ANGELE
//

require 'dboralib.slb'

run()
exit

function run()

	connect scott/tiger
	sql select DEPTNO, DNAME, LOC from DEPT;

	if DBERR eq 0
		sqlformat(10, 10, 10)
		sqlprint(0, stdout)
	else
		print(DBERRMSG, stdout)
	endif

	logout

endfunction
    Top‚Φ
    sqlprint()
    Description
    Outputs the result data of the select statement of sql() to a specified file (FILEHANDLE)
    You can set the number of rows (CNT) to be outputted
    To output the result continously, this function is called again.
    Syntax
    ret = sqlprint(count,filehandle)
    Parameters
    count:Number of rows to be fetched from Oracle
    filehandle:File number to be printed
    Return Value
    1 or more:Number of lines processed
    0:Error or number of printed lines
    System Return Value
    ERROR
    0Success
    1Argument error
    100Oracle error
    DBERR
    •ΆŽš—ρDatabase error number
    DBERRMSG
    •ΆŽš—ρDatabase eeror message
    DBUSER
    •ΆŽš—ρConnected username to database
    Requirements
    dboralib.slb
    Example
    //[result example]
//
//    DEPTNO DNAME      LOC
//        10 ACCOUNTING NEW YORK
//        20 RESEARCH   DALLAS
//        30 SALES      CHICAGO
//        40 OPERATIONS BOSTON
//        50 LOGISTICS  LOS ANGELE
//

require 'dboralib.slb'

run()
exit

function run()

	connect scott/tiger
	sql select DEPTNO, DNAME, LOC from DEPT;

	if DBERR eq 0
		sqlformat(10, 10, 10)
		sqlprint(0, stdout)
	else
		print(DBERRMSG, stdout)
	endif

	logout

endfunction
    Top‚Φ
    © Insight Technology, Inc. 1996-2003 All Rights Reserved.