By actually the program are suppose to return the records instead or returning the error. I've included the table that i'm trying to retrieve the records from as well.
TABLE NAME: ========== create table TEST_PREC (NO NUMBER(4,2)); DECLARE BEGIN INSERT INTO TEST_PREC VALUES (12.34); DBMS_OUTPUT.PUT_LINE('the no of records before commit '||SQL%ROWCOUNT); commit; /* What's happening inside commit */
difference between between these two constructs. Finally when i read the asktom.oracle.com , I was totally confused. The reason is thatTom says...we can retrieve more than one row in implicit cursor. If that would be case, what's the difference between these two cursors?? when to use?? My understanding was implicit cursors" ---> single-row queryExplicit cursors ---> multi-row query Experts
DECLARE CURSOR C1 IS SELECT EMPLOYEE_ID FROM EMPLOYEES; TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER; TABLE_EMP_ID EMP_ID_TYPE; BEGIN
[code].....
But when i am executing below code it is showing error as subprogram or cursor 'C1' reference is out of scope
DECLARE CURSOR C1 IS SELECT EMPLOYEE_ID FROM EMPLOYEES; TYPE EMP_ID_TYPE IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER; TABLE_EMP_ID EMP_ID_TYPE; BEGIN
[code].....
The only difference in above two blocks is that EXIT WHEN Statement.
I have written a purge package that would delete records older than 10 years. Since the data is huge, the purging was taking 14 hours plus. To improve performance, I disabled constraints , deleted records and then reanabled them. This was quite quick but the only problem is rollback. Say for some reason if enabling constraints fails there is no way to rollback as enabling and disabling constraints does an implicit rollback.
I'm working on a Java-based web application and we have unit tests that we use to test all our all code that interacts with the database or code that interacts with our DB code. The Spring framework allows us to perform some DML within a transaction before each test and then rollback the changes. For the most part, this works, however when I run the full suite of unit tests, it will randomly commit data to the database causing the rest of the tests to fail.
will Oracle's auditing let me see where this odd-ball commit is occurring? Is there another way for me to see when data is being committed?
This does not appear to be happening on any of the systems we've deployed, however this is a bit unsettling and would like to know why this is occurring so that we can prevent it from happening in production.
In my Project, there are many queries have join conditions between Number and Vrahchar2. Will it give any wrong results or Invalid Number exception even the varchar2 column always contains the number data?
Example: Select * from Table_t1 t1, table_t2 t2 where t1.num_col = t2.var_col -- Here var_col always hold only numaric data.
i write a select statement in proc that contains 44 columns.
when i precompile it. it is showing the error: implicit conversion of string literal to "char *" is deprecated.when i compile the same select with 40 columns it is not showing any error.
but for more than 40 columns (41-44) it is showing the above error.
I'm dealing with an ORA-1000 error in a Pro*C application where all the cursors are correctly closed (or so it seems to me).
Here is the code for a simple program which reproduces the problem:
Each cursor is opened in a PL/SQL package:
CREATE OR REPLACE PACKAGE emp_demo_pkg AS TYPE emp_cur_type IS REF CURSOR; PROCEDURE open_cur(curs IN OUT emp_cur_type, dept_num IN NUMBER); END emp_demo_pkg;
[Code]....
While testing the initialization parameter open_cursors is set to 50.
It's my understanding that Oracle doesn't close the cursors until it needs the space for another cursor, which in my test case seems to happen when I enter a value of 50 or bigger for "number of loops". To see how oracle is reusing the cursors, while the test program is running I run SQL*Plus and query v$sesstat for the session that's running the test with the following sentence:
select name, value from v$sesstat s, v$statname n where s.statistic# = n.statistic# and sid = 7 and name like '%cursor%';
Even before I enter a value for number of loops I can see that the session opened 4 cursors and closed 2 of them:
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 4 opened cursors current 2
Entering a value of 5 for number of loops yields
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 11 <----- 7+ opened cursors current 8 <----- 6+
With a value of 30
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 36 <----- 25+ (apparently, Oracle reused at least 5 cursors) opened cursors current 33 <----- 25+
With a value of 47
NAME VALUE ---------------------------------------------------------------- ---------- opened cursors cumulative 53 <----- 17+ opened cursors current 50 <----- 17+
Now I reached the upper limit set by the initialization parameter open_cursors.
Entering a value of 48, I get the ORA-1000 error.
ORA-01000: maximum open cursors exceeded ORA-06512: at "SCOTT.EMP_DEMO
Since I open and close the cursor in the same loop iteration, I expect to find in every iterarion 1 explicit cursor and a number of implicit cursors (the PL/SQL call along with the so-called recursive cursors), but I don't expect the sum of all of them to be greater than 50. If my understanding is correct Oracle should be reusing the 50 cursors previously marked as "closeable", not raising the ORA-1000 error.
-define a cursor with bind variables -get a cursor record from these cursor -and pass the bind variable in the OPEN clause
Did'nt succeed as shown in the example.
SET SERVEROUTPUT ON SIZE 900000; DECLARE --works fine CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<3; --doesn't work --CURSOR c1 IS SELECT * FROM USER_TABLES WHERE rownum<:1; crec c1%rowtype; BEGIN --works fine OPEN c1; --isn't possible ? --OPEN c1 USING 3;
in my plsql program(procedure) i have queries to fetch data from database. now i want to export those query results to an excel and store on my local machine drive path.
Here i don't want to use directory creation, how can i achieve this.
I have a program that was developed in Forms 6i.Actually, this program:
- reads a table with a Blob column that contains a MS Word format Document. - the document needs to be convert , then the program changes some variables that are wildcards.
In forms 6i I use a column with datatype Ole and the flowing procedure that read value from database:
PROCEDURE Pr_Abre_Contrato (pContrato In Contrato.Cont_Numero%Type, pQuery in Varchar2 ) Is Workbook OLE2.OBJ_TYPE; Application OLE2.OBJ_TYPE; MyDocuments ole2.obj_type; Args ole2.obj_type; vWhere Varchar2(200); [code]....
Now, I need to convert this program to forms10g. I opened the TAR in metalink and the Support said that exec_verb can not be used and it doesn't have another one to replace.
When i am trying to use another RECORD as a NESTED RECORD into another program, it shows the below error: "PLS-00201: identifier 'TIMEREC' must be declared"
Record 1: --------- declare type timerec is record ( seconds smallint); begin dbms_output.put_line('Hello'); end;
Record 2: --------- declare type days is record (day number(5),time timerec); begin dbms_output.put_line('Hello'); end;
I need to generate a report much like a program guide from the script attached. My problem is that my script works, but it's wrong, all the information is wrong. Here's the two queries I came up with;
we have a program that uses SQLPlus to run PLSQL scripts - this program then checks the return code of SQLPlus to determine whether the script ran successfully. Our PLSQL scripts often call other PLSQL scripts via "@@OtherScript.sql" - one problem we have is that if (due to an engineering mistake) the OtherScript.sql file is missing, SQLPlus throws an SP2 error stating that the file was not found. However, the return code of SQLPlus does not indicate any problem. I have found, by searching the internet, that I can simply add a line "whenever OS error exit failure" to our script which then allows our program to detect the failure... however, the error message is not as informative:
CODESQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 19 10:28:45 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [code]....
is it possible to get BOTH types of error messages? With "oserror exit" set it does not tell you which file was not found.. which is useful information. I would like the SQL Plus session to return failure but also print out what file it did not find.
We currently send mail via UTL_SMTP, we could easily switch to SMTP_MAIL but that is beside the point.
Our issue is that we have no way to find out what happens to mail we send to the MTA program, "smtpd". It is of my knowledge that the only information we can receive back from UTL_STMP is if the email address is invalid or if the email is going to be attempted to be forwarded by the MTA "smtpd".
We need the REAL data behind the scenes that "smtpd" is going to get after it attempts to forward an email from the database to GMail for example. We need "account does not exist" errors, and anything else that Google might have to say about us sending that address mail.
As far as I have researched there are no out of the box ways to send mail in this fashion.Could this be as simple as specifying the MX server for GMail? So for an email to a GMail account instead of connecting to localhost:25 for our SMTP server, we should connect to mx.googlemail.com:25?
how to setup receiving bounce messages through the database? We currently receive mail at the database box, and I think we can specify a special clob that will open one of the incoming mail files in RHEL to read for bounces.
I have developed a simple outbound program which creates a CSV file for all the batches uploaded in Oracle. The outbound program has a column named last_run_date
The outbound program checks for the last_update_date of batches should be greater than the last_run_date.
But the outbound program misses some rows randomly. When I run the same cursor individually, it fetches them as no other condition is violated.
I am not really able to debug the issue whether its temp table space issue or anything else.
We are using one application. This application is having 2types of jobs. Under each job again there are each 5 sub jobs. All the jobs are scheduled jobs. Now the requirement is The main 2 jobs we are going to run on some specified days( for this we need to mention on what days we want to run.once we gave those day the next steps will start).
for example 1 main job is scheduled on Monday ( we need to schedule it on monday-- we need to write a program for this) under this every sub job will run.these sub jobs are dependent jobs.if sub job completed successfully it should send an email to the distributed mail group which says like " <Job_name> completed successfully" if it fails also it needs to send an email to the distributed group. for each sub job it should send either success/falure mail to the distriuted mail group.
once after completing all the sub jobs successfully it should send a mail to the distributed mail group saying that The main job got completed successfully.these two main jobs will run paralelly but the the second main job will run after 30 mins of 1st job starts.
in the second job also will work as same as job1 process.it should also send mails to the distributed mail groups.The jobs are already scheduled.No need to scheduled the jobs.need to write a pl/sql program like it will check all the jobs and respected sub jobs if any thing got completed/failed it should send an email to the distributed mail group automatically. For that it should monitor all the jobs..that's the requirement.
I have already scheduled all the jobs. now want to write a script for the above requiement.
I am facing a problem while creating a program using oracle forms.These two table i am using in creating following program .
SQL> desc electricity ; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER NAME VARCHAR2(40) PREV_READING NUMBER(20) PRESENT_REDAING NUMBER(20) TOTAL_UNITS NUMBER(30) AMOUNT NUMBER(30) [code]....
What i want that whenever user writes a value in "PRESENT_Reading" Column then it stores value in "ID table" .For example if current month is July ,Then when user write a value in present Reading column ,it stores it in JULY Column of Id table ..i Write following trigger (Key_commit) On present_reading Column .
if sysdate=to_char(sysdate,'MON','Jan') Then :electricity.PRESENT_REDAING := :ID.Jan ;
elsif sysdate=to_char(sysdate,'MON','FEB') Then :electricity.PRESENT_REDAING := :ID.Feb ;
elsif sysdate=to_char(sysdate,'MON','MAR') Then :electricity.PRESENT_REDAING:= :ID.MAR ;
But when i run my form it does not work ,and raised unhandled Exception .I think i need to change some properties of PRESENT_READING column of electricity table.
I am trying to write which will return cursors to front end program.The logic which I got stuck is while exiting the procedure.I am having some cursors in teh procedures which are read by the front end script.I have one condition which when satisfied I have to exit from the procedure.But the constraint I am facing is if I exit at that logic, oracle closes all the cursor and front end script is not able to read teh cursor and hence throws an error.Is there any way to exit the procedure without closing the REF cursors.
