Get One Row For A Student (academic Program) - Effective Dated Error?
Jun 6, 2013
I have a query where I'm trying to get one row for a student that is their academic program. A view PS_NTSR_PRGPLN_VW returns all active programs. This student has three.The query is pretty straightforward but I'm not getting any rows, and I think it is because I'n not using MAX(pe.effdt) correctly.
select * from PS_NTSR_PRGPLN_VW h
where h.emplid = 'xxxxxx'
AND h.institution = 'ABCD'
AND h.acad_plan_type = 'MAJ'
AND h.primary_indicator = 'Y'
[code]....
I've had rows where I have the same plan_sequence so I do need to use MAX(pe.effdt), but when I do I get zero results.
View 1 Replies
ADVERTISEMENT
Oct 22, 2012
i have a requirement, wherein i have to update records in such a way that each student's marks are copied in a new column of the next student record.
View 10 Replies
View Related
Sep 18, 2013
need to build an application for registering the teaching hours of academic staff based on faculty's Time TableWhat is the best plugin for doing that?
For example, is this the best from your point of view - Region Plugin - SkillBuilders Calendaror the calendar region of apex can do that I need ability of adding lectures with their detail in flexible
View 0 Replies
View Related
Apr 9, 2013
I need the effective dates (start and end) of marital status changes in sequential order, without duplicate rows over the same time frame. (Per_all_people_f table only). For example below, I only need the items that are in bold. I am very new to pl/sql and cannot figure out how to do this.
When I do this in sql with Min date and max date; the 1st and 2nd blocks are correct, the 3rd block has wrong end date and 4th block is entirely missing as the 'M' is already counted for in block 1 even though it occurred after other status changes.
Example of the rows and what I need in BOLD below:
So no gaps in time and it captures the effective date range for that particular marital status; I need to get:
1st block 'S' 10/23/2000 - 4/12/2004
2nd block 'M' 4/13/2004 - 10/1/2006
3rd block 'D' 10/2/2006 - 5/23/2007
4th block 'M' 5/24/2007 - 12/31/4712
Actual data in table I do get on a query with no restrictions:
490 *10/23/2000* *4/12/2004* 0 US S F
490 *4/13/2004* *10/1/2006* 0 US M F
490 *10/2/2006* 2/12/2007 0 US D F
490 2/13/2007 *5/23/2007* 0 US D F
490 *5/24/2007* 10/7/2010 0 US M F
490 10/8/2010 11/15/2012 0 US M F
490 11/16/2012 *12/31/4712* 0 US M F
View 4 Replies
View Related
Oct 26, 2013
1) First Table
Id , Name , Eff_date
10 I1 15-APR-2013
20 I2 30-APR-2013
30 I3 26-May-2013
20 I4 10-SEP-2013
40 I5 10-sep-2013
40 I6 10-Oct-2013
2) Second Table
Eff date Rate
15-APR-2013 900
30-APR-2013 500
16-Sep-2013 400
05-Oct-2013 200
Q. How to get department wise effective rate?
View 3 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
May 30, 2010
When I run my package in TOAD and SQL plus I am not getting any errors. My package is being compiled well. When I integrate my Package through WED ADI .I am getting the above error. I am new to oracle apps WEDADI. can any give your valuable sugessions. I have even bounched the apache two times.
The schema for my package is APPS. Can you tell us how to go check the schema of WEB ADI. We see my integrator created in BNE_INTEGRATORS_TL. Since it is store in BNE table, the schema for BNE table is 'BNE'?
How to provide the access of package to the WEB ADI?
View 1 Replies
View Related
Jun 27, 2013
java.sql.SQLException: ORA-04054: database link GMAIL.COM does not exist
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:946)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1169)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1615)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1580)
at com.jdbc.pack.Lab1.main(Lab1.java:31)
View 7 Replies
View Related
Jun 30, 2011
my java program i am executing a pl/sql function using callable statement i checked everything in the function and the function is created without compilation errors in sql*plus environment, and i gave all DBA permissions to the user, but i got the error like below
java.sql.SQLException: ORA-06550: line 1, column 28: PLS-00201: identifier 'NAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
and i wrote the function like below for the below table
create table student(regno number(6),name varchar2(15),dob date,phone number(10),
address varchar2(30),cat varchar2(2),password varchar2(12),hallno number(6),
fee_paid varchar2(3),constraint student_pk primary key(regno))
create table fee(regno number(6), branch varchar2(15),amount number(4), ddno number(12), fee_paid varchar2(3),
dddate date, dd_received_date date default sysdate, constraint fee_pk primary key(regno))
[code]...
and the java program is like below
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection=DriverManager.getConnection(dbURL,dbUserName,dbPassword);
CallableStatement st=connection.prepareCall("{?=call
[code]...
View 5 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
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
Sep 16, 2010
May I have a PL/SQL program that can update 1000 rows.
View 4 Replies
View Related
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
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
