SQL & PL/SQL :: Get DBMS-PIPE Package?
Sep 24, 2012SET serveroutput on size 1000000
SET wrap on
SET linesize 80
DECLARE
v_text VARCHAR2 (4000);
v_res NUMBER;
v_num NUMBER;
[code]...
ADVERTISEMENT
Grant Permission To User On Package Dbms-transaction?
Mar 21, 2013i have to grant permission to a user on a package dbms_transaction.After i granted permission i am getting error:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_TRANSACTION", line 88
ORA-06512: at line 1
Pipe Function And Materialized View
Feb 27, 2011Pipe Function and Materialized View.
There is a materialized view:
CODECREATE MATERIALIZED VIEW MY_MAT_VIEW NOLOGGING
REFRESH FORCE
START WITH SYSDATE NEXT SYSDATE + 5/24/60
AS
SELECT F1,
F2,
F3
from SOMETAB;
And there is a pipelined function:
CODEDROP TYPE MY_MAT_VIEW_table_type;
DROP TYPE MY_MAT_VIEW_row_type;
CREATE TYPE MY_MAT_VIEW_row_type AS OBJECT
(
F1 number,
F2 varchar2(100),
F3 date
[code].......
I've noticed that when materialized view is near to be refreshed (every 5 minutes) there is some "delay" in getting result sets using this query:
SELECT *
FROM TABLE(FN_GET_MY_MAT_VIEW(100)) a;
And when the refresh is finished the result set returns immediately.
Question.
Is there a way to avoid such "delay" while materialized view is refreshing?
SQL & PL/SQL :: Spooling Data In Pipe Delimited File
Feb 15, 2010I am trying to Spool the data in pipe delimitted csv file but some of the records going on another line from the same records. Currently some of the data going to next line as below oulined in the 2nd and 3rd line (in bold - |Home & Family) . I have following sql setting in my spool file:
set linesize 4000 pagesize 0 trimspool on feedback off verify off echo off
set define off
spool Stk_hold_Sec_Tsk.csv
I tried increase linesize to 5000 but its not working.
Ex.
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
Data should be like into the file:
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager|House & street
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User|House & street
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street
I think it should be something with linesize or pagesize but not sure
SQL & PL/SQL :: PIPE Multiple Rows At Once In PIPELINED Function?
Sep 13, 2012I've used PIPELINED FUNCTION and I've no issues in using this. Just wanted to know is there a way so that I don't need to pipe each row separately and I can pipe a set of rows at once.
Like we use BULK COLLECT INTO to fetch multiple rows at once instead of fetching one row using SELECT INTO.
Below is the test case:
CREATE TABLE TMP_EMP
(
EMP_ID NUMBER(10,0),
EMP_NAME VARCHAR2(100),
DEPT_ID NUMBER(10,0),
SALARY NUMBER(14,0),
[code]....
SQL & PL/SQL :: Producing Pipe Delimited Flat File
Oct 20, 2011I have a task to code a procedure and function in sql developer that will extract data within a date range (Jan 1 to April 3) from a source (source_name: expenses)and produce a text-file in pipe-delimited format.
View 2 Replies View RelatedSQL & PL/SQL :: Parallel In Pipe-lined Table Function Not Working?
Feb 1, 2011'Oracle fast parallel data unload into ASCII file(s)' in this blog: URL....I have compiled the code and created the objects and the directory in my DB...But when I execute :
SELECT *
FROM TABLE(
DATA_UNLOAD(
CURSOR(
SELECT /*+ PARALLEL(A, 2, 1) */
TABLE_NAME || '|' ||
COLUMN_NAME || '|' ||
DATA_TYPE
FROM MYTABLE A
[code]....
It is supposed to return 2 rows (because of parallel execution), but it just returns 1..Do I have to do something special in order to make parallel pipelined function work
Server Utilities :: Export / Import Using Unix Pipe
Jun 9, 2010Is there any possible to export & import the data using unix pipe parallel with two server( at the same time)?
Eg. In Server A, i will export the data & import the data in server B at the same time. (300 GB data)
SQL & PL/SQL :: Split Is A Pipe Line Function To Convert Row As Columns
Jan 31, 2013In this query split is a pipe line function to convert row(rows stored with , delimited) as columns like below
for ex for below query
SELECT * from TABLE(SPLIT('bbb003,bb004'));
out put is
bbb003
bb004
now i have to apply same function on column,column is storing data with ',' separated.and i have tried like but it's throwing missing expression. how i can use this function on entire column from this table.
SELECT * from TABLE(SPLIT(select candidates FROM ibis.cw_uploads_inprogress ));
SQL & PL/SQL :: Pipe Line Function Taking Time To Return Table Record
Mar 15, 2011I want to use a function in join clause. so i go for pipelined function(using for loop to get record & 1 more loop to fetch in table type variable). i achieved what i required. but problem is it takes much time to fetch data. is there any other approach which returns table records without pipelined function.
View 2 Replies View RelatedSQL & PL/SQL :: Converting Comma And Pipe Delimited Input String To Nested Table
Nov 12, 2010I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
PL/SQL :: Find Out In Database Package Where Package Is Installed
May 28, 2013I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
SQL & PL/SQL :: Global Exception In Package Available Outside Package?
Jan 15, 2012I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
SQL & PL/SQL :: DBMS Job Scheduler
Apr 23, 2012How to pass parameter value to a procedure in schedule jobs ?
--procedure :
create or replace procedure updating_temptable(empcode in varchar2) is
begin
update ts_employee_master mas
set mas.last_accessed = sysdate
where mas.employee_code = empcode;
end;
--scheduler
begin
sys.dbms_job.submit(job => :job,
what => 'updating_temptable;',
next_date => to_date('24-04-2012 00:30:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(SYSDATE)+1+30/1440');
commit;
end;
/
How to pass employee_code in the procedure[updating_temptable ] in dbms job scheduler ?
SQL & PL/SQL :: Error Using Dbms-lob?
Mar 6, 2013I am begginer programing oracle and I have a issue to resolve but I can't resolve it.I have a procedure that upload a image from a directory. But when I pass the path, the function bfilename put a slash "/" in the path. I don't know why.Here my code.
CREATE OR REPLACE PROCEDURE p_grava_assinatura_gestor AS
---------------------------------------------------------
-- Crio o Cursor dos Nomes dos Arquivos JPG. --
---------------------------------------------------------
CURSOR cursor_nome_arquivo IS
SELECT p.id, (p.empresa || '_' || p.chapa || '.JPG') AS noarq
[code]...
Oracle 9i Dbms-redefinition?
Oct 29, 2010I am trying to redefine a table as to get clear of the thousands of chained and migrated rows. The darn table contains a lob column.I am trying to complete the process with the dbms_redefinition package.
I've createad the temporary table with all option to nologging. I've started the redefinition at midnight and it not yet completed and I can't find why it is taking so long. I've done the process with a table, similar in size in a Dev environement and it took at most two hours. In production, the process has been going on for 10h30 now.
I can see my tablespace size increase from time to time and when I querry the dba_segments to find out the size of both tables my temporary table is now 4 times the size of the original table.
SQL & PL/SQL :: Create DBMS-SQL Procedure?
Oct 7, 2010I 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.
Dbms Job Is Not Executing Automatically
Aug 11, 2011Database 1(sm01):
=============
oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.Daily jobs are running fine. But hourly job is not executing automatically. If forced (exec dbms_job.run(<enter here job number>), this execute fine.
job_queue_processes=5
total jobs in schema=2503
total jobs in db = 2614
Even there are many jobs scheduled, next_date for 2234 jobs are lesser than the sysdate. Again in 269(2503-2234) jobs, 2265 are having NULL in the interval column.
Database 2(sm02):
=============
oracle, 9.2.0.6
there are 4 jobs scheduled in oracle dbms_job. 3 jobs will run everyday at 4.00AM. 1 job will run at every hour.All the jobs are not running automatically. If forced (exec dbms_job.run(<enter here job number>), these execute fine.
job_queue_processes=5
total jobs in schema=7
total jobs in db = 7
I planning to follow the below steps to avoid the above issue.
1.) Restart the job queue process by executing alter system set job_queue_processes=0
2.) Increase the value for the job_queue_processes.
3.) Restart the database
But I got stuck in the 2nd step. what value I need to put for this job_queue_processes parameter?
SQL & PL/SQL :: Execution Details Of DBMS Jobs?
Sep 7, 2010Under what userid(privileges) does a scheduled DBMS Jos run, is it under the same user that scheduled the job?
Does oracle internally log in(opens a new session) in order to run a scheduled DBMS Job?
some reference websites where I can get some details about how the scheduled jobs are executed?
SQL & PL/SQL :: Using DBMS-METADATA To Transform Tablespace DDL?
May 18, 2010I'm currently busy with database consolidation, so I'm searching for a solution to generate some useful DDL to prepare the new target database before importing the application's data. This should include TABLESPACE DDL and all additional users with their grants.
So first I thought of developing a simple script, which will create the CREATE TABLESPACE DDL but with transformed datafile paths.But my throws some errors and I don't understand why:
ORA-31604: invalid NAME parameter "STORAGE" for object type TABLESPACE in function SET_FILTER
declare
l_hObject NUMBER;
l_Objddl CLOB;
[code]...
Where Value Of Systimestamp / Sysdate Comes From And Dbms-scheduler
Mar 27, 2013I saw bunch of other posts but I could find the post that exactly explaining about where the value returned as systimestamp / sysdate comes from or impacted Here is my situation I have an access to this db (let me call db A) and when I access it, I get following result. I don’t have full access to this db so I cannot experiment a lot here.
SYSTIMESTAMP CURRENT_TIMESTAMP LOCALTIMESTAMP DBTIMEZONE SESSIONTIMEZONE
--------------------------------------------------------- --------------------------------------------------------- ------------------------------------------------ --------------------- ----------------------------
27-MAR-13 02.31.55.041411 AM +00:00 26-MAR-13 07.31.55.041416 PM -07:00 26-MAR-13 07.31.55.041416 PM +00:00 -07:00
I’m in PST timezone.I have my db which I have full access as well as its host.I can make result like db A on my db if I started up db and its listener while TZ environment variable equal to UTC.Now I saw in other post that someone was trying to retrieve systimestamp value in a job executed via dbms_scheduler.run_job.
So I did that in two ways. 1 with use_current_session = true and 2 is false for the same.On my db, results are the same (both returns time in UTC) but on db A, I got UTC time when use_current_session = true and PST when use_current_session = false.
So questions are:
What could be the difference in setup between my db and db A?
Is there a query, logfile, or anything I should check to find out what can be the difference?
I tried to find the cause with my db and I could see the same result as db A which is to see UTC time if use_current_session = true and PST time if use_current_session = false by bringing up the db listener after I set TZ environment variable equal to PST8PDT. However this causes systimestamp from sqlplus session become also a PST time.
The reason I’m playing around with the setup and checking systimestamp value is because we are facing the situation where everywhere except pl/sql job submitted by enterprise scheduler service is pointing wrong timezone (PST instead of UTC)
PL/SQL :: Oracle 10g On Windows - Dbms-scheduler?
Jan 2, 2013We have oracle 10g On windows....
i have created a schedular like below
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'TEST_JOB',
job_type => 'PLSQL_BLOCK',
[code]....
what is the repeat_interval of above. I have specified MINUTELY but it's not firing after each minute.
Dbms-scheduler - Third Program Finishes Before Second?
Oct 26, 2010I've written a chain with four programs. The third program is depend on the output of the second. However the third program finishes before the second. How do I wait for the second to finish.
DBMS_SCHEDULER.define_chain_rule
(chain_name => 'SATURN.SHRROLL_CHAIN',
condition => 'TRUE',
action => 'START SHRROLL_STEP',
rule_name => 'RULE_100',
comments => 'START GRADE ROLL');
[code]...
SQL & PL/SQL :: DBMS Job Commit Other Sessions Automatically
Apr 14, 2011I am calling one procedure through job and after submitting the job i am using commit. problem is when job close the session it commit the code which is inside procedure and as per my understanding in another session also, which i want to avoid .
my procedure is
Create My_proc is
Begin
Insert into my_table values(1,2,3);
End;
And my job proc is
Create my_job_proc is
Begin
dbms_job.submit(jobno,'my_proc;',sysdate,null,null,false);
commit;
End;
now i am calling my job proc is
Declare
Begin
my_job_proc;
End;
Now problem is once it finish, it will insert the data into my_table which i don't want until I call Commit.I already try 'Set autocommit off'
Slave Sessions In DBMS-PARALLEL-EXECUTE?
Oct 4, 2012I need to identify, whether my current session is one of slave sessions scheduled as Oracle scheduler job via DBMS_PARALLEL_EXECUTE or not. I already succeeded using join of dba_scheduler_running_jobs, user_parallel_execute_chunks and v$session, but I feel that this is not optimal approach.
View 1 Replies View RelatedEnterprise Manager :: Cannot Access EM Of DBMS 11g R2 On Windows 64
Nov 14, 2010I have successfully installed Oracle DBMS 11g R2 on Windows 7 64-bit Ultimate. I can only access the EM using only one user which SYSTEM. Although, I created different users which are unlocked but no success in accessing the EM even with the user name 'SYS'.
View 2 Replies View RelatedClient Tools :: Way To Enable Dbms Profiler
Mar 4, 2010i want to tune my plsql code using dbms_profiler.
but dbms_profiler is disabled in my TOAD.
i have GRANT on this pkg. even though i have it disabled. how should i enable it??
RAC & Failsafe :: Dbms Jobs Initiates Many Sessions
Sep 11, 2012We created a job yesterday which will call the below procedure. if we start this job, it initiates 92 sessions parellely. How it is initiating 92 sessions parelley?
procedure prc_HECTOR_CIDB_IN_PURGE
IS
cursor CUR_PROC is
select rowid from CUSTMODEL.HECTOR_CIDB_IN where PROCESS_FLAG in ('Y','F');
TYPE TYP_CUR_DATA IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
v_typ_cur_data typ_cur_data;
[code]....
SQL & PL/SQL :: Dbms-scheduler - Stop Jobs When Database Down?
Nov 13, 2011How to stop all jobs(dbms_scheduler) when ever database down or power failure and I need to restart all jobs when ever database is up.
View 3 Replies View RelatedPerformance Tuning :: DBMS Stats Is Gathering
Oct 26, 2010Oracle 10g has the feature of automatic stats gathering in this case is it necessary to run DBMS_STATS on tables manually. Does the stats gathered become stale when the auto stat runs ?
View 1 Replies View Related