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
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>
Assign a character string that is too long
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>
Change varchar type value
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.
Check data size for varchar2 column
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.
Combine text string value in select clause
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>
Compare varchar2 value with =
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>
Comparison of CHAR with VARCHAR2 (Test the strings for equality)
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>
declare varchar2 variables
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>
Init a varchar2 type variable
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> --
Insert value to char type and varchar2 type
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>
IN with text value
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.
Read clob data to varchar2 type variable
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.
Use case with switch varchar2 variable
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.
Use select into clause to assign value to a varchar2 type variable and output with dbms_output.put_line
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.
Use varchar2 as table column type
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>
Use % with like in a select statement for varchar2 column
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>
Varchar2 type column with different byte length
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>
varchar2 type value with not null default value
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>
varchar type are case sensitive
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.
Varchar type variable is defined but has no value
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>