SQL & PL/SQL :: Use Of Global Variables

Feb 24, 2012

Explain about Global variables in Plsql? What is use of these global variables and where do use these variables?

View 1 Replies


ADVERTISEMENT

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

Precompilers, OCI & OCCI :: ProC Host Variables As Global

Aug 25, 2008

I Want to declare some host variables with global scope. Like this C variable:

MyFile.h
...
char str_var[20];
...

But i can't put SQL sentences in a include file. And if i do this:

MyFile.pc
...
EXEC SQL BEGIN DECLARE SECTION;
char str_var_h[20];
EXEC SQL END DECLARE SECTION;
...
... I can't see this variable from others sources, although I declare it like 'extern'.

View 1 Replies View Related

Forms :: List Forms Global Variables At Runtime

Jan 9, 2012

we are currently migrating from forms 6i to 11g. We would like to cleanup our global variables at runtime.

Is there any way to list the global variables at runtime?

View 2 Replies View Related

SQL & PL/SQL :: Local Variables Are Bind Variables

Apr 27, 2012

Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.

Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?

Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;

View 8 Replies View Related

Oracle 11g - Global Database Name Value?

Sep 22, 2011

When I installed Oracle 11.g I set 'GlobalDataBaseName' ("orcl.net").SID was "orcl". from where can I invoke 'GlobalDataBaseName' value ?

View 1 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

Update Global Indexes?

Aug 15, 2011

If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?

View 1 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

SQL & PL/SQL :: Global Temporary Table

Feb 20, 2010

What is the best option for GLOBAL TEMPORARY TABLE

1) option create GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS. and wheverever this is used for calculation commit at the end of porcedure.

CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
)ON COMMIT DELETE ROWS;

CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
as
begin

[Code]....

2) create GLOBAL TEMPORARY TABLE without ON COMMIT DELETE ROWS and wheverever this is used use delete from Temp table /Truncate table and then user it.

CREATE GLOBAL TEMPORARY TABLE gtt_test
(
A NUMBER
);

CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)

[Code]....

View 26 Replies View Related

Alternatives For Global Temporary Table

Mar 12, 2013

I have created global temporary tables to be used in my stored procedure, in order to view reports which i created in JASPER. Since global temporary tables are session based, when multiple users are trying to generate the report, every user is getting inconsistent data.

To make it clear, what i meant is if a user A tries to view a report with some filter criteria and simultaneously user B is trying to generate the same report with another filter criteria, User A is getting User B's report data and User B is getting User A's report data. How can we avoid this problem?

View 1 Replies View Related

SQL & PL/SQL :: Drop Global Temporary Table?

Dec 6, 2011

how to drop global temporary table?

while droping global temporary table we are getting below error

"ORA-14452: attempt to create, alter or drop an index on temporary table already in use"

View 1 Replies View Related

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

Server Administration :: Login Using Global DB Name?

Mar 11, 2013

I see we are unable to login to database using global database.
[oracle@sl73usircd01 ~]$ sqlplus DEMO@DB1.COM

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 11 14:47:31 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: why we are unable to login to database using global database name ?

View 2 Replies View Related

SQL & PL/SQL :: Global Temporary Table DDL In Schema

Dec 21, 2011

How to allow only "CREATE GLOBAL TEMPORARY TABLE" DDL in a schema. I have to restrict all DDLs performing by a particular schema except GT Table.

View 8 Replies View Related

SQL & PL/SQL :: Global Temporary Table And JAVA?

Dec 3, 2011

I am using a global temporary table in which place data from a few different queries.

It then select it out into a cursor.

This procedure works fine in PL.SQL Developer and Toad. It doesn't have to be adjusted.

Java has a problem though, as the data is gone when the Java call attempts to acquire it. This is due to session pooling I suppose.

So, my question is somewhat composite.

Is there a setting in Java (JDeveloper) that I could overcome this with? Perhaps a momentary "Hold" on a session?

