I had a lightbulb moment yesterday. My new employer is mostly an oracle shop so I end up spending a lot of time translating what I know about sql server into oracle terms. But one thing I've struggled with is SQL Plus vs. PL/SQL. That is until yesterday. Maybe I never took the time before but a sequence of searches forced me in the right direction.
I'm using very few stored proceedures lately (reasons for which will come in a different post). Consequently I was looking for ways to execute a batch of statements .net code with the Oracle Data Provider and found my answer here on OTN. But then i looked at how he was getting variable data out of the command. He was basically doing what I've always wanted to do with Oracle's SQL Developer. When using the Sql Server query tools (query analyzer, SSMS), I'd often write myself maintenance procedures that I just kept in a file. It's no problem to create variables and run sql statements in the exact same way you would from inside of a stored procedure. Sometimes I could copy nearly the same code into a SqlCommand and it would run just fine. Oracle isn't like that. Variables (pl/sql variables at least) must be declared within the 'declare' section of a begin/end block. These blocks can be 'anonymous' (not in a stored procedure) but once you enter the block things change. You can't simply do a 'select column from table' for example or you get an 'INTO clause expected' error. Oracle assumes that you're now in a programming block and not connected to some user interface so it wants you to store the data in some variable. But how was this .net code getting this v_maxnum variable out? On some sort of protocol level there must be a way for a human to do the exact same thing.
Finally thanks to Wikipedia, I got it. SQL Plus is very similar to the combination of cmd.exe and SqlCmd/isql? The big difference is that line between sqlcmd and t-sql is blurred a little more blurred than it is with SQL Plus and pl/sql. But once I dug in to sqlcmd a little I realized that other than begin/end blocks it's really not that different. I guess I really never used sqlcmd for anything but a single statement from the command line.
The big realization came now when I understood the variable types in SQL Plus. It has both substitution variables (&var and &&var) and regular variables (var myvar varchar2). But unlike sqlcmd's variables being t-sql variables, SQL Plus's variables are different than PL/SQL variables. However, PL/SQL can 'bind' SQL Plus variables. Take the following example.
var myDate varchar2;
begin
select sysdate into :myDate from dual;
end;
/
print myDate
Now I finally understood what the data provider was doing and consequently I understood SQL Plus a lot better too.