Oracle PL/SQL/Data Type/VARCHAR2
Содержание
- 1 Are two text value equal
- 2 Assign a character string that is too long
- 3 Change varchar type value
- 4 Check data size for varchar2 column
- 5 Combine text string value in select clause
- 6 Compare varchar2 value with =
- 7 Comparison of CHAR with VARCHAR2 (Test the strings for equality)
- 8 declare varchar2 variables
- 9 Init a varchar2 type variable
- 10 Insert value to char type and varchar2 type
- 11 IN with text value
- 12 Read clob data to varchar2 type variable
- 13 Use case with switch varchar2 variable
- 14 Use select into clause to assign value to a varchar2 type variable and output with dbms_output.put_line
- 15 Use varchar2 as table column type
- 16 Use % with like in a select statement for varchar2 column
- 17 Varchar2 type column with different byte length
- 18 varchar2 type value with not null default value
- 19 varchar type are case sensitive
- 20 Varchar type variable is defined but has no value
Are two text value equal
<source lang="sql">
SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE);
Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Wodget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SQL> SELECT * FROM product WHERE product_name = "Small Widget"; PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------- ---------------- ---------
Small Widget 99 1 15-JAN-03 1 row selected. SQL> SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL> SQL>
</source>
Assign a character string that is too long
<source lang="sql">
SQL> SQL> DECLARE
2 v_TempVar VARCHAR2(5); 3 BEGIN 4 v_TempVar := "abcdefghijkl"; 5 END; 6 /
DECLARE
ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 4
SQL> SQL>
</source>
Change varchar type value
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 quantity NUMBER(4,2), 4 purchase_date DATE, 5 salesperson VARCHAR2(3) 6 );
Table created. SQL> SQL> INSERT INTO purchase VALUES ("A", 1, "14-JUL-03", "CA"); 1 row created. SQL> INSERT INTO purchase VALUES ("B", 75, "14-JUL-03", "BB"); 1 row created. SQL> INSERT INTO purchase VALUES ("C", 2, "14-JUL-03", "GA"); 1 row created. SQL> INSERT INTO purchase VALUES ("D", 8, "15-JUL-03", "GA"); 1 row created. SQL> INSERT INTO purchase VALUES ("A", 20, "15-JUL-03", "LB"); 1 row created. SQL> INSERT INTO purchase VALUES ("B", 2, "16-JUL-03", "CA"); 1 row created. SQL> INSERT INTO purchase VALUES ("C", 25, "16-JUL-03", "LB"); 1 row created. SQL> INSERT INTO purchase VALUES ("D", 2, "17-JUL-03", "BB"); 1 row created. SQL> SQL> SELECT * FROM purchase; PRODUCT_NAME QUANTITY PURCHASE_ SAL
---------- --------- ---
A 1 14-JUL-03 CA B 75 14-JUL-03 BB C 2 14-JUL-03 GA D 8 15-JUL-03 GA A 20 15-JUL-03 LB B 2 16-JUL-03 CA C 25 16-JUL-03 LB D 2 17-JUL-03 BB 8 rows selected. SQL> SQL> UPDATE purchase
2 SET product_name = "AA" 3 WHERE product_name = "A";
2 rows updated. SQL> SQL> SQL> DROP TABLE purchase; Table dropped.
</source>
Check data size for varchar2 column
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select ename, vsize(ename), sal, vsize(sal) from emp; ENAME VSIZE(ENAME) SAL VSIZE(SAL)
------------ ---------- ----------
SMITH 5 800 2 ALLEN 5 1600 2 WARD 4 1250 3 JONES 5 2975 3 MARTIN 6 1250 3 BLAKE 5 2850 3 CLARK 5 2450 3 SCOTT 5 3000 2 KING 4 5000 2 TURNER 6 1500 2 ADAMS 5 1100 2 ENAME VSIZE(ENAME) SAL VSIZE(SAL)
------------ ---------- ----------
JAMES 5 950 3 FORD 4 3000 2 MILLER 6 1300 2 14 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Combine text string value in select clause
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 sales_tax NUMBER(4,2), 5 purchase_date DATE, 6 salesperson VARCHAR2(3));
Table created. SQL> SQL> INSERT INTO purchase VALUES ("Product Name 1", 1, .08, "5-NOV-00", "AB"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 2", 2.5, .21, "29-JUN-01", "CD"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 3", 50.75, 4.19, "10-DEC-02", "EF"); 1 row created. SQL> INSERT INTO purchase VALUES ("Product Name 4", 99.99, 8.25, "31-AUG-03", "GH"); 1 row created. SQL> SQL> SQL> SQL> SELECT product_name || " was sold by " || salesperson SOLDBY FROM purchase; SOLDBY
Product Name 1 was sold by AB Product Name 2 was sold by CD Product Name 3 was sold by EF Product Name 4 was sold by GH 4 rows selected. SQL> SQL> SQL> DROP TABLE purchase; Table dropped. SQL> SQL> SQL>
</source>
Compare varchar2 value with =
<source lang="sql">
SQL> CREATE TABLE products
2 ( 3 id NUMBER(6), 4 name VARCHAR2(50), 5 descr VARCHAR2(4000), 6 category VARCHAR2(50), 7 cat_desc VARCHAR2(2000), 8 weight_class NUMBER(2), 9 unit_of_measure VARCHAR2(20), 10 min_price NUMBER(8,2) 11 );
Table created. SQL> SQL> SQL> SQL> select id, category, weight_class
2 from products 3 where category = "Hardware" 4 or weight_class = 4;
no rows selected SQL> SQL> drop table products; Table dropped. SQL> SQL>
</source>
Comparison of CHAR with VARCHAR2 (Test the strings for equality)
<source lang="sql">
SQL> -- Comparison of CHAR with VARCHAR2. SQL> SQL> SET SERVEROUTPUT ON SQL> SET ECHO ON SQL> SQL> DECLARE
2 employee_name_c CHAR(32);
3 employee_name_v VARCHAR2(32);
4 BEGIN
5 -- Assign the same value to each string.
6 employee_name_c := "String";
7 employee_name_v := "String";
8
9 -- Test the strings for equality.
10 IF employee_name_c = employee_name_v THEN
11 DBMS_OUTPUT.PUT_LINE("The names are the same");
12 ELSE
13 DBMS_OUTPUT.PUT_LINE("The names are NOT the same");
14 END IF;
15 END;
16 /
The names are NOT the same PL/SQL procedure successfully completed. SQL>
</source>
declare varchar2 variables
<source lang="sql">
SQL> SQL> SQL> declare
2 l_value1 varchar2(100); 3 l_value2 varchar2(100) := ""; 4 l_value3 varchar2(100) := null; 5 l_value4 varchar2(100) default null; 6 begin 7 null; 8 end; 9 /
PL/SQL procedure successfully completed. SQL>
</source>
Init a varchar2 type variable
<source lang="sql">
SQL> SQL> set echo on SQL> SQL> DECLARE
2 v_MyChar VARCHAR2(20) := "test";
3 v_NUMBER NUMBER;
4 V_Date DATE := SYSDATE;
5 v_counter INTEGER;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE("This is a Test");
8 DBMS_OUTPUT.PUT_LINE("Of Syntax Error Debugging");
9 For v_COUNTER IN 1..5 LOOP
10 DBMS_OUTPUT.PUT_LINE("You are in loop:" || v_counter);
11 END LOOP;
12 END;
13 /
This is a Test Of Syntax Error Debugging You are in loop:1 You are in loop:2 You are in loop:3 You are in loop:4 You are in loop:5 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> --
</source>
Insert value to char type and varchar2 type
<source lang="sql">
SQL> create table myTable(
2 codeValue char(2) not null, 3 address varchar2(30) 4 );
Table created. SQL> SQL> desc myTable;
Name Null? Type ------------------------- -------- ------------------ CODEVALUE NOT NULL CHAR(2) ADDRESS VARCHAR2(30)
SQL> SQL> insert into myTable values ("AZ","Arizona"); 1 row created. SQL> insert into myTable values ("AZ","Arizona"); 1 row created. SQL> SQL> SQL> select * from myTable;
CO ADDRESS -- ------------------------------ AZ Arizona AZ Arizona 2 rows selected. SQL> SQL> select address from myTable;
ADDRESS
Arizona Arizona 2 rows selected. SQL> SQL> SQL> drop table myTable; Table dropped. SQL>
</source>
IN with text value
<source lang="sql">
SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE);
Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Wodget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product 1", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Product 2", 25, 10000, null); 1 row created. SQL> SQL> SELECT * FROM product WHERE product_name IN ("Small Widget", "Round Church Station"); PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------- ---------------- ---------
Small Widget 99 1 15-JAN-03 1 row selected. SQL> SQL> SQL> DROP TABLE product; Table dropped.
</source>
Read clob data to varchar2 type variable
<source lang="sql">
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB NOT NULL 4 );
Table created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE initClob(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN 3 SELECT clobData INTO clob_par FROM myTable WHERE id = id_par; 4 END initClob; 5 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE readClob(id_par IN INTEGER) IS
2 clobVariable CLOB;
3 charVariable VARCHAR2(50);
4 offsetPos INTEGER := 1;
5 amount_var INTEGER := 50;
6 BEGIN
7 initClob(clobVariable, id_par);
8 DBMS_LOB.READ(clobVariable, amount_var, offsetPos, charVariable);
9 DBMS_OUTPUT.PUT_LINE("charVariable = " || charVariable);
10 DBMS_OUTPUT.PUT_LINE("amount_var = " || amount_var);
11 END readClob;
12 /
Procedure created. SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE write_example(id_par IN INTEGER) IS
2 clobVariable CLOB; 3 charVariable VARCHAR2(10) := "pretty"; 4 offsetPos INTEGER := 7; 5 amount_var INTEGER := 6; 6 BEGIN 7 SELECT clobData INTO clobVariable FROM myTable WHERE id = id_par FOR UPDATE; 8 9 readClob(1); 10 DBMS_LOB.WRITE(clobVariable, amount_var, offsetPos, charVariable); 11 readClob(1); 12 13 END write_example; 14 /
Procedure created. SQL> SQL> SQL> drop table myTable; Table dropped.
</source>
Use case with switch varchar2 variable
<source lang="sql">
SQL> declare
2 val varchar2(100); 3 city varchar2(20) := "TORONTO"; 4 begin 5 val := CASE city 6 WHEN "TORONTO" then "RAPTORS" 7 WHEN "LOS ANGELES" then "LAKERS" 8 WHEN "BOSTON" then "CELTICS" 9 WHEN "CHICAGO" then "BULLS" 10 ELSE "NO TEAM" 11 END; 12 13 dbms_output.put_line(val); 14 end; 15 /
RAPTORS PL/SQL procedure successfully completed.
</source>
Use select into clause to assign value to a varchar2 type variable and output with dbms_output.put_line
<source lang="sql">
SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMM
DD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date , Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMM
DD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> SQL> SQL> declare
2 empName varchar2(80); 3 begin 4 select fname into empName from emp where rownum = 1; 5 dbms_output.put_line(empName); 6 end; 7 /
Jason PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Use varchar2 as table column type
<source lang="sql">
SQL> SQL> create table MyTable (
2 event_name varchar2(100), 3 event_date date);
Table created. SQL> SQL> insert into MyTable ( event_name, event_date ) values ( "Oracle", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) ); 1 row created. SQL> SQL> insert into MyTable ( event_name, event_date ) values ( "Sample code", SYSDATE ); 1 row created. SQL> SQL> column event_name format a40 SQL> SQL> select * from MyTable; EVENT_NAME EVENT_DATE
------------------
Oracle 02-DEC-01 Sample code 10-JUN-08 SQL> insert into MyTable (event_name, event_date) values ("World", TO_DATE( "2-DEC-2001", "DD-MON-YYYY" ) ); 1 row created. SQL> SQL> insert into MyTable ( event_name, event_date ) values ( "code", SYSDATE ); 1 row created. SQL> SQL> show parameters nls_date_format NAME TYPE VALUE
----------- ------------------------------
nls_date_format string SQL> SQL> insert into MyTable ( event_name, event_date ) values ( "9i", DATE "2001-10-11" ); 1 row created. SQL> SQL> select * from MyTable; EVENT_NAME EVENT_DATE
------------------
Oracle 02-DEC-01 Sample code 10-JUN-08 World 02-DEC-01 code 10-JUN-08 9i 11-OCT-01 SQL> SQL> drop table MyTable; Table dropped. SQL>
</source>
Use % with like in a select statement for varchar2 column
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select empno, ename from emp where ename like "A%";
EMPNO Employee Name
-------------
7499 ALLEN
7876 ADAMS
SQL> SQL> drop table emp; Table dropped. SQL>
</source>
Varchar2 type column with different byte length
<source lang="sql">
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> SQL> create or replace view emp_hq
2 as select * from emp 3 where id = "2";
View created. SQL> SQL> SQL> drop table emp; Table dropped. SQL>
</source>
varchar2 type value with not null default value
<source lang="sql">
SQL> SQL> DECLARE
2 lv_test_txt VARCHAR2(5) NOT NULL DEFAULT "HELLO";
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Test Value: " || lv_test_txt);
5 END;
6 /
Test Value: HELLO PL/SQL procedure successfully completed. SQL> SQL>
</source>
varchar type are case sensitive
<source lang="sql">
SQL> SQL> CREATE TABLE product (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 quantity_on_hand NUMBER(5,0), 5 last_stock_date DATE);
Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03"); 1 row created. SQL> SQL> SQL> SQL> UPDATE product
2 SET product_name = "Product Number" 3 WHERE product_name = "product Number";
0 rows updated. SQL> SQL> SQL> DROP TABLE product; Table dropped.
</source>
Varchar type variable is defined but has no value
<source lang="sql">
SQL> SQL> SQL> declare
2 str varchar2(50); 3 begin 4 Null; 5 exception 6 when others then 7 raise_application_error (-20100, "error#" || sqlcode || " desc: " || sqlerrm); 8 end; 9 /
PL/SQL procedure successfully completed. SQL>
</source>
