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?
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'.
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:
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...
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.
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
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;
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.
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?
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)
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?
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;
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.
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 ?
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
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;
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?
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;
