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 .
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.
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.
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 ??
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?
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 :
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.
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
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.
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
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 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.
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]....
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.
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