Creating And Dropping Global Temp Tables Inside A Single Procedure

Aug 20, 2010

I am writing a procedure that will be called from a java wrapper.

The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.

Create or replace proc_name ()

update table........

delete from ..........

CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)

insert into............

drop table TSAAG;

End;

creating a global temp table inside a procedure is expensive...

Do we have anything like creating table before and calling the instanse of it in procedure.

Do we have any alternatives to this..

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Index On Global Temp Tables

Jun 16, 2010

I am trying to use Global temporary tables, and index on this table to get my results faster. I can see even if I run any query on this table, it does full table scan and not Index scan..

create global temporary table abc_tab on commit preserve rows
as select a,b,c from xyz;

create index lmn on abc_tab(a,b,c)

View 10 Replies View Related

SQL & PL/SQL :: Use Local Temp Tables Within Procedure?

Nov 23, 2012

Is it possible to create local temp variable based table within sql procedures, i am planning on writing few select queies,and want to store teh result of each select to a temp variable based table.

Within sql serevr, i can create temp tables within and then later after i use teh data at teh end drop the table.

Is it possible, since i don't want to create any physical tables.

View 1 Replies View Related

SQL & PL/SQL :: Store All Rows Of Columns Into Single Variable / Use In Inside Of Stored Procedure

Mar 6, 2012

i want to store all rows of columns into single variable and then use in inside of SP

declare
CUR_REC SECURITY_TYPE%rowtype;
begin
select *
into CUR_REC
from SECURITY_TYPE;
[code]....

it return ORA-01422: exact fetch returns more than requested number of rows error. Is any chance to implemented above scenario in oracle 10g

View 4 Replies View Related

SQL & PL/SQL :: Global Temp Table

Sep 2, 2010

what are minimum privilege required to create GTT (Global Temp Table)?

View 7 Replies View Related

Cursor Versus Global Temp Table

Jan 16, 2013

We had an issue with a PL/SQL package taking hours to run as a concurrent program. Database version is 10.2.0.4.0, running on Linux x86 64-bit. A tkprof'd trace file revealed the problem SQL statement to be a cursor. This one SQL statement would run for 3+ hours. I copied the SQL statement and ran it in TOAD and it completed in seconds, returning the exact same result set. To resolve the issue in the PL/SQL package I created a global temp table and ran the exact same SQL statement as an INSERT into the global temp table.

Again, instead of hours, the SQL statement completes in seconds. If I revert the change, it goes back to taking hours. I've attached the relevant sections of the tkprof showing the two SQL statements (identical other than the insert in front of one) and the resulting explain plans and performance data. I've always been under the impression that a cursor was a better option than a temp table and I've never run into a situation where the same SQL statement runs so much longer when executed as a cursor.

Attached File(s)

SQL_As_Cursor.jpg ( 274.02K )
Number of downloads: 7

Explain_for_SQL_As_Cursor.jpg ( 189.43K )
Number of downloads: 4

SQL_as_Insert.jpg ( 277.38K )
Number of downloads: 4

Explain_for_SQL_As_Insert.jpg ( 180.66K )
Number of downloads: 2

View 2 Replies View Related

SQL & PL/SQL :: Performance With Union And Global Temp Table

May 24, 2010

I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.

So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.

When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.

View 7 Replies View Related

SQL & PL/SQL :: Declaring Global Variable Inside Package?

Sep 11, 2012

I want to declare global variable inside package. get the correct query. how to assign value for that variable.

SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /

Package created.

SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)

[Code] ..........

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY NEW_PACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar

View 10 Replies View Related

SQL & PL/SQL :: Dropping Users - Queue Tables

Dec 9, 2010

I am trying to drop a user but i get the following error

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

A couple of questions on this error:

- I did a search on the forum and the thread [URL] appears to have a solution to it by using

DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);

What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?

- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?

View 2 Replies View Related

SQL & PL/SQL :: Creating Global Variables In Sqlplus On 11g

May 4, 2011

I have taken some procedures code from SVN repository.These procedures are split into different schema. There are some client and common parameters used in the from clause of table in these sp.for example

select *
from client.emp;

during the compling procedure, we replace the client value into some schema name.

select *
from scott.emp;

The client and common parameter should be different for every schema.

Is any possible to create global varaible for client and common parameter and subsitute value in it.i.e without editing client and common parameter?

View 8 Replies View Related

