Background: We have a Webservice that is running under axis2 on a Tomcat. We would like our Oracle (10g Enterprise Edition Release 10.2.0.1.0) db to call our Webservice when a new row is entered into one of our tables. We've discovered we can use a trigger to call a Java Stored Procedure (JSP) or us PL/SQL and the UTL_DBWS utility. Neith...
Initial Question How do you create or replace a role (that might or might not exist) in Oracle? For example, the following does not work: CREATE OR REPLACE ROLE role_name; GRANT SELECT ON SCM1_VIEW_OBJECT_VW TO role_name; Any way to do this without PL/SQL? Update #1 The following does not compile: CREATE OR REPLACE FUNCTION crea...
Hi, I have the following Problem in a PL/SQL Script: I am trying to print an TABLE TYPE for debugging purposes, but don't know how. I tried the following: dbms_output.put_line (V_TEMP_TABTYPE(1)); and dbms_output.put_line (V_TEMP_TABTYPE); which doesn't work (PLS-00306: wrong number or types of arguments in call to). So, how can I pr...
For INSERT, UPDATE and DELETE SQL statements executed directly against the database, most database providers return the count of rows affected. For stored procedures, the number of records affected is always -1. How do we get the number of records affected by a stored procedure? ...
i have this procedure and i have to create an anonymous block to print all the orders at same time. here is the procedure create or replace procedure Item_bill is sSQLstr VARCHAR2(1000); type refcur is ref cursor; rcur refcur; stmt1 VARCHAR2(300); BEGIN sSQLstr := 'select t.n_portions||'' portions of ''||p.dish_name||'' at '...
I'm fairly new to the language, and I was wondering if tail calls were optimized. In other language I could examinate the machine code or an intermediate representation and figure it for myself but I have no idea about how to do that in PL/SQL. Thanks in advance. ...
Hello, I have a performance issue when selecting data in my project. There is a table with 3 columns: "id","time" and "group" The ids are just unique ids as usual. The time is the creation date of the entry. The group is there to cummulate certain entries together. So the table data may look like this: ID | TIME | GROUP -----...
Friends, I am looking to introduce a logging framework into our existing Oracle application to replace the use of DBMS_OUTPUT. The framework will be used primarly to aid debugging and would detail such things as starting x procedure, details of parameters, ending procedure x etc. It should also have the functionality to be turned on ...
Suppose I have a PL/SQL function that selects one value from a table. If the query returns no records, I wish for the NO_DATA_FOUND error to propagate (so that the calling code can catch it), but with a more meaningful error message when SQLERRM is called. Here is an example of what I am trying to accomplish: FUNCTION fetch_customer_id...
I currently have a table(/grid) of data that I can page, filter and sort. On the table I also have a built in checkbox column. Paging, filtering and sorting right now happen within the SQL query. What I want to be able to do is sort by the clicked items in my checkbox column. This would bring all items that are checked to the front o...
The trigger below is delaying my insert response. How can I prevent this? create or replace TRIGGER GETHTTPONINSERT BEFORE INSERT ON TABLENAME FOR EACH ROW Declare -- BEGIN -- The inserted data is transfered via HTTP to a remote location END; EDIT People are telling me to do batch jobs, but I would rather have the data earlier...
I wanted to check my monthly payroll in which there are employee salary along with other detail. I have created a PL/SQL block but when I place my condition for checking of existing employee id with another table some return null value and hence my table does not go further. set serveroutput on declare emp_id NUMBER :=&emp; temp N...
SET SERVEROUTPUT ON DECLARE v_student_id NUMBER := &sv_student_id; v_section_id NUMBER := 89; v_final_grade NUMBER; v_letter_grade CHAR(1); BEGIN SELECT final_grade INTO v_final_grade FROM enrollment WHERE student_id = v_student_id AND section_id = v_section_id; CASE -- outer CASE WHEN v_final_grade IS NULL THEN DBMS_OUTP...
I'm a little bit new to PL/SQL and need something that looks a bit like this: create type base as object ( unused number, member procedure p( c in ref cursor ) ) not final; create type child1 under base ( overriding member procedure p( c in ref cursor ) as t table1%rowtype begin fetch c into t; -- process table1 row...
I have a table defined by: create table apple( A number, B number); Now, I need to get values in the table such as the following: A B ------------------ 1 4(max of A) 2 4(max of A) 3 4(max of A) 4 4(max of A) How can I insert these rows, making B the maximum value of A? ...
Let me be clear - I have more than enough "references" and "for beginners" books. What I want is something like "Exceptional PL/SQL" or "Agile PL/SQL" or "PL/SQL Design Patterns" or even just "PL/SQL For Developers Who Want To Write Pretty Code". Does such a thing exist? (I'm a C++/Ruby/etc. developer. I've been thrown into an Oracle ...
Could you tell what is the problem with this code. It gets error Compilation errors for Error: PL/SQL: ORA-00933: SQL command not properly ended Text: IF iCnt > 0 THEN WITH S600 AS ( Prod_KEY NUMBER; iCount NUMBER; BEGIN WITH TEMP_All AS (SELECT * FROM TE...
create or replace PROCEDURE XXB_RJT_HEADER_PROCEURE ( V_PROD_ID IN NUMBER, V_WARE_ID IN XXB_RJT_HEADER.WAREHOUSE_ID% TYPE, V_PAY_METH IN XXB_RJT_HEADER.PAYMENT_METHOD% TYPE, V_PAY_STAT IN XXB_RJT_HEADER.PAYMENT_STATUS% TYPE, V_ORD_ID IN XXB_RJT_HEADER.ORDER_ID% TYPE, V_ORD_DT IN XXB_RJT_HEADER.ORDER_DATE%...
I have a procedure like this: procedure foo( p_field_name in varchar2, p_record in table%rowtype ) is v_value varchar2( 100 ); begin execute immediate 'select p_record.' || p_field_name || ' from dual' into v_value; end Oracle complains that "p_record"."my_field_name" is an invalid identifier, which leads me to believe that th...
I have a table with some denormalized precalculated columns that are maintained by a trigger. The data sometimes is corrupted and the process fails but in these cases I need just ignore the exceptions and continue because it is not important to catch the error. How can I write the exception clause to just go on without raising any error...