I'm having some trouble dealing with the PL/SQL coding. At the moment, I have a file (set of data) that is read using java. An array is created (line by line) and is passed into a temp. table, which is then called by the plsql (call PKG_xxx.PRC_xxx...) script/command for processing. Is there any way I could directly get the file loaded into plsql (into a temp. table maybe?)
The data are just numbers and letters. I already got the PL/SQL part to select the set I want for different cases. All I need is to get the file (directly or indirectly) for the procedure without going through java.
I have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.
Below is example
TABLE1
ACOL1 ACOL2 ACOL3 A1 A2 A3 B1 B2 B3 C1 C2 C3
TABLE2
BCOL1 BCOL2 BCOL3 11 12 13 21 22 23 31 32 33
COMBINED_TABLE
ACOL1 BCOL2 BCOL3 A1 12 13 B1 22 23 C1 32 33
I tried below query but no luck. It gives below error:
Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum
Error : ORA-01747:"invalid user.table.column, table.column, or column specification"
I would like to create a table in another schema(CBF) as already exist in my schema(TLC) without data but related indexes,synonyms and grants should be include.
How could I do this without using export import. I am using TOAD 9.0.1.
I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?
What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.
Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.
I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table. I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN CREATE GLOBAL TEMPORARY TABLE myAAAA AS (SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777, DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C, [code]........
I need to create a temp table within a stored procedure. I want to apply some logic on the table, and then delete it when it is completed in runtime -- all inside the stored procedure.
In one of the databases, we have created more temp tables as on commit preserve rows. but i want to change the on commit preserve rows to on commit delete rows as per application requirements.I have searched the google but i didnt find any alter scripts for it.?
I have been developing in MS SQL for about 15. So I'm still getting use to the syntax and features within Oracle.I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN CREATE GLOBAL TEMPORARY TABLE myAAAA AS (SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777, DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C, AAAA.1D1D [code].....
We are using a GTT table to store the summarize data and display it on same screen(10g 10.2.0.5). Now we are facing temporary tablespace space issue very frequently as our client do not enable the auto extend on for temp table space. We have analyzed the AWR and came to know that there are 900000 inserts per hour on an average. Client DBA Claims that there are sessions(1or 2) which inserts the data in temporary table continuously for 2-3 days.
According to him one session is running from 28th Aug and problem comes on 2nd Sep and after killing the problematic session the application will work fine. Generally this problems come on weekend. I have discussed with our dev team and as per them there is no session leakage issue.
following is the insert statement: INSERT INTO DT_CA_STNDALN_DETAILS_TMP (ORG_ID,BA_PRODTYPE_ID,MAX TENOR,GROSSLIMIT,GROSS_UTILISATION,HAS_MDR,HAS_CLUSTER,SIGN_IN D,GROSS_AVAILABILITY,COLLATERAL,NET_UTILISATION,DT_CA_STNDALN_DE TAILS_TMP_VER,DM_LSTUPDDT,NET_AVAILABILITY) VALUES (:1,:2,:3,:4, :5,:6,:7,:8,:9,:10,:11,:12,sysdate,:13) ;
What is the need of Temp table in Oracle ? what is the advantage of Temp table over normal table ? Is temp table a log operation one. What is the scenario of using temp table in real time ?
I'm still getting use to the syntax and features within Oracle. I'm trying to create a stored procedure that has two temporary tables within it, and then queries both them tables and inserts the results into a table.I created the script but when they try to run in on the server it wont run.
CREATE OR REPLACE PROCEDURE UpdateFIDB_SP IS BEGIN CREATE GLOBAL TEMPORARY TABLE myAAAA AS (SELECT AAAA.1111, AAAA.2222, BBBB.3333_EXT, CCCC.4444, DDDD.5555, DDDD.6666, DDDD.7777, DDDD.8888, AAAA.9999, EEEE.1010, EEEE.1A1A, EEEE.1B1B, FFFF.3333_LO, FFFF.1C1C, AAAA.1D1D FROM mySchema.FFFF_07 FFFF RIGHT OUTER JOIN mySchema.EEEE EEEE ON FFFF.9999 = EEEE.1B1B RIGHT OUTER JOIN [code].........
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
I have a view, which has a union. (Union is required because of the nature of the data fetched). THis view is later joined with a global temp table which holds the -say employee Id the user selects.
So at runtime there is a join with the global temp table and the view. But the performance is really bad. I have tried using various hints, like materialize, /*+ CARDINALITY(gtmp 1) */ etc.
When i query the view alone,. the performance is good. When I remove the union, the performance is good. Some how with the union- there is a full table scan on one of the joining tables.
We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.
while trying to refresh an materialized view.. oracle throws cannot extend temp table space error.. while starting to refresh mivew temp table space is empty but once refresh started temp tablespace is growing and throws cannot extend temp tablespace error,,,size of temp tablesapce is 200GB..when i monter the session it does an sort event of an table(ammt_pol_ag_comm).. only 4% of this sort event is completing after that it throws error bu occupying the entire 200 GB tabespace.. MView script below..
CREATE materialized VIEW ammv_agent_pol_persis_emas NoLogging Parallel 10 Build Immediate Refresh on demand With Primary Key AS
Is it possible to hold the data from select statement without temp table or materialized view or view in oracle?
because my DBA does not give access to create temp table.but we are selecting the records from 3 different sql statement.
Example: inserting in temp table
a) insert into temp select empno,ename,sal from emp where sal>4000 b) insert into temp select empno,ename,sal from emp where dept=40 c) insert into temp select empno,ename,sal from emp where comm is null