DBMS SQL
DBMS_SQL#
The DBMS_SQL provides procedures and functions which utilize dynamic SQL as shown in the table below.
| Procedures/Functions | Description |
|---|---|
| OPEN_CURSOR | Opens a cursor. The maximum number of cursors, which can be open, is be specified in the PSM_CURSOR_OPEN_LIMIT property. (Default Value: 32) |
| IS_OPEN | Checks on the status of cursor to see if it is open or not in order to return the results. |
| PARSE | Execute parsing SQL statements. |
| BIND_VARIABLE | Binds variables which are included in the SQL statement. |
| EXECUTE_CURSOR | Executes the cursor. |
| DEFINE_COLUMN | Defines the columns which will be fetched in the cursor. |
| FETCH_ROWS | Imports a row which is supposed to fetch. It is only used in the SELECT statement. |
| COLUMN_VALUE | Imports the value of a column which is a variable of the cursor. It is only used in the SELECT statement. |
| CLOSE_CURSOR | Closes the cursor. |
| LAST_ERROR_POSITION | Returns the location of the error that occurred when parsing. |
Related Properties#
DBMS_SQL package related properties can be set in altibase.properties.
- PSM_CURSOR_OPEN_LIMIT
For more detailed information, please refer to the General Reference.
BIND_VARIABLE#
The BIND_VARIABLE procedure execute binding of variables which are included in the SQL statement.
Syntax#
DBMS_SQL.BIND_VARIABLE(c, name, value);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| c | IN | INTEGER | The cursor number |
| name | IN | VARCHAR2(128) | The variable name starting with a colon (;). |
| value | IN | VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE |
The language option(It is not supported so that it can be neglected regardless of any value). |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( :b1 )', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
end;
/
Create success.
iSQL> exec proc1;
0
Execute success.
CLOSE_CURSOR#
The CLOSE_CURSOR procedure closes a cursor. If the cursor cannot be closed, it is closed when the session is terminated.
Syntax#
DBMS_SQL.CLOSE_CURSOR(c);
Parameter#
| Name | In/Output | Data Type | Descritption |
|---|---|---|---|
| c | IN | INTEGER | Cursor number |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.close_cursor( c );
end;
/
Create success.
iSQL> exec proc1;
0
Execute success.
COLUMN_VALUE#
The COLUMN_VALUE procedure imports the value of a column which is the binding variables of cursor.
Syntax#
DBMS_SQL.COLUMN_VALUE(c, position, column_value);
Parameters#
| Name | In/Output | Data Type | Descritpion |
|---|---|---|---|
| c | IN | INTEGER | The cursor number |
| position | IN | INTEGER | The relational position when fetching a column. It starts with 1. |
| column_value | OUT | VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE |
Store the value of a column |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
loop
exit when dbms_sql.fetch_rows( c ) = 0;
dbms_sql.column_value(c, 1, c1);
println( 'fetch -> ' || c1 );
end loop;
end;
/
Create success.
iSQL> exec proc1;
0
fetch -> 999
Execute success.
DEFINE_COLUMN#
The DEFINE_COLUMN procedure defines the type of column which will be fetched. It is only used in the SELECT statement.
Syntax#
DBMS_SQL.DEFINE_COLUMN(c, position, column_value);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| c | IN | INTEGER | Cursor number |
| position | IN | INTEGER | The location of a column. It starts with 1. |
| column_value | IN | VARCHAR2(32000), CHAR(32000), INTEGER, BIGINT, SMALLINT, DOUBLE, REAL, NUMERIC(38), DATE |
The definition of column type |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
end;
/
Create success.
iSQL> exec proc1;
0
Execute success.
EXECUTE_CURSOR#
The EXECUTE_CURSOR function implements a cursor.
Syntax#
BIGINT variable:=DBMS_SQL.EXECUTE_CURSOR(c);
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| c | IN | INTEGER | The cursor number |
Result Value#
This function returns the number of records by executing a cursor.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( :b1 )', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
println( rc );
end;
/
Create success.
iSQL> exec proc1;
0
1
Execute success.
FETCH_ROWS#
The FETCH_ROWS imports the row which will be fetched in a cursor. It is only used in the SELECT statement.
Syntax#
INTEGER variable:=DBMS_SQL.FETCH_ROWS(c);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| c | IN | INTEGER | The cursor number |
Result Value#
0 is returned if there is no row to fetch; otherwise, it returns 1.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
b1 integer;
c1 integer;
rc bigint;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'select i1 from t1 where i1 = :b1', dbms_sql.native );
b1 := 999;
dbms_sql.bind_variable( c, ':b1', b1 );
rc := dbms_sql.execute_cursor( c );
dbms_sql.define_column( c, 1, c1 );
rc := dbms_sql.fetch_rows( c );
println( rc );
end;
/
Create success.
iSQL> exec proc1;
0
1
Execute success.
IS_OPEN#
The IS_OPEN is a function which returns the result whether the cursor is open or not.
Syntax#
BOOLEAN variable:=DBMS_SQL.IS_OPEN(c);
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| c | IN | INTEGER | Cursor number |
Result Value#
True is returned when the cursor is open, and FALSE is returned when the cursor is not open.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
if dbms_sql.is_open( c ) = TRUE
then
println( 'cursor opened' );
else
println( 'invalid cursor' );
end if;
end;
/
Create success.
iSQL> exec proc1;
0
cursor opened
Execute success.
LAST_ERROR_POSITION#
The LAST_ERROR_POSITION returns the location of error that occurred when parsing
This function should be used immediately after calling the PARSE procedure to get the correct result
Syntax#
DBMS_SQL.LAST_ERROR_POSITION;
Result Value#
Returns the error locaiton.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1( a varchar(128) )
as
c integer;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse( c, a, dbms_sql.native );
exception
when others
then
println( dbms_sql.last_error_position );
dbms_sql.close_cursor( c );
end;
/
Create success.
iSQL> exec proc1( 'select empno, ^a from emp' );
14
Execute success.
OPEN_CURSOR#
The OPEN_CURSOR opens the cursor.
Syntax#
INTEGER variable:=DBMS_SQL.OPEN_CURSOR;
Result Value#
If is successfully executed, the number of cursor is returned.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
end;
/
Create success.
iSQL> exec proc1;
0
Execute success
PARSE#
The PARSE procedure parses SQL statements.
Syntax#
DBMS_SQL.PARSE(c, sql, language_flag);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| c | IN | INTEGER | The cursor number |
| sql | IN | VARCHAR2(32000) | SQL which will be parsed |
| language_flag | IN | INTEGER | The language option(it is not supported so that it is neglected regardless of specifying any values). |
Result Value#
Since it is a stored procedure, there is no result value.
Exception#
There is no exception.
Example#
iSQL> create or replace procedure proc1
as
c integer;
begin
c := dbms_sql.open_cursor;
println( c );
dbms_sql.parse( c, 'insert into t1 values ( 1 )', dbms_sql.native );
end;
/
Create success.
iSQL> exec proc1;
0
Execute success.