Overview
The usage of dynamic SQL statement in ORACLE is almost same with that of ALTIBASE HDB.
Example
Oracle | Altibase | Comments |
|---|
CREATE OR REPLACE PROCEDURE raise_emp_salary
AS
sql_stmt VARCHAR2(200);
v_column VARCHAR2(30) := 'dno';
dno NUMBER(4) := 46;
dname VARCHAR2(30) := 'Special Projects';
mgr_no NUMBER(6) := 200;
dep_location NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)';
EXECUTE IMMEDIATE sql_stmt USING dno, dname, mgr_no, dep_location;
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
USING dno;
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/ | CREATE OR REPLACE PROCEDURE raise_emp_salary
AS
sql_stmt VARCHAR2(200);
v_column VARCHAR2(30) := 'dno';
dno NUMBER(4) := 46;
dname VARCHAR2(30) := 'Special Projects';
mgr_no NUMBER(6) := 200;
dep_location NUMBER(4) := 1700;
BEGIN
-- note that there is no semi-colon (;) inside the quotes '...'
EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO departments VALUES (dno, dname, mgr_no, dep_location)';
EXECUTE IMMEDIATE sql_stmt USING dno, dname, mgr_no, dep_location;
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num'
USING dno;
EXECUTE IMMEDIATE 'ALTER SESSION SET EXPLAIN PLAN = ON';
EXECUTE IMMEDIATE 'DROP TABLE bonus';
END;
/ | |