Say you want a weekly sales report grouped by each day and you want to show days with zero sales. Usually this is accomplished by creating and joining to a date table that has one row for each day. If you're going to be doing this report all the time or need additional groupings like quarter or fiscal year, than it makes sense to create this table. But sometimes I need to do this for one report in a database that doesn't already have this table. For oracle, Uli Bethke describes what I think is the best way to date. In short, it's clever use of oracle's 'connect by' clause to recursively join dual to itself. For example, the following would generate all days in march:
trunc(to_date('1-mar-2009','dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date
select (level-1) n
connect by level-1 <= trunc(to_date('31-mar-2009','dd-mon-yyyy')) - trunc(to_date('1-mar-2009','dd-mon-yyyy'))
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;
select sysdate into :myDate from dual;
Now I finally understood what the data provider was doing and consequently I understood SQL Plus a lot better too.