Forms :: Create Temporary Objects Through 10g?

Jan 11, 2011

How can be create temporary objects through forms 10g?

In oracle we can create a temporary table so similarly by using forms_ddl can we create a temporary table?

Are there any other temporary objects?

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Create A Temporary Table

Aug 30, 2013

creating a temporary table.i have this query mentioned below

CREATE TABLE WEBPEN AS (SELECT PNSR_PPO_NO PPO,PNSR_FILE_NO,
DECODE(F_GET_APPLN_NO(PNSR_PK),'1',PNSR_VOL_NO,F_GET_APPLN_NO(PNSR_PK)) APPLN_NO,
PNSR_FULL_NAME NAME,
TO_CHAR(PNSR_DOB,'DD/MM/YYYY') DOB,
TO_CHAR(PNSR_DOR,'DD/MM/YYYY') DOR,
F_GET_ADBK_NAME(PNSR_TO_PENSION) TREASURY,
PNSR_SPOUSE_NAME SPOUSE,

[code]....

This creates a table webpen with around 54107 rows. What i am want is every time run "select * from webpen" it should run the above query and give the result as per the values in main table M_PENSIONER ,M_PEN_DCRG_WITHHELD.

What i want is it should truncate the existing values and insert the value by running the above mentioned query .

View 6 Replies View Related

SQL & PL/SQL :: Create A Temporary Table With Same Structure

Mar 13, 2013

create table my_rows
(
my_envvarchar2(100),
anumber(2),
bnumber(2)
)
/
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
insert into my_rows values ('A', 10, 20);
[code]....

The first row means that the value 10 represents 40% in the couple (10,20). Meaning if I have 100 rows with the couple (10,20), 40 rows will be marked with the value 10 and 60 will be marked with the value 20. To do this, I used to create a temporary table with the same structure as the my_rows table with a new column "the_value" and I used to update this new column wth a PL/SQL for loop. But I think it is doable in a signle SQL.

View 9 Replies View Related

PL/SQL :: Create Temporary Table Using Execute Immediate

Jun 11, 2012

How can i create a temporary table using EXECUTE IMMEDIATE ??

Like:

CREATE GLOBAL TEMPORARY table new_table as (Select * from old_table);

View 11 Replies View Related

SQL & PL/SQL :: Create Dictionary Managed Temporary Tablespace In Oracle 10g

Aug 19, 2011

How we can create dictionary managed temporary tablespace in Oracle 10g.

SQL> create temporary tablespace temp
2 tempfile '+GWDAAS04_TEMP_DG01/pimsb_gw/tempfile/temp01.dbf' size 500m
3 extent management dictionary;
create temporary tablespace temp

ERROR at line 1:
ORA-25139: invalid option for CREATE TEMPORARY TABLESPACE

View 7 Replies View Related

Reports & Discoverer :: REP-0118 / Unable To Create A Temporary File In Windows 7

Mar 14, 2012

While Running a Report through form i am getting this below error.

REP-0118 Unable to create a temporary file

But while running the same report in a windows 98 or windowsXp PC i dont get this error.

View 1 Replies View Related

SQL & PL/SQL :: ORA-04089 / Cannot Create Triggers On Objects Owned By SYS

Oct 10, 2011

now i can log in but here i faced with another erorr, while i am creating a trigger it gives the following erorr:

SQL> ed
Wrote file afiedt.buf
1 create or replace trigger trgr
2 before insert on myTable
3 for each row
4 begin
5 select mySequence.nextval into :new.ID from dual;
6* end;
SQL> /
create or replace trigger trgr
*
ERROR at line 1:ORA-04089: cannot create triggers on objects owned by SYS

note: i tried and gave different names for the trigger, still it is the same.

View 22 Replies View Related

SQL & PL/SQL :: Create Two Objects In Different Schemas In A Single Database?

Jul 25, 2010

Is it possible to create two objects in different schemas in a single database.

For example in Schema A, table "T1" is there, is to possible to create a table with the same name "T1" in Schema B.

View 4 Replies View Related

SQL & PL/SQL :: Create Oracle Objects (Views) Using Conditional Compilation?

Jun 6, 2011

it is possible to create Oracle objects(Views) using Conditional compilation?

View 5 Replies View Related

Forms :: How To Insert And Update From Global Temporary Table

May 15, 2013

Global temporary Table Name:

1.MT_GBL
2.DT_GBL
3.DT2_GBL

Base Table Name:

1.MT
2.DT
3.DT2

My Steps:

1.Insert all data from global table to base table.
2.Update all data (that means retrieved all data from base table to global table and update this data). Question: How to Insert and Update from Global temporary table ??

View 4 Replies View Related

Forms :: FRM-40404 Post Command In Save Procedure To Apply Some Record In Database Temporary

Sep 26, 2011

I am working on form 10g, here i am using post command in save procedure to apply some record in database temporary.

But Frm-40404 Message raise during saving record i need post commond but i dont want to this message should popup, is there any method to avoid the message only?

View 7 Replies View Related

Forms :: Frm-18108 / Unable To Load The Objects

May 3, 2010

I use a form (ex. a.fmb) as a reference for another form (ex. b.fmb) . after I subclass the a.fmb into b.fmb and generate it,it works will. but next time I want to open b.fmb I get this error :

frm-18108 unable to load the following objects

source module: A.FMB
SOURCE OBJECT:STD_OBJECT_GROUP
source module: A
SOURCE OBJECT: BUTTON

View 2 Replies View Related

Forms :: Create View Based On Values Selected In Forms

Jun 7, 2013

I am working on a simple form which will get the user to fetch required columns of employee master.

For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.

As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.

To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.

View 3 Replies View Related

Temporary Tablespace Is Empty

May 10, 2011

While I was exporting ( schema level ) ,I am getting following errors :

vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully

'Parfile' contents :

owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log

What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace

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

SQL & PL/SQL :: Insert Into Temporary Table

Jun 5, 2005

I migrate procedures MS SQL Server to Oracle.In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins. For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.

1)INSERT #konfig
exec sp_configure.