SQL & PL/SQL :: Dropping A Procedure During Execution

Apr 26, 2012

When the procedure is executing can we drop a procedure . Is there any way to drop the procedure with force .

View 5 Replies View Related

PL/SQL :: Creating Collections Inside Package

Nov 23, 2012

I was trying to create types inside package like:

-create or replace package pack1 is
type udtable is table of integer index by binary_integer;
end;

-create or replace package BODY pack1 is
type udtable is table of integer index by binary_integer;
end;

Although this is not exact query what i have doubt in my mind. is it possible to create object types/collections inside/with packages?

View 6 Replies View Related

Server Utilities :: Schema Level Import By Dropping Existing User That Contain Tables

Jan 20, 2011

Our Testing DB is running in No archive log mode. I did a schema level import by dropping the existing user that contain tables, recreate the user and finished the import. Now they want the old tables back.Is there is any way to recover the old tables?

View 5 Replies View Related

PL/SQL :: SYSDATE In DDMMYYYY Inside Single Quotes

Dec 4, 2012

I have a requirement like:

Create Table A
( a number,
CreationDate DATE)
PARTITION BY RANGE (CreationDate)
(
Partition p_03122012 VALUES LESS THAN (TIMESTAMP' 2012-12-04 00:00:00'),

[Code]...

NOTE: Partition are named as p_ddmmyyyy where ddmmyyyy is date.

select * from dba_tab_partitions where table_name = 'A'

Now my requirement is :

select * from dba_tab_partitions where table_name = 'A' and partition_name > 'p_ddmmyyyy' (Here I want ddmmyyyy to be from sysdate i.e. date we get from - Select to_char(sysdate,'ddmmyyyy') from dual
i.e. for today it becomes
select * from dba_tab_partitions where table_name = 'A' and partition_name > 'P_04122012'

So it returns me two rows.

How can I do it in single quotes.

View 6 Replies View Related

Global Temporary Tables

Jan 17, 2011

I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.

DB1 uses some Global Temporary tables to write and store session level information.

I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.

DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.

Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.

View 1 Replies View Related

PL/SQL :: Create A Procedure Inside A Procedure

Jun 25, 2012

DB version:11g

can we create a procedure inside a procedure...if not is there any alternative??

in db2 it is allowed so do oracle support this????

View 5 Replies View Related

SQL & PL/SQL :: What Is A Global Procedure In Oracle And Its Usage

Aug 6, 2010

what is a global procedure in oracle and its usage

View 5 Replies View Related

Server Utilities :: How To Export Global Temporary Tables

Apr 18, 2013

There's an Application Express application which is based on a schema named TRAFOGLED. In order to let users test new features, there's a test application (Apex has export/import capabilities; no problem about that) which is based on another schema whose name is TRAFOTEST.

I'd like to export TRAFOGLED and import it into TRAFOTEST.I'm using 10gR2 EXPDP utility with a parameter file. Everything seems to be OK, except the fact that I'm unable to export global temporary tables (GTT). How can I tell? I didn't see them after import!

These are my GTTs:
SQL> show user
USER is "TRAFOGLED"
SQL>
SQL> select table_name from user_tables where temporary = 'Y';

[code]...

C:TEMP>
No tables were exported. Certainly, I don't expect any data to be exported, but I'd be happy with CREATE TABLE statements so that I don't have to create these tables separately.

View 4 Replies View Related

SQL & PL/SQL :: Executing A Procedure Containing A Global Temporary Table

Mar 3, 2010

create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';

stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;

-- when am trying to execute this

begin
test;
end;
-- showing ora-01031, insufficient privileges.

View 9 Replies View Related

Temp Table Within A Stored Procedure

Jul 15, 2013

I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
[code]........

View 1 Replies View Related

SQL & PL/SQL :: Creation Of Temp Table Within Procedure

Aug 17, 2010

I need to create a temp table within a stored procedure. I want to apply some logic on the table, and then delete it when it is completed in runtime -- all inside the stored procedure.

View 13 Replies View Related

SQL & PL/SQL :: Temp Table Within Stored Procedure

Jul 15, 2013

I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP
IS
BEGIN
CREATE GLOBAL TEMPORARY TABLE myAAAA
AS
(SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,
DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,
AAAA.1D1D
[code].....

View 7 Replies View Related

PL/SQL :: Temp Table Within Stored Procedure

Jul 15, 2013

I'm still getting use to the syntax and features within Oracle. I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.

CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN  CREATE GLOBAL TEMPORARY TABLE myAAAA    AS  (SELECT  AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777,                       DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C,                       AAAA.1D1D               FROM mySchema.FFFF_07 FFFF               RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B               RIGHT OUTER JOIN
[code].........

View 10 Replies View Related

SQL & PL/SQL :: Using Procedure Inside Function?

Nov 8, 2011

I have question.Using procedure inside the function ?can I get better performance?

View 8 Replies View Related

View Inside The Procedure

Jun 12, 2013

I am having a view say name vw_mytable , i need to call this view inside the stored procedure it is saying as Grant execute on

usera.vw_mytable to userb;ORA-02204: ALTER, INDEX and EXECUTE not allowed for views

how do i create a store procedure to call a view from another user.Usera is having a view and i need to create procedure in userb and call usera.vw_table.

View 1 Replies View Related

Server Administration :: Utilize Temp Tables As Part Of A Scheduled Job?

Jan 18, 2013

There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.

Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?

View 2 Replies View Related

SQL & PL/SQL :: How To Set Environment Variable Inside Procedure

Nov 24, 2010

I am calling a select query inside a procedure but i need to set environment variable 'set linesize 200' inside that procedure but i am not able to create the procedure due to some error. I am attaching the procedure query here with:

before the select query i need to insert this environment variable : "set linesize 200"

create or replace
procedure TABLESPACE_USAGE
is
l_mailhost VARCHAR2(64) := 'ip address';
l_from VARCHAR2(64) := 'email id';
l_subject VARCHAR2(64) := 'TABLESPACE_USAGE1';
l_to VARCHAR2(128) := 'email id';
[code]......

View 2 Replies View Related

SQL & PL/SQL :: Procedure Call Inside Trigger?

Aug 8, 2011

I am trying to call procedure inside trigger.. but i get error ora-04098 ..

create table emp_hstry
as
select * from emp
where
1= 2 ;

create or replace procedure emp_del_hstry(v_empno NUMBER ,
v_ename VARCHAR2,
v_job VARCHAR2)
is
insert into emp_hstry (empno,ename,job)
values (v_empno,v_ename,v_job);
COMMIT;
end;

create or replace trigger emp_del_hstry1
after insert or delete
on emp
for each row
begin
if deleting then
emp_del_hstry(:old.empno,:old.ename,:old.job);
end if;
end;

delete from emp
where
empno = '7369'

AFTER delete statement run i get ora-04098 message i also check show error command ,but still i am not getting solution of this error ..

View 7 Replies View Related

SQL & PL/SQL :: Cursor In DML Statement Inside A Procedure

Sep 12, 2011

I have a DML Statement inside a procedure and i use a cursor variable to get the values checked as below . I have attached my procedure not completely but the declaration part and the DML statement part.

The issue is my procedure is not inserting the records at all. It selects the values and then inserts accoringly but its not selecting because of the cursor reference R_LOC.LOCATION_GID.

when i hard code the value in the DML statemnt for the R_LOC.LOCATION_GID, the rows are inserted as expected. So i guess the way the procedure executes the value is not correct.

Modifying my select part which uses cursor variable R_LOC.LOCATION_GID under Insert statement.

select d.servprov_gid, d.depot_gid, replace(d.appointment_time,'':'') appmt_time, d.'||v_day_to_use||' DayUsed
from tesco_fresh_templates t, tesco_fresh_templates_d d, location_refnum r
where t.set_id= d.set_id
and d.depot_gid=r.location_gid
AND D.SERVPROV_GID=''R_LOC.LOCATION_GID''
and r.location_refnum_qual_gid=''TESCO.IVS SCHEDULING''
and (r.location_refnum_value=''YES'' or r.location_refnum_value=''Y'')
and t.default_set=''Y''

View 21 Replies View Related

SQL & PL/SQL :: Bind Variable Inside Procedure?

Jun 18, 2013

I have two procedure , from first procedure having some ref cursor output.

from second procedure I need to call first procedure and i need to process ref cursor output from first procedure so I decide to use bind variable to process ref cursor output but it showing error .

can I define bind variable inside the procedure , then how can I define it .

SQL> CREATE OR REPLACE PROCEDURE emp_by_job (
2 p_job VARCHAR2,
3 p_emp_refcur OUT SYS_REFCURSOR
4 )
5 IS
6 BEGIN

[code].....

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved