SQL & PL/SQL :: ORA-28827 - Invalid Cipher Type Passed?
Jan 7, 2011
I keep on getting the Invalid cipher error whenever I do a call to DBMS_CRYPTO.Decrypt The flavor I am calling is the one taking the parameter list below:
(dst in out Blob,src in Blob,typ in pls_integer, key in raw,iv in raw)
So the issue happens on the third parameter: typ. It is a constant defined in the package representing the decryption algorithm I intend to use (AES256 in my case). So the call goes something like that in my pl/sql
create or replace type type_sh_fld_rec ( s_count count, s_name varchar2(200), e_date date); / create or replace type t_table_s_field as table of type_sh_fld_rec; /
I have used these types in package pkg_shippers PROCEDURE process_shipments(userid);
when I run the following sql
select * from table(cast(DEV.PKG_SHIPPERS.process_shipments(324) AS T_TABLE_S_FIELD));
I get the following error.
ORA-00902 Invalid datatype.
I have tried to create the types and table from the sql prompt instead of the package spec, still no luck
Note: The sql statement works fine if I run it as schema owner but not as user who has all the privileges.
ORA-31180: DOM Type mismatch in invalid PL/SQL DOM handle
I am getting this error while debugging one of the package in PL/SQL developer or in TOAD. Is there any setting which we need to perform at the database level to debug the packages which have the code related to XML DOM. I am asking as the same code works exactly fine at one of the test database and it gives the above error on the other database.
Is it possible to pass a nested select statement as a parameter to a procedure? In other words, I am currently successfully doing this:
SELECT BASE_PAY INTO N FROM EMPLOYEES WHERE ID=p_ID; SetPackageValueProc(PkgID,'YearlyBasePay',N); And I'd like to do something along the lines of this: SetPackageValueProc(PkgID,'YearlyBasePay',(SELECT BASE_PAY FROM EMPLOYEES WHERE ID=p_ID));
I have used ref cursor to retrun output in cursor from procedure.I have used bind variables in the v_query and passing values with using clause as given in the following code. It is working but client want to pass values dynamically.
OPEN p_cur FOR v_query USING p_ht_nm, p_ht_treat_source, var_cycle (1), var_cycle (2), var_cycle (3), var_cycle (4), var_cycle (5), var_cycle (6), var_cycle (7),
[code]...
We can also use execute immediate with the above code. But in both the cases we have to pass values in using clause.
QUERY: /* Formatted on 2011/09/24 21:13 (Formatter Plus v4.8. */ INSERT INTO z_ca_get_lot_id SELECT DISTINCT attrbts.lot_id FROM (SELECT DISTINCT lot_id FROM z_alv_cert_lot_attrbts_syn WHERE NAME = 'Heat' AND UPPER (text_value) = :p_ht_nm) attrbts,
I have developed a RDF report which when linked to my application called OTM, it returns data which are not expected.
My report has two lexical parameters which are of date variables.
When i run the report from my application it asks for the parameter valeus and when i specify it, the report is not returnign the data within the date range i specified. it returns me all the data.
I have a requirement to extract data from an XML formatted string that is passed as an IN parameter to a database stored procedure. I do not have a table to store the XML, nor an XML schema, nor a namespace, just the XML string. While new to XML, I do understand the basics. Using XMLTable I am able to parse out the simplest of XML data. My problem is this; attempting to extract data from nested XML nodes results in the error
Testing a solution example I found on this site by Barbara Boehmer, I still get the ORA-19279 error. I suspect it may be because I have version 10g while she has version 11g. Her solutions is from #msg_514154. Here is that code that gives me the error:
SCOTT@orcl_11gR2> SELECT x."deptno" 2 FROM (SELECT '<departments><deptno>1</deptno><deptno>2</deptno></departments>' col FROM dual) xml_data, 3 XMLTABLE 4 ('/departments/deptno' 5 PASSING XMLTYPE (xml_data.col) 6 COLUMNS 7 "deptno" number path '/deptno') x 8 /
deptno ---------- 1 2
2 rows selected.
As I said, this example generates the ORA-19279 error. Need processing XML data from an IN parameter?
The OUI threw the subject error during installation of Grid Infrastructure 12.1.0.1 on OEL 6.4 with Job Role Separation. This occurred after successfully running root.sh when the OUI started the NETCA task . The solution was very simple: go to /u01/app/12.1.0 and "chmod g+w grid". At that point I was able to click "retry" and it worked perfectly. There were no other errors or issues during the entire installation. Just passing it along.
Id Country city 1 US 2 US Boston 3 Boston 4 US Newyork 5 London 6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row If i pass country null and city Boston it should return row3 If i pass country UK and city Boston it should return row3 If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
I have 3 user defined collection types. I am trying to access the type3's attribute in type1 body (like backward accessing).
Ex. My Collection Types Structure (something like master detail structure)
create type type1 as object ( attr1 varchar2(10), attr2 varchar2(10), member procedure function1
[code]...
so, in the type1 body i have to get or assign the value either to type2's attribute or type3's attribute. I have search all the in internet but i haven't find anything such. how to find the reverse method of accessing the super type's attribute(s) in sub type's body.
I have created the below types and oracle objects.
create or replace type T_EMA_NP_SETDEL_RESP_REC as object ( respCode number, respDesc varchar2(255) )
create or replace type T_EMA_NP_RANGE_LNPTICKET_TAB AS TABLE OF T_EMA_NP_RANGE_LNPTICKET_REC create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following types are created in the Package specification
type t_resp_rec IS RECORD ( resp_code number, resp_desc varchar2(255) ); -- subtype t_ema_lnpticket is T186_IN_REQ_PARAMETER.T186_EMA_LNPTICKET%TYPE; -- Number type t_ema_lnpticket_tab is table of t_ema_lnpticket index by binary_integer;
I have the following two procedures
PROCEDURE getEMAReturnResponse( p_in_call_request_id IN number, p_ema_resp_rec IN t_ema_np_setdel_resp_rec, p_ema_range_lnpticket_tab IN t_ema_np_range_lnpticket_tab, p_endof_event IN varchar)
PROCEDURE Return_Response(p_in_call_request_id IN number, p_ema_resp_rec IN t_ema_resp_rec, p_ema_lnpticket_tab IN t_ema_lnpticket_tab, p_endof_event IN varchar2)
getEMAReturnResponse Procedure:
Accessed by Java application to pass the values. Should call the Return_Response procedure and pass the values received from Java.
Return_Response Procedure
The p_ema_lnpticket_tab is a sort of array that can have multiple values. Please see the example of values. Has all the business rules and validation that should be adhered.
Example of Vaules p_in_call_request_id = 1 p_ema_resp_rec = 12345, 'Operation Failed' p_ema_lnpticket_tab = (1,2,4,5) p_endof_event = Y
i am trying to run a script in which a command tries to create or replace a type.
i get this error:
ORA-02303: cannot drop or replace a type with type or table dependents
SQL> SQL> --create a test user: SQL> SQL> create user tuser identified by tuser
[Code]....
Table created.
SQL> SQL> --then change the type: SQL> SQL> create or replace type t1 as object (obj_type number(15)) 2 / create or replace type t1 as object (obj_type number(15)) * ERROR at line 1: ORA-02303: cannot drop or replace a type with type or table dependents
SQL> SQL> --if i'll do FORCE action on the type - it'll corrupt my depandant table: SQL> SQL> drop type t1 FORCE 2 /
Type dropped.
SQL> SQL> SQL> SQL> SQL> desc dpntnt_table Name Null? Type ----------------------------------------- -------- ---------------------------- ID1 NUMBER(7)
SQL> SQL> SQL> SQL> --if i re-create it - my table is still corrupted: SQL> SQL> SQL> create or replace type t1 as object (obj_type number(15)) 2 /
Type created.
SQL> SQL> SQL> SQL> desc dpntnt_table Name Null? Type ----------------------------------------- -------- ---------------------------- ID1 NUMBER(7)
SQL>
--if i re-create it - my table is still corrupted:
create or replace type t1 as object (obj_type number(15)) / desc dpntnt_table [/code]
1. If i'll do drop type FORCE what will happen to the dependent object(might be a table for example) ?
2. I understand that this type is already assigned to some object, but i can't seem to find out which one.
how do i find out which object is depending on the type i want to create or replace?
I have created the below types and oracle objects.
create or replace type T_SETDEL_RESP_REC as object ( respCode number, respDesc varchar2(255) ) -- create or replace type T_EMA_NP_RANGE_LNPTICKET_REC as object ( ticket number ) create or replace type T_RANGE_TICKET_TAB AS TABLE OF T_RANGE_TICKET_REC
The following type is created in the Package specification
type t_resp_rec IS RECORD ( resp_code number, resp_desc varchar2(255) );
I have the following two procedures
Procedure getResponse(p_call_request_id IN number, p_resp_rec IN t_setdel_resp_rec, p_range_ticket_tab IN t_range_icket_tab, p_endof_event IN varchar)
PROCEDURE ProcessResponse(p_call_request_id IN number, p_resp_rec IN t_resp_rec, p_ticket_tab IN t_ticket_tab, p_endof_event IN varchar2)
The get Response procedure is a wrapper procedure exposed to Java to pass values. The Process Response procedure is a main procedure where all logics and business rules are handled.
The Problem is:
How can I pass the values from get Response procedure to Process Response procedure. So that rules and validations are applied. Please note the p_ticket_tab may have many ticket numbers corresponding to p_call_request_id.
I've the following function returning OBJECT type. how to call this function
CREATE OR REPLACE TYPE GET_EMP_OBJ is object ( emp_name varchar2(50) , mgr_id number, dept_id number );
[Code]...
The above function got created successfully. And i'm confused how to call this functions. I tried like below but didn't work
DECLARE t_emp_info_1 GET_EMP_OBJ ; BEGIN t_emp_info_1 := get_emp(7566) ; for i in 1..t_emp_info_1.COUNT LOOP DBMS_OUTPUT.put_line ('Values are'||i.emp_name ) ; END LOOP; END;
I have been creating lot many threads around the same problem, however i thought i knew but realized I do not know or else do not know how to. I have created object type with an attribute READINGVALUE NUMBER(21,6)...How can i use type attribute on this object while declaring variable.....can we use type attribute on NESTED TABLES, similar to the db tables?
example CREATE TYPE READING AS OBJECT(READINGVALUE NUMBER(21,6)); CREATE TABLE INTERVALREADINGS OF TYPE READING;
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL Release 11.1.0.7.0 - Production CORE 11.1.0.7.0 Production TNS for Linux: Version 11.1.0.7.0 - Production NLSRTL Version 11.1.0.7.0 - Production
I'm new to this XML DB thing and also this is my first post.
I'm trying to populate collection type from XMLType. I was able to populate a table from XMLType but any way to populate the collection type. Here is the description of my problem:
Object Type: CREATE OR REPLACE TYPE DOC_ROWTYPE AS OBJECT ( REFERENCENUMBER VARCHAR2(255), REQID NUMBER(12),
[Code]....
Collection Type:
CREATE OR REPLACE TYPE DOC_TABLETYPE IS TABLE OF DOC_ROWTYPE;
I have a physical table which is created when I registered a schema.
A table (Temp_Result) got created with column SYS_NC_ROWINFO$ which is of XMLType.
As you can see this is only a temporary table which will store the response XML which I want to finally get it to collection type.
XML to parse: <code> <TFSResponse> <TFS> <refNumber>12345</refNumber> <reqId>123</reqId>
[Code]...
So each object in the collection is one TFS tag. how to implement this?
i want to get table name, constraint name, constraint type with join processes in string type. this is what i want: alter table tablename add constraint constraintname constrainttype(columnname)
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1 AS OBJECT ( SYSTEM_IDENTIFER VARCHAR2(50), PROCESS_TYPE VARCHAR2(50), abc typeObj2
) /
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC, ...... Where ....... AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL ) AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
WITH DATA AS ( SELECT '100' GRP, '01-JAN-2012' EFFECTIVE_DATE, '30-JUN-2012' TERMINATION_DATE from DUAL UNION ALL SELECT '100' GRP, '01-JUL-2012' EFFECTIVE_DATE, '31-JUL-2012' FROM DUAL union all [code]......
The above mentioned output is produced by using the following business rules:
-- row no 1 = Valid records -- row no 2 = effective date is 1 day after termination date of row no 1. Means valid records -- row no 3 = effective date is equal to the row no 2 termination date -- Means invalid record -- row no 4 = effective date is 1 day after termination date of row no 2 Means valid records -- row no 5 = The gap between row no 4 termination date and row no 5 effective date is more than 1 day. Means record is invalid.
Query always compare effective date with previous valid record termination date by using the above mentioned condition and return result accordingly.
Summary is that there should be 1 day gap between termination date and next effective date as well as effective date is always less than termination date in same rows. In addition, if record is invalid then next record check with previous valid record termination date.
