I have a package with pipelined function in it. using the below stmt to execute the function in Oracle 11g.
select * FROM table(Test_PKG.Test_PIPELINED ('A','--N/A--','1/1/2010','1/1/2011'));
Throwing an error: invalid month error.
CREATE TYPE WBS_ROW IS OBJECT
(
Product VARCHAR2 (100),
DESC VARCHAR (1000),
RecDATE DATE);
[code]....
I have this requirement, I have a following Record type within a package, instead of selecting datas into this i need to assign variables to this record type
TYPE xx_delivery_detail_rectype IS RECORD ( p_delivery_id NUMBER, p_ultimate_dropoff_location_id NUMBER, p_creation_date DATE, p_last_update_date DATE, p_container_name VARCHAR2 (30), p_inventory_item_id NUMBER, p_shipped_quantity NUMBER, p_shipment_line_id NUMBER ); TYPE xx_delivery_table_rectype IS TABLE OF xx_delivery_detail_rectype INDEX BY BINARY_INTEGER;
Say i have variables to assign to all the values within the record type, How do i do it within the package
I have huge package having numerous procedures. Therein there is one wrapper procedure which is invoked from front end and which in turn calls other stored procedures within the package. The stored procedures called return the result to the wrapper procedure which in turn calls other stored procedures.
The ultimate result is returned to the calling environment by the wrapper procedure itself.I need to know the time taken in totality by the wrapper procedure along with the individual execution time of each procedure called by the wrapper procedure. I am not allowed to modify the code for adding timestamp capturing though.
When i am trying to execute the below in sql. i am getting the error.
create or replace type sum_n as object (
nodes node_d,
constructor function sum_n return self as result,
member procedure do_s (m date,exd varchar)
);
/
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/9 PLS-00201: identifier 'NODE_d' must be declared
I have a trigger that is called from an update on the table, this trigger performs the procedure and this procedure update the Same record in the table That shot the trigger. this situation returns error ORA-0060 - DEADLOCK DETECTED WHILE WAITING FOR RESOURCE. Is there any way that this works?
View 1 Replies View RelatedI am learning pl/sql and this is procedure i had created which got created successfully.then i created two variables name v_name, v_loc with command.
variable v_name varchar2; etc.
and executed procedure like
execute dept_proc2(10, :v_name, :v_loc);
and i am getting the error
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
1 create or replace procedure dept_proc2
2 (v_dno in number, v_name out varchar2, v_loc out varchar2)
3 is
4 cursor dept_cur is select dname, loc from dept
5 where deptno=v_dno;
[code]...
I created one Update or Insert trigger on a table.When I executing the update statement it is giving error
Ora-04091 : Table gtest is mutating, trigger/function may not see it.
ORA-04088: error during execution of trigger test_email_change
ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
Action: Rewrite the trigger (or function) so it does not read that table.
I create this trigger:
CREATE OR REPLACE TRIGGER T_HST AFTER INSERT
On HST
FOR EACH ROW
DECLARE
Begin
INSERT INTO HST (EMP_NO, START_DATE )
SELECT EMP_NO, SYSDATE
FROM HST
WHERE EMP_NO = :NEW.EMP_NO;
End T_HRS_SAL_PERIOD_HSTY_UPD_PRI;
/
when I insert a new record in table HST I get this error:
ORA-04091: table HST is mutating, trigger/function may not see it
ORA-06512: at "T_HST", line 3
ORA-04088: error during execution of trigger 'T_HST'
What I wrong in this trigger?How can I modify it?
i create this trigger to lock inserting transaction for any data greater than 30-04-2013 on a certain table
CREATE OR REPLACE TRIGGER INVALID_DATE_VALUE BEFORE INSERT or UPDATE on ra_cust
FOR EACH ROW
BEGIN
IF :NEW.TRX_DATE >'30-APR-2013'
THEN
-- RAISE_APPLICATION_ERROR('ora-0000','DATE CANNOT FALL IN RANGE...Invalid Date');
RAISE_APPLICATION_ERROR(-20000, 'IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN ''30-04-2013''');
END IF;
END;
and this trigger created successfully
when i try to insert data in the table ra_cust even in a range less than 30-apr-2013
i got this error
ORA-20000: IT IS NOT ALLOWED TO INSET DATE VALUE GREATER THEN '30-04-2013'
ORA-06512: at "ARASK_HAGAR.INVALID_DATE_VALUE", line 5
ORA-04088: error during execution of trigger 'ARASK_HAGAR.INVALID_DATE_VALUE'
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)
Environment is CentOS 6.3, 64-bit OS
I get the following error when I use the universal installer (runInstaller.sh)
OUI-10150: Error: A runtime execution error occured while setting s_digcfgnaminglabel isidnull in compnent oracle database 11g 11.2.0.1.0. Installation cannot continue for this component.
I have check my /etc/hosts entries and I believe my jre paths are correct since I get the JAVA windows.
I have a procedure which i wrapped using the oracle 11g wrap utility. If i execute the wrapped procedure using jdbc i am getting an error of 0RA-00900 invalid sql statement.
The procedure is having basic sql statements only.The same procedure if i wrap using Oracle 9i and execute using jdbc it works fine.Is there any change in Oracle 9i wrap utility and Oracle 11g wrap utility.
I tried even Oracle 10g wrap it is also not working fine.
How to see the error Oracle is returning?
SQL> select owner, object_name, object_type from sys.dba_objects
2 where status = 'INVALID' order by owner;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
CRMAPP
CRM
PACKAGE BODY
SQL> alter package CRMAPP.CRM compile;
Warning: Package altered with compilation errors.
SQL> show errors;
No errors.
SQL> select owner, object_name, object_type from sys.dba_objects
2 where status = 'INVALID' order by owner;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
CRMAPP
CRM
PACKAGE BODY
when am trying to compile package, am getting the below error am not understanding whats that exactly ,
PLS-00103: Encountered the symbol "FUNCTION" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
way give error in package below
SQL> CREATE OR REPLACE PACKAGE DATA_BKG AS
2
3 TYPE OBJ_DEPT IS RECORD
4 (
5 DEPT_ID NUMBER(10),
[code]...
I am getting the below error in a Stored Package.
SQL> BEGIN
2 NCOTE.OTEGENERATOR;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "GENEVA_ADMIN.NCOTE", line 3
ORA-06512: at line 2
I must be trying to do a select or put some other value into a variable that is too small. The Odd thing is that when I run the stored procedure again (as long as I don't exit from sqlplus)...
SQL> BEGIN
2 NCOTE.OTEGENERATOR;
3 END;
4 /
PL/SQL procedure successfully completed.
why the process is successful the second (or third, etc) call from the sqlplus but not the first. If I exit sqlplus and then start it again, once more I get the error again.
When i compile the package body i get errors.
CREATE OR REPLACE PACKAGE BODY CEE_OSPCM_PKG AS
PROCEDURE CEEOT_WORK_TYPE_PRC IS
CURSOR CUR_WORK_TYPE IS
SELECT *
[code]...
When trying to create package I receive this error...[Error] PLS-00103 (8: 16): PLS-00103: Encountered the symbol "." when expecting one of the following:;
pointing to "." in the last line "END nondrg_prod.Readmissions;"
CREATE or replace PACKAGE nondrg_prod.Readmissions AS
TYPE Readmits IS REF CURSOR RETURN READMISSIONS_DATA_MH%ROWTYPE;
PROCEDURE open_rdmh (rdmh IN OUT rdmhCurTyp);
END Readmissions;
[code]....
SQL> conn system/sys@ORCL;
Connected.
SQL> @F:oracleproduct10.2.0client_5RDBMSADMINutlmail.sql
Package created.
Synonym created.
SQL> @F:oracleproduct10.2.0client_5RDBMSADMINprvtmail.plb
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY UTL_MAIL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
319/5 PL/SQL: SQL Statement ignored
320/10 PL/SQL: ORA-00942: table or view does not exist
325/7 PL/SQL: SQL Statement ignored
326/12 PL/SQL: ORA-00942: table or view does not exist
while compiling this package body, i get error
error 102 at line 21 column 11
encounterd symbol "Group" when expecting one of following
begin function package pragma procedure subtype type use form cursor
I am having a problem when calculating dates in a package usiong Toad. I have my function :
FUNCTION PORTFOLIO_MEMO_RPT_Sql( schema_name IN VARCHAR2 , select_business_date IN DATE ) RETURN VARCHAR2.....SELECT DISTINCT Case when NVL(TO_DATE(''09/24/2013'',''MM/DD/YYYY'') - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0 then ''0Days'' when NVL(''' || select_business_date || ''' - DECODE(mn.Active, 1, mn.DueDate, sc.DueDate ),0) = 0
then ..... when I give it my variable from outside , it is not working......but when I use a date inside, it works. I call my function from outside like :
SELECT * FROM TABLE ( FI_rpt.Fi_Rpt_Pkg_loan.PORTFOLIO_MEMO_RPT (sys_context('userenv','session_user'),TO_DATE('09/24/2013','MM/DD/YYYY')) )
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;
It's been some time since I've written any PL/SQL, I'm getting Found 'CURSOR' Expecting: External Language for line 76 when I try to compile? :
1 create or replace PACKAGE pa_user_maint
2 AS
3
4
5 -- ------------------------------------------------------------------------------------
6 -- Exceptions
7 -- ------------------------------------------------------------------------------------
8
9 -- User Exists
10
11 ex_existingUser exception;
12 PRAGMA EXCEPTION_INIT (ex_existingUser, -01920);
13
[code].......
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
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;
but could not get it to work.
consider the trigger below,
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;
Error : Identifier EMP must be declare
what is use of cursor variables?
View 1 Replies View RelatedThe 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");
OracleCommand cmd = connection.CreateCommand();
cmd.Parameters.Add(new OracleParameter("beginDTParameter", OracleType.DateTime)).Value = beginDT;
cmd.Parameters.Add(new OracleParameter("endDTParameter", OracleType.DateTime)).Value = endDT;
