PROCEDURE calc_avg_sales
BEGIN
:sales.avg := :sales.month1 / :sales.total;
EXCEPTION WHEN ZERO_DIVIDE THEN
:sales_avg := 0;
RAISE FORM_TRIGGER_FAILURE;
WHEN OTHERS THEN NULL;
END;
Excerpt from Oracle PL/SQL Programming ISBN 0-596-00381-1 Pg. 106
Why are the variable :sales.avg, :sales.month1, and, :sales.total not declared in the above snippet? Is it presumed that they have been? Why is a : (colon) placed in front of the variables?
Why would you think that FORM_TRIGGER_FAILURE is raised rather than ZERO_DIVIDE?
Within my production environment I have noticed that error messages are sometimes vague to the user and for the technical user, and, would like to make use of an error table which would troubleshoot the cause of the error, without giving unnecessary details to the end user. Ideally this would include details of the Java Exceptions raised (usually large stacks) within applications that form part of the software architecture. How would you go about using an error handling mechanism?
I installed APEX 4.1 with EPG on Oracle linux 6 and for some reason I want to change image prefix from */i/* to */i4/*.
I browsed to apex/utilities and logged in sqlplus as sysdba and run the file:
@reset_image_prefix.sql choosing */i4/* as my new prefix.
then i tried:
select id, flow_image_prefix from apex_040100.wwv_flows; all application now have the prefix /i4/ but when I go to http://myserver:8080/apex nothing is displayed !!!!
when i change */i4/* back to */i/* everything is back to normal...
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=p1; END; ------------ Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS v1 varchar2(100):=p1; BEGIN OPEN p_refcursor FOR select * from Test_tab WHERE item=v1; END;
Have got basic form on a table and have a textarea which holds Notes added by user.
So Notes database field is updated on Save / Apply changes button being pressed.But would really like any text added / appended to the Notes field to be prefixed by userid and date / timestamp.
Is it possible via dynamic actions or Javascript to have any new text added / typed to be auto prefixed as per above.
Would only want the first key press in the filed to trigger the auto-prefix and if added text was deleted then the auto prefix to be deleted as well ?? If user doesn't press Save / Apply changes obviously want to leave existing Notes as is.
im as using oracle 8 with sqltools i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them.for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable.but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like: declare x number; begin set x=45454 select x from SomeTable; end;
CREATE OR REPLACE TRIGGER PPMAPP.PPMCR_HH_CHR_TRG AFTER UPDATE ON PPMCR_STEN.PPMCR_HH_CHARACTERISTICS
[code].....
The cursor HH_ATTR_CSR returns a set of values and I'm iterating each values using a loop, but when comparing the post and pre values, I have to use the variable(HH_ATT_VAR) instead of column names.Usually we give it as (re.XXXX_YYYY) but the cloumn names has to be given in the form of a variable got from the cursor like (re.HH_ATT_VAR).In doing so, I'm getting an error as "bad bind variable" So,Is there any to view the old and the new value in the local?
Below is the code I am facing problem using tablename as variable.
I have five tavble is scheme Emp1,Emp2..Emp5
CREATE OR REPLACE procedure emp_up as tablename1 varchar2(30) ; Begin For x in 1..5 LOOP tablename1 := 'EMP' ||to_char(x); EXECUTE IMMEDIATE 'update '||tablename1 || 'set ename = ''ZZZZZ'' where ename in (''MILLER'')'; END LOOP; End;
The following code is getting the 'Not all Variables bound' error. There are only two variables so I don't see the order being an issue. Assume the integers are assigned elsewhere.
DateTime beginDT = new DateTime(yearInt, monthInt, dayInt, hourInt, minuteInt, secondInt); DateTime endDT = new DateTime(yearInt, monthInt+1, dayInt, hourInt, minuteInt, secondInt);
SQL.Append(" WHERE DATE >= :beginDTParameter "); SQL.Append("AND DATE < :endDTParameter");
I have a tmp table with 6 cols, and data to be inserted is from two other tables. My problem is there the cols of the table are
sid varchar2(10), cob_dt varchar2(10), deal_id varchar2(10), new_val varchar2(10), old_val varchar2(30), amend_col v archar2(50), i have return a proc create or replace
Name Null? Type ----------------------------------------------------- -------- ------------------------------------ C1 NOT NULL VARCHAR2(15) C2 VARCHAR2(254) C3 NOT NULL NUMBER(15) C4 VARCHAR2(254) C5 NOT NULL VARCHAR2(254) C6 NOT NULL NUMBER(15) C7 NOT NULL NUMBER(15) C8 NOT NULL VARCHAR2(254)
[Code]...
But, till yday it was showing the orignal column name..
I am trying to build an array that contains known column names, 63 columns in all.The idea is to search backwards from 63 down to say 10 to find the first non-blank column.
Then using a loop with the known number of lines print from 1 to last non-blank column.The problem is that it only shows the column names and not the values of the columns.So I am getting 63 lines every time, instead of 20, 30, 40 or however how many fields actually have something.
Here is my declare to_num number; field_name varchar2(15); [code]....
Anand basic Anand pascal Juliana cobol Kamala dbase Mary oracle Mary c Partick c++ Qadir assembly Qadir c Ramesh dbase Remitha c Remitha assembly Revathi pascal Revathi basic
PROCEDURE CALCULATE_CASH_REBAL( P_Account_id IN VARCHAR2, P_Txn_Ccy IN VARCHAR2, P_Allocation IN VARCHAR2, l_lty_id IN VARCHAR2 ) IS l_balance_fmt NUMBER := 0; BEGIN [code]....
it should be updating l_balance_fmt field for all the records in the loop. my question is when it loops to the next cash txn record, will it take the previous value of l_balance_fmt ? it should start again and take 0 as the balance_fmt and add to that..
I have written a small code while going through the PL/SQL guide but I got a message for the BIND VARIABLE. I don't think I have used any bind variable in this code.
<<outer>> declare v_sal1 number(7,2) := 60000; v_comm number(7,2) : v_sal1 * 0.20; v_message varchar2(2000) := 'eligible for commission'; begin [code]........
Create and invoke the GET_JOB function to return a job title. a.Create and compile a function called GET_JOB to return a job title. b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION: CREATE OR REPLACE FUNCTION GET_JOB( p_jobid IN jobs.job_id%TYPE) RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35) set serveroutput on DECLARE P_JOBID VARCHAR2(10); v_jobtitle VARCHAR2(200);
I'm trying to execute a procedure within a Declare/Begin/End statement and using variables as input parameters in my procedure but I keep on getting an Invalid SQL Statement Error. My code is below:
declare
START_dt VARCHAR2(30); END_DT VARCHAR2(30);
begin
SELECT '01-APR-2011' INTO END_DT FROM DUAL; [code]....
The table the procedure is pulling data from doesn't have proper date/time stamps but my procedure takes the varchar dates above and turns them into dates in the procedure so the input date parameters are left as just string characters.
I was given a SQL query to develop another query that will need to do something similar. The query I was given I believe is a stored procedure. It is PL/SQL and has a SELECT statement that looks like
SELECT :var1 AS var1 FROM t1 WHERE :var1 = 1
In the past I have seen where variables can be used in the WHERE clause like
SELECT c1 FROM t1 WHERE c1 = :my_column_valueand this makes sense to me.
Usually :my_column_value is declared in the DECLARE block of the PL/SQL. In the query I was given, :my_column_value is not declared in any DECLARE blocks (maybe I wasn't given the entire code??). But even if it was, that would be even more confusing because in the DECLARE block it could be assigned one value and then set to another value in the WHERE clause???
The first query doesn't make sense to me and when I run it as plain SQL I get an error like "SP2-0552: Bind variable "var1" not declared. I am guessing that such a syntax is not valid for regular SQL?
The only thing I can gather that the first query does is something along the lines of
SELECT 1 as var1
so it will output a 1 for every row. Not always that interesting of a query, but it is needed sometimes, I know. But I still don't understand this type of assignment.
So what's going on here? I'd be happy to look it up and read about it myself, but I don't know what to search for.
I am running this query and getting this exception ORA-01008 All variables are not bound .
SELECT EQMT_INGT_LOG_ID, EQMT_ID, XMLSerialize(DOCUMENT XMLType(ingLog.BUCK_SLIP_XML) AS CLOB) BUCK_SLIP_XML FROM TOS_EQMT_INGT_LOG ingLog where BUCK_SLIP_XML is not null and ingt_date between to_date(:fromDate, 'MM/DD/YYYY HH24:MI')
----- SELECT SUM(s1.PRODUCT_QTY) INTO anz FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3 WHERE s1.SALES_ORDER_ID = s2.SALES_ORDER_ID AND s2.REGION = 'ANZ' AND s1.GCM_OPP_ID = s2.GCM_OPP_ID
[Code]..
The statement sums up all the products attributed to the region ANZ and stores it in a NUMBER variable called anz.
The issue is that I have multiple regions: ANZ, JP, ASEAN etc. Wat I would do is to create a statement for each and every region. Is there a way to consolidate them all into a single statement. that means something like
----- SELECT SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY),SUM(s1.PRODUCT_QTY) INTO anz, jp, asean FROM EXACTS_TRANSACTION_HAS_PDTS s1, EXACTS_TRANSACTIONS s2, EXACTS_PRODUCTS s3 WHERE .................. -----