View 6 Replies View Related

SQL & PL/SQL :: Use Of Local And Global Partition Index

Aug 17, 2010

What is the use of Local and Global Partition Index?

View 1 Replies View Related

Reports & Discoverer :: Global Cursor

Mar 18, 2013

I have a report,in which i have used a function and inside function i made a cursor.and that cursor is used by many functions .so i want to make it globally.so that i would be able to get it in all the function instead of making it in all the functions.

View 1 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

When Do Global And Local Indexes Becoming UNUSABLE?

Nov 29, 2012

RDMS Version : 11.2.0.2
Platfomrm : AIX 6.1

For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?

View 5 Replies View Related

Global And Local Inventory - Patchset?

Apr 21, 2013

1)What is the use of inventory?

2) Global inventory is found by checking the oraInst.loc file. How to find the location of Local inventory?

3)Why is every patchset(11.2.0.2) now a full release?

View 4 Replies View Related

PL/SQL :: Not Able To Update Global Temporary Table

Oct 17, 2012

create or replace procedure p_populate_gtt
as
begin
insert into gtt
select last_name,first_name,null from funcdemo where rownum <51;
update gtt set vote=100
where ln ='Tim';
end;
/

gtt is my global temp table. i am updating vote column which is null to 100.But i am not able to update it

View 21 Replies View Related

Application Express :: CSS In Global Page

Mar 20, 2013

I wanted to apply some CSS throughout my application by adding it in Global Page in APex 4.2

And I used

<style>
#t18InlineError {
color: rgb(243, 12, 12);
}
</style>

It doesn't work.

View 4 Replies View Related

How To Use Variables Within Select

Apr 5, 2011

im as using oracle 8 with sqltools i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them.for example:

select SomeID from SomeTable;

i want SomeID to be put into a variable.but i still want to be able to get a normal select query at the end so that i can see the returned value:

i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;

but could not get it to work.

View 2 Replies View Related

Using Variables In A Trigger?

Oct 24, 2007

consider the trigger below,

CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG
AFTER UPDATE
ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS

[code].....

The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?

View 2 Replies View Related

SQL & PL/SQL :: Using Table Name As Variables?

Aug 23, 2010

Below is the code I am facing problem using tablename as variable.

I have five tavble is scheme Emp1,Emp2..Emp5

CREATE OR REPLACE
procedure emp_up as
tablename1 varchar2(30) ;
Begin
For x in 1..5
LOOP
tablename1 := 'EMP' ||to_char(x);
EXECUTE IMMEDIATE 'update '||tablename1 || 'set ename = ''ZZZZZ'' where ename in (''MILLER'')';
END LOOP;
End;

Error : Identifier EMP must be declare

View 15 Replies View Related

SQL & PL/SQL :: Use Of Cursor Variables?

Mar 11, 2012

what is use of cursor variables?

View 1 Replies View Related

ODP.NET :: Binding SQL Variables

Nov 5, 2012

The following code is getting the 'Not all Variables bound' error. There are only two variables so I don't see the order being an issue. Assume the integers are assigned elsewhere.

DateTime beginDT = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt);
DateTime endDT = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);

SQL.Append(" WHERE DATE >= :beginDTParameter ");
SQL.Append("AND DATE < :endDTParameter");

OracleCommand cmd = connection.CreateCommand();
cmd.Parameters.Add(new OracleParameter("beginDTParameter", OracleType.DateTime)).Value = beginDT;
cmd.Parameters.Add(new OracleParameter("endDTParameter", OracleType.DateTime)).Value = endDT;

View 2 Replies View Related

PL/SQL :: Not All Variables Bound

Sep 4, 2012

I was new to Oracle. When i am Executing this Query, I was getting the error "ORA - 010008 - not all variables bound" .

VARIABLE sathya NUMBER
BEGIN
SELECT sal INTO :sathya FROM emp WHERE empno=7902;
END;

View 10 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







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