[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

exec

To have a stored procedure to create a sequence do I just have:

CREATE OR REPLACE PROCEDURE procedure_name IS
BEGIN
create sequence ...
END;

Is this the same as exec ...
0
msimons4
Asked:
msimons4
  • 6
  • 5
  • 4
  • +1
10 Solutions
 
slightwv (䄆 Netminder) Commented:
No.

An anonymous pl/sql block from sqlplus plus like:
begin
--some  plsql command;
end;


is the same as
exec --some plsql command;

inside a procedure since you have 'create or replace procedure', it is not.

also,
you cannot do DDL like that.  If you must do it (which is in very rare circumstances), you need execute immediate:

begin
execute immediate ' create sequence ...';
end;
/
0
 
Mark GeerlingsDatabase AdministratorCommented:
The only SQL verbs that are directly supported in PL\SQL are: select, insert, update and delete.  Any other SQL verbs that you may want to use (like: create, drop, alter, etc.) can only be supported in PL\SQL inside an "execute immediate" command or in the more-complex procedures of the DBMS_SQL package.

If you want to create a sequence in PL\SQL (I suggest that it is usually better to do DDL commands outside of PL\SQL) your procedure could look like this:

CREATE OR REPLACE PROCEDURE procedure_name IS
  test_str varchar2(200):
BEGIN
  text _str := 'create sequence ...'
  execute_immediate(text_str);
END;

0
 
msimons4Author Commented:
I have to run a drop/create sequence script every night from DBMS_SCHEDULER. I was going to put it in a stored procedure and do it that way. Is there a better way?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
please always use the exception handling ( especially when dynamic sql is being used ) which helps a lot to debug issues/errors if any :

begin
execute immediate 'create sequence ...';

exception when others then
dbms_output.put_line('...'); -- you can print the sqlcode or log to some log / error table.
dbms_output.put_line('...'); -- you can print the sqlerrm or log to some log / error table.

end;
/
0
 
Mark GeerlingsDatabase AdministratorCommented:
First I would ask why is it necessary to drop and recreate a sequence every night?

Second, DBMS_SCHEDULER has more flexibility than the older DBMS_JOB (the older database scheduler). DBMS_JOB could only run PL\SQL procedures, so if you needed to drop and re-create a sequence, and you wanted the database scheduler to do it, the only way was with 'execute immediate" in a PL\SQL procedure.  Now, with DBMS_SCHEDULER, that can run host commands, *.SQL files or PL\SQL procedures so you have more options.
0
 
msimons4Author Commented:
Will this work:

CREATE OR REPLACE PROCEDURE procedure_name IS
  test_str1 varchar2(200):
  test_str2 varchar2(200):
BEGIN
  text _str1 := 'create sequence schema.sequence_name1
      increment by 1
      start with 0
      nomaxvalue
      minvalue 0
      nocycle
      nocache;'
  execute_immediate(text_str1);
  text _str2 := 'create sequence schema.sequence_name2
      increment by 1
      start with 0
      nomaxvalue
      minvalue 0
      nocycle
      nocache;'
  execute_immediate(text_str2);'
END;
0
 
slightwv (䄆 Netminder) Commented:
remove the semi-colon inside the string and put it outside the string to terminate that command.

execute immediate:  No underscore.

remove the space in text _str1 and text _str2

Then it 'should' run as long as the syntax is OK (I didn't check it) but again, why create a sequence inside a procedure?  You normally don't do this.

Also, it will fail if the sequence already exists.
CREATE OR REPLACE PROCEDURE procedure_name IS
  test_str1 varchar2(200):
  test_str2 varchar2(200):
BEGIN
  text_str1 := 'create sequence schema.sequence_name1
      increment by 1
      start with 0
      nomaxvalue
      minvalue 0
      nocycle
      nocache;'
  execute immediate(text_str1);
  text_str2 := 'create sequence schema.sequence_name2
      increment by 1
      start with 0
      nomaxvalue
      minvalue 0
      nocycle
      nocache;'
  execute immediate(text_str2);'
END;

Open in new window

0
 
msimons4Author Commented:
Where is the space in text_str1 and textstr2?
0
 
msimons4Author Commented:
Also, this should be a semi colin correct?

test_str1 varchar2(200):
test_str2 varchar2(200):
0
 
slightwv (䄆 Netminder) Commented:
>>Where is the space in text_str1 and textstr2?

re: http:#a37062224 
"text _str1 := 'create sequence schema.sequence_name1"
and
"text _str2 := 'create sequence schema.sequence_name2"

>>Also, this should be a semi colin correct?

Yes.  I missed that one.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Will this work?"

Maybe.  The part I'm concerned about is the "schema." prefix on the sequence name.  Do you need that?  Or, could the procedure be written in the same schema as the sequence?

Yes, you are correct, that should be a semi-colon ";" at the end of the variable declaration.  I accidentally had a colon ":" in my example.
0
 
msimons4Author Commented:
There are 13 schemas so it's easier this way if it can be done. Why do you think there may be a problem if it can be run dynamically OK?
0
 
slightwv (䄆 Netminder) Commented:
>>this way if it can be done.

It 'can' be done.  Just add a loop to add the schema name.  You will likely need to hard-code the names or create a table to retrieve them from.

If not, you can loop through DBA_USERS and 'hard-code the list to exclude certain users.
0
 
msimons4Author Commented:
How do I include:
grant select on schema.sequence_name to role_name;
0
 
slightwv (䄆 Netminder) Commented:
>>How do I include:

re: http:#a37062113  "If you must do it (which is in very rare circumstances), you need execute immediate:"

execute immediate 'grant select...';
0
 
Mark GeerlingsDatabase AdministratorCommented:
Dropping and recreating sequences in multiple schemas every day with a PL\SQL procedure does not look to me like the best way to solve a business problem.  We don't know what he problem is that you are trying to solve with this approach.  If you give us some details on that, we may be able to help you find a better way to solve the problem.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now