Recently I have started preparing datamarts for regular reporting process, and tried to make some use of procedures with parameters.
So I read this guideline and tried to replicate but confused with error in compiling.
Here is my code:
create or replace procedure mig_rate @rep_date date AS
create or replace table mig_temp1(
report_date date,
portfolio string,
bucket integer,
Q integer);
begin
for j in 1..7 loop
for t in 0..32 loop
-- execute immediate 'bla-bla-bla insert statement mig_temp1
end loop;
end loop;
end
This results in PLS-00103
saying that @
symbol was unexpected.
What's wrong with the syntax, how should I put the parameters into the procedure??
The main thing wrong is that you're trying to use SQL Server syntax in an Oracle database.
Firstly, Oracle object names have to start with a letter (unless you use quoted identifiers, but please don't) and... well, here are the relevant parts:
Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.
Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.
Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).
... so you can't use @
at all in the procedure name, and don't need to as that's a SQL Server thing (I think, I don't use that!).
Edit: see, I'm so unfamiliar with SQL Server I misread what it was doing. Your procedure name is OK, but that's not how you declare parameters; it should start:
create or replace procedure mig_rate(rep_date date) as
...
Secondly, you can't create (declare?) a table like that in an Oracle procedure. It looks like you want a temporary table for the duration of the procedure, but that's not how Oracle works; you can create a global temporary table outside the procedure but it's a permanent database object - only the data is temporary.
You can use a PL/SQL collection instead but it depends what you want to do with it. If it's a staging area before inserting into a normal table then you might be able to do it as a single SQL command, which would perform better.
I'm not sure why you'd want to use dynamic SQL (execute immediate
) here...
Please refer to the PL/SQL language reference and Oracle-specific sites rather than SQL Server-specific sites to learn what to do; there are very significant differences in how they work.