Tutorial by Examples

Let's say a user wants to select data from different tables. A table is specified by the user. function get_value(p_table_name varchar2, p_id number) return varchar2 is value varchar2(100); begin execute immediate 'select column_value from ' || p_table_name || ...

Example below inserts value into the table from the previous example: declare query_text varchar2(1000) := 'insert into my_table(id, column_value) values (:P_ID, :P_VAL)'; id number := 2; value varchar2(100) := 'Bonjour!'; begin execute immediate query_text using id, value; end; / ...

Let's update table from the first example: declare query_text varchar2(1000) := 'update my_table set column_value = :P_VAL where id = :P_ID'; id number := 2; value varchar2(100) := 'Bonjour le monde!'; begin execute immediate query_text using value, id; end; /

This code creates the table: begin execute immediate 'create table my_table (id number, column_value varchar2(100))'; end; /

You can execute anonymous block. This example shows also how to return value from dynamic SQL: declare query_text varchar2(1000) := 'begin :P_OUT := cos(:P_IN); end;'; in_value number := 0; out_value number; begin execute immediate query_text using out out_value, in in_value; dbms_o...