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


ADVERTISEMENT

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

Pass Variables For UserID And Password To Sqlplus In Windows From Text File?

Jul 23, 2013

I would like to store user-id, password and the TNS-Entry in a small text file and pass them to a script to log in and execute another script on the database from Windows.

Example of the text file samp.txt that stores user info:

username=Jdoe
password=candybar
db_sid:orcl

main script main.bat is as follows:

--read the samp.txt do not know how to do it

sqlplus -s &username/&password@&db_sid @db_info.sql

db_info.sql contents:

column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
spool C:\server\DB_Report_&file_time..log
select * from v$instance;
spool off;
exit

View 1 Replies View Related

SQL & PL/SQL :: Creating Loop - Sequence Should Add A Number In The End Of Variables

Jun 12, 2013

SELECT
DECODE(BREINV1.NAMEKEY, NULL,'0','1') "BRE_INV1",
DECODE(BREINV1.NAMEKEY, NULL,' ',BREINV1.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV1.NAMEKEY, NULL, ' ', DECODE(BREINV1.SEQUENCE,NULL,NULL,

[Code]....

My problem, I have the query which works correctly. However as you can see there is always a sequence number in the end of variables and I am not sure the total number of cases, so I have to create a loop for the same query.

I am doing the same things on BREINV1 BREINV2 BREINV3 BREINV4... AND BRENUMINV1 BRENUMINV2... so the sequence should add a number in the end of variables...

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

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

PL/SQL :: Passing Variables From Shell Script To Separate Sqlplus Script

Apr 5, 2013

I am having issues passing variables to a separate sqlplus script invoked by the shell script, e.g.

#!/bin/sh
DB_NAME=TEST
PWD1=PA55W0rd

echo exit | sqlplus / as sysdba @${DB_NAME}.sql ${DB_NAME} $PWD1 >> ${DB_NAME}.sql

exit 0

The script picks up the $DB_NAME variable fine, and therefore invokes the required sql script. However, when I pass the variable $PWD1 to the sql script I get an error. The script creates a database link:

create or replace procedure new.link
is
begin
execute immediate 'create database link TEST
connect to TESTSCH identified by '$PWD1'
using ''TEST''';
end;
/
exec new.link;
/

output is:

ERROR at line 1: ORA-00911: invalid character ORA-06512: at "NEW.LINK", line 4 ORA-06512: at line 1

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







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