SQL & PL/SQL :: Code Not Running After IF Statement?
Feb 6, 2012
with the code below i have created 2 varaibles v_start_date and v_finish_date, i have inputs for them, thats fine the problem is when the inputs are empty, i have put a ELSIF statement in the begin section of the code, now if the variables have input from the textbox's then it works fine but if they are empty, the programme reads into the if statement executes that bit of code but doesn't seem to open the cursor up and execute that bit of code.
DECLARE
v_start_date enrollments.enrollment_date%TYPE:= :START_DATE;
v_finish_date enrollments.enrollment_date%TYPE := :FINISH_DATE;
CURSOR no_grades_cur IS
SELECT class_id,stu_id, status
FROM enrollments
running a jquery code after submit button has successfully completed, how do i integrate these 2 events ? clicking the submit button and if submits successfully execute the small jquery code based on the same submit button
I am getting ORA-00933 after running below mentioned delete statement;
DELETE FROM REPOSITORY.MEDIASEGMENT MS INNER JOIN REPOSITORY.ROUTINGEVENT RE ON TRIM(MS.Segment_Key) = TRIM(RE.uuid) INNER JOIN REPOSITORY.TEMPCONTACT TC ON TRIM(RE.Contact_Key) = TRIM(TC.vduid) WHERE TC.CREATETIME BETWEEN (TO_DATE('04/24/2008 00:00:00','MM/DD/YYYY, HH24:MI:SS')) AND (TO_DATE('04/30/2008 23:59:00','MM/DD/YYYY, HH24:MI:SS'))
After Migrating from 11.1 to 11.2 SQL Statements are running for a long time. Our DBA told that they don't have any execution plans for the SQL Statements in 11.1
I don't want to rewrite the SQl Statement as this is working fine in 11.1. Our DBA conveyed us that they are using DBMS_STATS.AUTO_SAMPLE_SIZE for Schema Level.
But we have planned to generate DBMS_STATS.GATHER_TABLE_STATS Estimate Percentage ==> 100 for only the tables that are used in SQl Statements.
Of course we can change the parameters.. But we are afraid it may have some side effects.
I ran one long running update statement in my sqlplus session, where it took hours together since we have millions of rows on the table.lock the system and left the office , the next day I found my system is rebooted, hope the session would have performed the long running update statement.
Now, I need to issue a commit for the session, since my system is rebooted I don't have opened session with me. so, How to issue commit to the session, which I have issued long running update statement.
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE EXCEPTIONS --STRSQL := ''; --STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i); -- EXECUTE IMMEDIATE STRSQL; INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement commit; --dbms_output.put_line(unrecondata(2).TRANSID); EXCEPTION
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') 2 || ' User '||s1.username || '@' || s1.machine 3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text ||' is blocking the SQL statement on '|| s2.username || '@' 4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> ' 6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 , 7 v$session s2,v$sql sqlt1, v$sql sqlt2 8 WHERE s1.sid =l1.sid 9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1 10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2; [code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
I need to retrieve the records where the involvement is either a person or an organization. Using the code below i receive an error stating that the outer join operator is not allowed in the operand OR:
(( INVOL1.PERSON_IDENTIFIER(+)=ALL_PERSONS.IDENTIFIER ) OR ( INVOL1.OUNIT_IDENTIFIER_INV=INVOL1_ORG.IDENTIFIER(+) ))
If I change it to an and query it will only retrieve the records where there is a person and an organisation as the involvement but I need this option as well as one or the other.
I would like to backup all source code (packages, procedures, functions, etc) every day automatically. Is there a built-in feature of Oracle to do this, I've been going through the Toad IDE and its getting to be too time consuming.
I assume I could use the export command, but not entirely sure what to specify.
I would like to backup a few tables along all code and set something in place (trigger maybe?) to send me an email when a modification is made to the production code environment.
I am fighting with some code to work in IE7. It is currently working in Firefox.
A little bit more on the context of the situation is that I am entering the code as a personalization on an Oracle Self Service Page. I am entering in as a raw text personalization.
My Asp.Net/C# application is connected to an Oracle database where it retrieves database values and also values calculated/returned from Oracle functions. My problem is that sometimes an Oracle function I calling returns a null value because of not correct input parameters which will cause Oracle function to (I think) return an null value. My problem and question is how to catch/obtain if he Oracle function returns a null value? Oracle function should return a decimal value if correct input parameters. Below is my row of code that crashes when Oracle function returns a null value in case of incorrect input parameters.
I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST ( IDx number, STARTx number, DURATION number, RESULT number ); insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
this is the table i have in which repeated itemids for different dates,for example itemid 205 and 203 comes 2 times because that item was consumed again and again in a different date. like this the table data has more than a 20 thousand records;
In which i need to select the last updated dates itemid from this table, because same item id could be consumed 'n' number of times so i need to select it as per the last date or latest dates itemid from this table for each and every item, so how can i select it?
I have a problem in running a sql query.I have a dataset with the following details. Product name,product status,approval date in table product_details. I have a code as follows
select Product_name, Product_status, approval_date, case when product_status ='Cancelled' or product_status ='Stopped' then approval_date='N/A' when product_status='Active' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null') when product_status='Completed' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null1') when product_status='Planned' then NVL2(approval_date,cast(appoval_date as nvarchar2(30)),'Null2') end as DER_approval_date
from product_details
but i have a error in running this code saying character mismatch.
There are two List boxes namely :list_all (All) and :list_sel(Selected). :list_all show some data. What I want is to insert / delete records from the list_all to list_sel or vice versa using the buttons ( e.g.For one record using > and for all >>). The next step is to call a report to show data from the :list_sel.
I have populated the list_all with using the following query.
"select distinct all_ded,to_char(all_ded_id) from salary_temp order by all_ded"
This Is My Code ------------------- Declare obutton number; begin
obutton := Show_alert('ALT_EXIT'); clear_block(no_validate); If obutton = ALERT_BUTTON1 Then -- commit; exit_form(no_validate); else exit_form(no_validate); end if; end; -------------------- But When I pressed Yes Or No the database is not update. Why ? I have save My data with save button already no problem but why i close this form The data is'nt save?
So i have to put "summit;" in it when ALT_EXIT pass another warning* appear how do i exit program without the second warning
*it say about"2 record i already updated" about this but i can't remember exactly.