Intro to Oracle for SQLServer Developers and DBAs
1. Oracle has something called V$ tables, which are essential similar to the Dynamic Management View (DMV) of SQLServer, Here are two examples:
select * from v$database; select * from v$thread;
Result – this shows that my 11g Express Edition has both an instance name of “xe” and a database name of “XE”. See “Intro to Oracle SQL for SQLServer Developers and DBAs – Part 1” for an explanation of instances vs databases.
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME ---------- --------- --------- ----------------- -------------- 2749400775 XE 06-JAN-15 370965 06-JAN-15 THREAD# STATUS ENABLED GROUPS INSTANCE ---------- ------ -------- ---------- -------------------------------------------------------------------------------- 1 OPEN PUBLIC 2 xe
2. Oracle has an odd “fake” table called “dual”.
Oracle always requires a table name, so this is the dummy “catch-all” table” The links below will you the full scoop and history of why it was called “dual”.
select 5+5 from dual; select sysdate into myDate from dual;
Additional explanation of the “Dual” table;
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388
http://stackoverflow.com/questions/73751/what-is-the-dual-table-in-oracle
3. The following example will illustrate many things I put together, from dozens of examples found in many different sources.
Sample Code
set serveroutput on
select 5+5 from dual;
select sysdate from dual;
declare
myDate date;
myNumber number;
myString varchar2(50) := 'Initial Value';
begin
select sysdate into myDate from dual;
myNumber := 10;
myNumber := myNumber + 8;
myString := 'New Value';
-- use this function/method to print
DBMS_OUTPUT.put_line ('The date is ' || myDate);
DBMS_OUTPUT.put_line ('myNumber is ' || myNumber);
DBMS_OUTPUT.put_line ('myString is ' || myString);
end;
SQL Output
5+5 ---------- 10 SYSDATE --------- 09-JAN-15 anonymous block completed The date is 09-JAN-15 myNumber is 18 myString is New Value
Dbms Output Window:
The date is 09-JAN-15 myNumber is 18 myString is New Value
1. “set serveroutput on” will bring the needed to use the DBMS_OUPUT_put_line into the SQL result window. Otherwise, it will only show in the “Dbms Output” window. “:DBMS_Output.put_line()” is basically the SQLServer Print command. Use || to concatenate, and to actually see the results of these ‘print’ statement, you may have to manually “view” and then “enable” the DBMS-Output window in the SQL “GUI” tool that you are using.
2. “from dual” see prior point above.
3. declare/begin/end – is the structure – variables have local scope to the block in which they are declared, and these blocks can be nested.
4. Semicolons to end the lines are very important! This small sample gives you an idea of where they go, and where they don’t go.
5. “Number” is a data type You could use “PLS_INTEGER” is you want an integer. VarChar2(nn) is the standard string type.
6. := is the assignment operator, to set the value of something equal to another value
7. Comments work the same between PL/SQL and Transact-SQL (T-SQL)