2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go

how migrate for example code to Oracle?

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

How To Recover Temporary Tablespace

Aug 8, 2013

Today I found a Temporary tablespace is corrupted, generally speaking, how we can recover a temporary tablespace?

View 3 Replies View Related

Reformat Data In A Temporary Table

Apr 26, 2011

Is there a neat way other than having to reformat the data in a temporary table to do the following,I've got the following content in a table:

CODECustid           Type               Nb                Amount
1                  Deposit           2             10000
1                  Withdrawal        1             4000

I'd like to show the data in this manner:

CustID Deposit  DepositAmount  Withdrawal WithdrawalAmount
1       2         10000            1          4000

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

Querying Records From Temporary Tables

Feb 2, 2011

I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.

However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.

View 3 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 :: Temporary Disable All Constraint In Particular Table?

Jan 7, 2012

i have one table HR.employees

SQL> desc hr.employees
Name Null? Type
----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL>

in this table i want to temporary disable all constraint in this employees table

View 4 Replies View Related

SQL & PL/SQL :: Display Temporary Column Of Two Users

Sep 26, 2011

I have created a query that display the difference in the tables and column of two users. Some of the column in the new user have been added new which were not there in the old user and some columns from the old user still exits but their data_type is changed.

Now I want to select one more column in the select statement specifying whether the column is modified or has been added.

SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ABC' AND
TABLE_NAME NOT IN (SELECT table_name FROM all_tables where owner = 'ABC'
MINUS
SELECT table_name FROM all_tables where owner = 'XYZ' )
MINUS
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'XYZ'
[code]....

View 9 Replies View Related

SQL & PL/SQL :: How To Find Temporary And Default Tablespace

Jun 24, 2010

How can we find the temporary tablespace and default tablespace names.

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

Temporary Tablespace Not Releasing Space?

Jul 13, 2013

We have EBS database with version 11.1.0.7 . Our tablespace is not releasing space ,as the below query show that TEM2 is using only 3 g .  

SELECT   A.tablespace_name tablespace, D.mb_total,         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_freeFROM     v$sort_segment A,         (         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total         FROM     v$tablespace B, v$tempfile C         WHERE    B.ts#= C.ts#         GROUP BY B.name, C.block_size         ) DWHERE    A.tablespace_name = D.nameGROUP by A.tablespace_name,

[Code]....

View 3 Replies View Related

PL/SQL :: How To Free Temporary LOB Returned By A Function

Apr 4, 2013

I have a business need to have a db function that would construct and return a (temporary) CLOB value.

here is its sample code:

create or replace package PKG_TEST_CLOB
as
function FN_TEST_TEMP_CLOB
return clob;

[code]....

when this function is invoked from a SQL Statement...

***
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB from dual;
***

... the NOCACHE_LOBS counter in V$TEMPORARY_LOBS for my session is incremented by 1

when this function is invoked via a PL/SQL block...

***
declare
l_clob clob;
begin
select PKG_TEST_CLOB.FN_TEST_TEMP_CLOB into l_clob from dual;
end;
/
declare

[code]....

... the counter doesn't budge

In real life, this function will be used by a Reporting Tool (cognos) via SQL. I tested it, and it seems that it is allocating a new temp lob segment with every invocation.

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







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