Skip to main content

Posts

Showing posts from August, 2012

The the knowledge I learned when worked on the oracle data migration - Part 1

#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);
resetSeqStatement varchar2(2000);
begin
    deleteStatement := 'DELETE FROM ' || tablename;

     execute IMMEDIATE deleteStatement;
    declare

    begin
      RESET_SEQ(sequenceName);
 …

Oracle PL/SQL Stored Procedure from scratch

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




Oracle/PLSQL: Dealing with apostrophes/single quotes in strings

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 to enter 2 single quotes f…

Oracle : create Generic Store Procedure for data migration

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';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name |…