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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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()';
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.
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
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
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.
