I love programming

#1 Stored procedure to rest the sequence create or replace procedure reset_seq( p_seq_name in varchar2 ) is     l_val number; begin      execute immediate     'select ' || p_seq_name || '.nextval from dual' INTO l_val;     execute immediate     'alter sequence ' || p_seq_name || ' increment by -' || l_val ||                                                           ' minvalue 0';     execute immediate     'select ' || p_seq_name || '.nextval from dual' INTO l_val;     execute immediate     'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0'; end; / #2 How to reset table and the sequence create or replace procedure reset_table( tablename in varchar2 , sequenceName in varchar2) is deleteStatement varchar2(2000); re...

"The Best Programming Advice I Ever Got" with Rob Pike | | InformIT : 'via Blog this'

1. The simple example -- this will make sure the result output to the Script Output(or the console) SET SERVEROUTPUT ON -- below command will clear the screen before you output new content clear screen Declare newid varchar2(100); begin select 123 into newid from dual; dbms_output.put_line('new id is:' || newid); end; / 2. get result after insertion INSERT INTO mytable (NAME)  VALUES(1,'xx','data') return id into newid;  -- id is one of the mytable's column

Tiers are the separation of physical units. Usually, different tier locates in different server. Layers are the separation of logical units.

Question:  How can I handle apostrophes and single quotes in strings?  As you know, single quotes start and terminate strings in SQL. Answer:  Now it is first important to remember that in Oracle, you enclose strings in single quotes. The first quote denotes the beginning of the string and the second quote denotes the termination of the string. If you need to deal with apostrophes/single quotes in strings, your solution depends on where the quote is located in the string. We'll take a look at 4 scenarios where you might want to place an apostrophe or single quote in a string. Apostrophe/single quote at start of string When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example: SELECT '''Hi There' FROM dual; would return 'Hi There Apostrophe/single quote in the middle of a string When the apostrophe/single quote is in the middle of the string, you need t...

I am working on data migration from the legacy db to the new db. For those lookup tables, the sequence need to start from 1 each time do the migration testing. And in addition, the existing table should be wiped out before import data from old db. So, I created to helper store procedure to implement it. -- function or procedure for the easy for testing of data migration -- this will be used to reset the sequence(back to 1) for each lookup table create or replace procedure reset_seq( p_seq_name in varchar2 ) is     l_val number; begin     execute immediate     'select ' || p_seq_name || '.nextval from dual' INTO l_val;     execute immediate     'alter sequence ' || p_seq_name || ' increment by -' || l_val ||                                                           ' minvalue 0'; ...