I want to create one dummy job using dbms_jobs and execute it under BALA user.This is just for testing purpose.My goal is to test Whether the job executes successfully under the new user i created BALA. All these should happen under BALA user.
View 5 RepliesI am using ORACLE SQL developer.. I am trying to schedule a package to run daily..here is the overview of my package..
-----
create or replace
PACKAGE BODY xxx_MTO_yyy
AS
PROCEDURE yyy_mto (p_message OUT VARCHAR2, p_detail OUT VARCHAR2, p_value OUT VARCHAR2) IS
XXXXXX
CXXXXXX
XXXXXXX
END yyy_mto;
end xxx_MTO_yyy;
Now I created schedule as shown (The sql is processed)
begin
dbms_scheduler.create_schedule
(schedule_name => 'MTO_DAILY',
start_date=> trunc(sysdate)+6/24, repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
comments=>'Runtime: Every day (Mon-Sun) at 6:00 );
END;
Now I am creating SCHEDULER PROGRAM as shown (sql processed)
begin
dbms_scheduler.create_program
(program_name=> 'mto_DATA',
program_type=> 'STORED_PROCEDURE',
program_action=> xxx_MTO_yyy.yyy_mto',
enabled=>true,
comments=>'Procedure to collect session information'
);
end;
Now i am creating the scheduler jobs as shown (here also the sql processed)
begin
dbms_scheduler.create_job
(job_name => 'str_data',
program_name=> 'mto_DATA',
schedule_name=>'MTO_DAILY',
enabled=>true,
auto_drop=>false,
comments=>'nil');
end;
Now I have two questions.
a) the job should have been visible under the "jobs" tab in sql developer. I dont see that.
b) When I tried to run the job manually using
**********
(BEGIN
DBMS_SCHEDULER.RUN_JOB (
JOB_NAME =>'STR_DATA'
);
END;
it failed , saying that "ORA-06553: PLS-ORA-06553: PLS-306: wrong number or types of arguments in call to 'yyy_MTO'". When defining the "dbms_ scheduler. create_program" object , how can I define the arguments?. My procedure has 3 variable out arguments?
how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.
i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..
I need to create PROCEDURE to create user in oracle
CREATE OR REPLACE PROCEDURE "CREATE_USER_ORACLE8"
(
USER_ID in VARCHAR2,
PASSWORD in VARCHAR2,
ROLES in VARCHAR2,
nReturnCode OUT NUMBER
)
BEGIN
[code].......
Compilation errors for PROCEDURE NOG.CREATE_USER_ORACLE8
Error: PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following:
; is with authid deterministic parallel_enable as
Line: 9
Text: BEGIN
i want that the customer execute PROCEDURE (user_id,password,PROCEDURE )
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies View RelatedWhat is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
I've got a problem to create a job :
ORA-06550: line 1, column 93:
PLS-00201: identifier 'PKG_MAINTENANCE.MAINTENANCE_PURGE' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 139
ORA-06512: at line 4
IS GTT will create HWM?
View 4 Replies View RelatedI have oracle 11.2.0.2 on Linux. I have question regarding that i have last week (1/26/2013) hot backup of database and cold backup from (dec 9 2012). and user needs data from the database but needs to create new db.
how can i do that to create new db with Hot backup of last week?
Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.
View 5 Replies View RelatedAs per project requirement, we need to create tab seeprated file for a table. This table contain around 1000000 records.I have written below sql
set pagesize 10000
set feedback OFF
set serveroutput on
set heading OFF
spool D:OfficeworkDatabase_maintainceMATRIX.txt
[code]...
This query is taking more than 6 hours.if there any other way how can we create tab separated file. Or we can export the entire table in tab separated file format.
I want to create a new table 'b' from table 'a'.I know the query will be " create table b as select * from a;" But there is some twist in my question. I do not know how many table are there in table 'a' all I know is that there is one column named "timestamp" in table 'a'.
Now I want to create table 'b' with the difference that the column "timestamp" should contain the value "sysdate" instead of the earlier values which were there in table 'a';
Note: I have to do this in single query. I can not first create table 'b' from 'a' and then update the values of column "timestamp" to sysdate.
how to create the trigger that count the last 7 day about from sale table if the last 7 day amount is greater then 10000 then update the commission 2% other wise do nothing
View 19 Replies View RelatedI want to create view as follows.
Example:
Create or replace v_name
select job,sal, <funcation>
from emp;
Note:- Funcation returning 4 values.
Requirement:- I want to create view 6 columns.
how to create view.
I have connected to the database and am logged in with SQLPLus and have the SQL prompt.
I need to run a query and would like to create a file 'x.sql' and type the query into that file, save it then run it.
I want to create a standby db "PROD" to another server. But the problem is that there is also and existing "PROD" instance in that server :(
Can I create it using different name like "PROD2"?
primary key should be
W1
W2
W3 like this.
1,2,3 are sequence numbers.
When I try create controlfile reuse resetlogs database 'cms';
ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file
'%ORACLE_HOME%DATABASEDBS1%ORACLE_SID%.ORA'
ORA-27048: skgfifi: file header information is invalid
OSD-04001: invalid logical block size (OS 1919900730)
What could be the problem? And why is it looking for DBS1%ORACLE_SID%.ORA' should there be such file?
ora11, Win2003 server
I cant create a trigger for the following problem "write a trigger to change the "hiredate"(in emp tale) in the default format even if the user enters it in some other format, say year/mon/date..."
View 7 Replies View RelatedI was using DBCA to create database in 11g. It goes to the last step ,but was not able to show up database creation screen. I am running it on AIX.
View 1 Replies View RelatedI'm trying to create a ASM disk using oracleasm.I created list the disk (using oracleasm), but the view V$ASM_DISK and ASMCMD (lsdsk command) can't see it..Look:
CODE$ oracleasm createdisk DISK3 /dev/sdc1
Writing disk header: done
Instantiating disk: done
CODE$ oracleasm listdisks
DISK1
DISK2
DISK3
Looks fine, but the ASMCMD doesn't listing it...
CODEASMCMD> lsdsk
Path
/dev/oracleasm/disks/DISK1
/dev/oracleasm/disks/DISK2
And the V$ASM_DISK can not...
CODESQL> select name from v$asm_disk;
NAME
------------------------------
DATA_0001
DATA_0000
If I try create table from the following syntax
create table a as select * from table b;
Then I could get only base table structure alone, I would like to get partition syntax as well.
I am trying to create a table that will increment my ID by one using the following commands:
/*Creates the log needed to increment ID*/
create sequence seq_log;
CREATE TABLE MESSAGE_LOG_TABLE
(
IDNUMBER(10)NOT NULLPRIMARY KEY,
[code]...
When I run the above my create sequence completes successfully but I get a ORA-00955: name is already used by an existing object error message on the create table. I have dropped all tables and sequences before running my command but I still get the same error message.
After it bombs out it appears that SQL+ want's more information for it begins to give me line numbers as if it is looking for a ";" to end the above command. I have to exit SQL+ and log back in to continue working.
How do I create a filename with a date using SQL?
View 2 Replies View Relatedif there is a way to create a custom log files or if there is way to utilize the built in log function of Oracle. Here is the code with what I am trying to do:
IF (_CONDITION IS NOT NULL) THEN
IF (V_CONDITION <> 'NS' OR V_CONDITION <> 'NE' OR V_CONDITION <> 'AR' OR V_CONDITION <> 'OH' OR V_CONDITION <> 'SV') THEN
[Code]....
Ok, so where you see V_CONDITION := 'XX'; I would like to have the value of V_CONDITION be input into a log file as apposed to changing the value to XX. So I would have something like:
RAISE BAD_CONDITION_CODE;
The problem is that I am not sure how to create the actual log file that the value of V_CONDITION will be stored in.
running on 10.2.0.5 and above satisfies the following:
having locally managed tablespace with assm enabled
create table t1 (n1 number) storage (next 1M);
insert into t1 values (1); commit;
create table t2 storage (next 2M) as select * from t1;
This will indeed set the storage option next to 2M;
The same situation in 10.2.0.4 will allways set next option for the ctas statement to the initial value of table t1.
Is this an impact of patch set 4 (10.2.0.5)? Why does it differ from 10.2.0.4 to newer releases?
Create a trigger that stamps the date_created and the date_updated column with current date on new and updated records?
View 2 Replies View RelatedHow do I create a trigger for the age. I want an alert to appear when the date of birth field is less than the age of 17. The formula is age=sysdate-dateofbirth
View 11 Replies View RelatedI am creating the following dbms_sql procedure...
CREATE or replace PROCEDURE table_demo
(tabname IN varchar2)
IS
cursor_name INTEGER;
rows_processed INTEGER;
[code]...
There are no compilation errors.I call this code from the following anonymous block...
DECLARE
X CHAR:='T';
BEGIN
TABLE_DEMO(X);
END;
This also compiles successfully and without any errors. It runs properly as wellHowever when I run 'select * from T'. Then system throws up the error of table or view does not exist.
i would like to create a sequence inside a dml trigger. Is it possible? I created a trigger and it is compiled sucessfully.
create or replace trigger tri_update_test
after delete on test
declare
pragma autonomous_transaction;
[code]...
trigger created sucessfully.And i try to delete data from the test
delete from test where id=5;
Output:
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.TRI_UPDATE_TEST", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRI_UPDATE_TEST'