Oracle regularly creates a sequence through a JOB

Because the business needs to add a sequence every month, it is expected to use the job to create a regular one year each time. Some problems encountered when writing this job are recorded here. It seems that

Because the business needs to add a sequence every month, it is expected to use the job to create a regular one year each time. Some problems encountered when writing this job are recorded here. It seems that

Because the business needs to add a sequence every month, I thought of using the job to create a regular one year each time. Some problems encountered when writing this job are recorded here.

Step 1: Write the stored procedure for creating a sequence. The Code is as follows:

The stored procedure is successfully created, but the following error is returned:

SQL> exec pro_create_seq ('factory _ NOTICE_INFO_DB _');

Begin pro_create_seq ('factory _ NOTICE_INFO_DB _ '); end;

ORA-01031: insufficient Permissions
ORA-06512: In "YCPS. PRO_CREATE_SEQ", line 16
ORA-06512: In line 2

Search for the solution on the Internet and find that the ROLE is invalid in the stored procedure, and the authorization must be displayed.

There are two solutions:

1. PassGrant create sequence to ycps;To display the authorization to the user
2. Modify the stored procedure and addAuthid Current_UserYou can use the role permission for the stored procedure.

I used the second method, that is, adding Authid Current_User. The modified stored procedure is as follows:

SQL> exec pro_create_seq ('factory _ NOTICE_INFO_DB _');

PL/SQL procedure successfully completed

Execution successful.

Step 2: Create a job and call the Stored Procedure regularly to create a sequence.

SQL> variable job1 number;
SQL> begin
2 dbms_job.submit (: job1, 'Pro _ CREATE_SEQ (''factory _ NOTICE_INFO_DB _ ''); PRO_CREATE_SEQ (''factory _ NOTICE_INFO_XB _''); ', sysdate, 'Add _ months (TRUNC (SYSDATE, ''yyyy'), 12 )');
3 commit;
4 end;
5/

PL/SQL procedure successfully completed
Job1
---------
84

SQL> begin
2 dbms_job.run (84 );
3 end;
4/

Begin
Dbms_job.run (84 );
End;

ORA-12011: unable to execute 1 job
ORA-06512: In "SYS. DBMS_IJOB", line 637
ORA-06512: In "SYS. DBMS_JOB", line 284
ORA-06512: In line 3

It can be seen that the job is successfully created, but an error occurs during execution. The following describes how to query ORA-12011 errors:

ORA-12011: execution of string jobs failedCause: An error was caught in dbms_ijob.run from one or more jobs which were due to be run. action: Look at the alert log for details on which jobs failed and why. SQL> select value from v $ diag_info where;

VALUE
--------------------------------------------------------------------------------
/U01/app/Oracle/diag/rdbms/ycps/ycps1/trace

The alert log Path is found above. The log file content is as follows:

Thu Aug 16 14:30:57 2012
Errors in file/u01/app/oracle/diag/rdbms/ycps/ycps1/trace/ycps1_ora_2622362.trc:
ORA-12012: Automatic job 84 Error
ORA-01031:Insufficient Permissions
ORA-06512: In "YCPS. PRO_CREATE_SEQ", line 15
ORA-06512: In line 1

As you can see, it is still a permission issue. However, you can call the stored procedure through exec pro_create_seq ('factory _ NOTICE_INFO_DB _ '), which is confusing. Later, explicit authorization is required, that is, method 2 mentioned above. So after testing, the operation succeeded:

SQL> conn sys/oracle @ psdb as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS

SQL> grant create sequence to ycps;

Grant succeeded

SQL> conn ycps/ycps @ psdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as ycps

SQL> begin
2 dbms_job.run (84 );
3 end;
4/

PL/SQL procedure successfully completed
It seems that the use of Authid Current_User is limited. You need to pay attention to the authorization issue when executing DDL statements through the stored procedure.