PL/SQL :: Rows Not Fetched In Outbound Program
Dec 17, 2012
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.
View 1 Replies
ADVERTISEMENT
Aug 12, 2010
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
View 6 Replies
View Related
Sep 16, 2010
May I have a PL/SQL program that can update 1000 rows.
View 4 Replies
View Related
Sep 28, 2007
I want to copy some rows in one column from Excel and Paste it in a Oracle Forms Progam. Normaly with the clipboard. But my Oracle Forms 6i Program copy alle the rows in one Field, separated with a | character. But I need it in several rows.
View 12 Replies
View Related
Jan 28, 2011
I have a Cobol program that call C program above
#include stdlib
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char *uid = "puntos/puntos@cmrdesar";
[code]....
Are something wrong in C programm ? Pro*C code from vouters.dyndns.org/tima/OpenVMS-Cobol-C-Cobol_ passing_ variable_ number_ of_ arguments_to_C.html
View 1 Replies
View Related
Jun 25, 2013
I have a select query select userid from user_tbl where name='hhh';
I want to return 0 when no records are fetched and I want to handle both teh cases in a single query other than proc or function.
View 12 Replies
View Related
Apr 25, 2012
Just want to confirm, that where Oracle stores data fetched from inline view.
I am asking, as I am making an inline view on a very large table and its getting failed with TEMP space error.
View 3 Replies
View Related
May 4, 2012
I need to insert values fetched from an object type into an empty table.
-- >> table creation:
create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));
-- >> created an object_type
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created
--- >> create the package
SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /
Package created
SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:
[code]....
View 6 Replies
View Related
Apr 26, 2012
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.
View 2 Replies
View Related
Dec 6, 2006
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.
View 26 Replies
View Related
Oct 20, 2010
I have forms 10g and use oracle database11. I have a form and I need to execute a program x.bat in another PC.
View 2 Replies
View Related
Aug 18, 2010
I've tried to use implicit cursor in my pl/sql program and i get an error as following:
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 17
Then,I tried to include an exception for it and the error is 'solved'.
DECLARE
d_call_id course.call_id%TYPE;
d_course_name course.course_name%TYPE;
d_term_desc term.term_desc%TYPE ;
d_f_first faculty.f_first%TYPE;
d_f_last faculty.f_last%TYPE;
d_day course_section.c_sec_day%TYPE;
d_time course_section.c_sec_time%TYPE;
d_bldg_code location.bldg_code%TYPE;
d_room location.room%TYPE;
[code]....
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.
View 4 Replies
View Related
Jan 23, 2011
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;
View 5 Replies
View Related
Jun 14, 2012
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;
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
And
SELECT CHANNELS.channel_NO,
CHANNELS.CHANNEL_NAME,
PROGRAM_PACKAGES.PACKAGE_NAME,
SUPPLIER_LISTING.PROGRAM_NAME,
[code]...
View 9 Replies
View Related
Oct 19, 2010
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.
View 4 Replies
View Related
Mar 3, 2011
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.
View 7 Replies
View Related
Feb 19, 2013
When I am executing below code it is working fine
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.
View 2 Replies
View Related
Jun 26, 2011
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.
View 3 Replies
View Related
Jul 19, 2010
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.
View 7 Replies
View Related
Mar 14, 2013
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.
View 3 Replies
View Related
Oct 26, 2010
I've written a chain with four programs. The third program is depend on the output of the second. However the third program finishes before the second. How do I wait for the second to finish.
DBMS_SCHEDULER.define_chain_rule
(chain_name => 'SATURN.SHRROLL_CHAIN',
condition => 'TRUE',
action => 'START SHRROLL_STEP',
rule_name => 'RULE_100',
comments => 'START GRADE ROLL');
[code]...
View 2 Replies
View Related
Jan 13, 2012
I want plsql code for output as follows
menu
enter 1 for veg
enter 2 for non-veg
enter value:
after entering the number it have to show different items like
item1 cost $10
item2 cost $12
.
.
.
i tried this with case with substitution variables. it is asking variable first with out showing the menu. i want menu first then it should ask for entry. what can i do for this.
View 1 Replies
View Related
Aug 11, 2008
How Do I Create An Interactive Program That Will Add Numbers Between Two Given Digits Using Sql. For Example:
Enter Start Num: 0
Enter End Num: 15
Then It Does 1+2+3+...........+14
View 2 Replies
View Related
Jan 10, 2012
I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table. The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens. I had the same problem with a Python program, and I thought that the problem was from Python, but now with Visual Basic the problem remains, both aren't executed.
The strange thing is that when I call the same Java class outside Oracle, directly from a command line, it executes both Python and Visual Basic programs.
Here is the Java class defined in Oracle:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static void Run(){
try
{
[code]....
And here is the procedure that calls the Java class:
create or replace
procedure run_os_command
as language java
name 'OSCommand.Run()';
View 1 Replies
View Related
Feb 27, 2012
I have a database in which there are lots of programs running by default user or programs started by some oracle users.
Now, I know that the USER tablespace is growing at very high speed - But I dont know which user/program is causing this growth of USER tablespace.
how to identify the program/user who is responsible for this growth of USERS tablespace.
View 1 Replies
View Related
Jun 19, 2011
At PLSQL level how can we make our program re-startable such that if there is some abort after the commited update program will restart from the last commit checkpoint.
View 2 Replies
View Related
May 3, 2011
We have a query which uses start by and connect with method. this query works fine in our earlier version 10. But when we migrated to 11g, we are facing problem with the query.
Scenario 1:
------------
There is a table in Schema 1 and created a view for the same.
Table : create table alias_test1 ( A varchar2(10),B varchar(10),C varchar2(20),D varchar2(40), E varchar2(10),F number(1));
View: create or replace view alias_vw1
as select A, B,C,D, E,F from alias_test1
Values :
Insert into A.ALIAS_TEST1 (A, B, C, D, E, F)
Values ('Block1', '136', 'TOTBANK', 'Total Bank', ' ', 1);
Insert into A.ALIAS_TEST1 (A, B, C, D, E, F)
Values ('Block2', '136', 'PPRSEGHKB', 'HKB', 'TOTBANK', 2);
Now in the schema B, created a synonym for the view to access. create synonym alias_vw1 for alias_vw1@link.world
Query :
select srl_no,cde,pdesc,lvl,is_leaf,hcy
from( select srl_no,
cde, pdesc, level lvl,
connect_by_isleaf is_leaf,
Ltrim(replace(sys_connect_by_path (rpad(Trim(cde),19,' '),'~'),'~',' ')) hcy
[code]...
View 3 Replies
View Related
Jul 13, 2010
I need to open an excel sheet from PL/SQL program where I will put in the data. How to open an excel sheet from Pl/sql code.I got hold of a code as below Looks like I need to configure DAD PL/SQL
BEGIN
-- Prepare Headers
owa_util.mime_header('application/vnd.ms-excel',FALSE);
--htp.p('Content-Length: '||document_length||NL_CHAR);
--htp.p('Content-Length: '||document_length);
[Code]....
View 4 Replies
View Related
Sep 21, 2011
need to write a program for date of birth is always less than sysdate, using exception handling
View 10 Replies
View Related
Jan 2, 2013
I am facing below Oracle error when ever I am changing the contents of the package within it. %ORA-06508: PL/SQL: could not find program unit being called:
The packages and the dependents are recopmiled successfully and there are no invalid objects.The problem is resolved only if the database is reastarted.
What might be the problem?I am using a PL/SQL collection of record datatype in this package.
View 13 Replies
View Related
