Database

Hi Albert,

You can use DBMS_LOB.substr like this for example:

DBMS_LOB.substr(column, 3000)

but don’t forget that substring of a CLOB column has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size.

For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000

and you can refer to Oracle Substr Function Article for more information about the substr function

hello aya,

as you know the procedure you can’t use inside query so if you need to create procedure to return multiple row or values you can use :

create or replace procedure myprocedure(retval in out sys_refcursor) is
begin
open retval for
select employee_id,last_name from employees;
end myprocedure;

and here you can call procedure inside anonymous pl/sql block :

[code]

DECLARE
myrefcur SYS_REFCURSOR;
employee_id employees.employee_id%TYPE;
last_name employees.last_name%TYPE;
BEGIN
myprocedure (myrefcur);

LOOP
FETCH myrefcur INTO employee_id,last_name;

EXIT WHEN myrefcur%NOTFOUND;
DBMS_OUTPUT.put_line (‘Employee Id: ‘ || employee_id || ‘ Last Name: ‘|| last_name);
END LOOP;

CLOSE myrefcur;
END;
[/code]

hope this help.

SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff.

SYS is a schema that includes tons of tables, views, grants, etc etc etc.

SYSTEM is a schema…..

Technically — A schema is the set of metadata (data dictionary) used by the database, typically generated using DDL. A schema defines attributes of the database, such as tables, columns, and properties. A database schema is a description of the data in a database.

utl_file.fopen takes the name of a directory, not the path. For example:

(you may need to login as SYS to execute these)

[code]Windows:
CREATE DIRECTORY CUST_DIR AS ‘C:’;

Linux
CREATE DIRECTORY CUST_DIR AS ‘/usr/tmp’;

GRANT READ ON DIRECTORY CUST_DIR TO SCOTT;[/code]

Then, you can refer to it in the call to fopen:

[code]
UTL_FILE.FOPEN(‘CUST_DIR’, ‘oraask_test.txt’, ‘W’);
[/code]

hope this help.