I have an SP which takes filename as input parameter and loads the data into table (using UTL_FILE package), it is working fine with flat file, but if it is anything other than flat file (xls, doc, docx, pdf) then in When Others exception I'm showing as UNKNOWN Error.
Requirement: Is there a way to check the type of file (txt, xls, doc or pdf) passed as input to the stored procedure using UTL_FILE package (Only file name is send as input parameter not with file extension)
Is there a seeded function by which I can check all the rows which stored dates in varchar column.
I have a table say test (test_data varchar2(100));
Now I will insert all types of records into the table varchar,number dates and then i will write q query to etch all those records only which has dates only
INSERT INTO test(1); INSERT INTO test('ABC'); INSERT INTO test(SYSDATE); INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY')); INSERT INTO test(TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')); INSERT INTO test('15/01/2012'); ... commit;
I am trying to compile a .pc file with the make file which I created. But when I try to give make command I am getting following error
>make testfile cc -o testfile testfile.c "testfile.c", line 117: warning: no explicit type given "testfile.c", line 119: warning: no explicit type given "testfile.c", line 121: warning: no explicit type given "testfile.c", line 122: warning: no explicit type given "testfile.c", line 123: warning: no explicit type given Undefined first referenced symbol in file sqlcxt testfile.o ld: fatal: Symbol referencing errors. No output written to testfile
3 recover database using backup controlfile;//in this step generate error msg "database not mounted"
4 alter database open// this is also happend above error
ORA-01122: database file 1 failed verification check ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf' ORA-01207: file is more recent than control file - old control file
I am running Oracle Database 10g R2 on windows 2003, I want to create a batch file to check if the database is idle or not, and if it is idle shut it down and start it up.
I am writing a program for doing some file transfer between the client machine and the application server.I am using Webutil_File_Transfer.Client_To_AS to do the transfer and also using Webutil_File.File_Size to check on the file size at the source.
Once the transfer is complete, I also need to check on the destination file size (the application server running on Linux) for verification purposes and can't find the way to do it.
I have just migrated database to 11.2 ..Migration is successfull and now database is in open mode working fine.BUT i m getting following mesage in alert log file
"Time drift detected. Please check VKTM trace file for more details."I m using windows platform.
When uploading a static text/javascript file (i.e. filename = 'thisFile.js') the file Mime Type defaults to 'application/octet-stream' (instead of 'text/javascript'). This issue occurs in I.E. 8 but not in Firefox.
i load data to this table from a file using sqlldr.
what is the proper data type should i use in control-file for both the fields.? i dont mention any datatye in ctl file which is working fine with given dataset.
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.
I have a table in SQL , I am creating a column Of name in it , i want to restrict user to enter name in Capital only ,and i want to create this at table level . I tried Check Operator but failed .
I have a text column having date data. In the format of YYYYMMDD.Some of the records might have invalid date data as well. Is there any function which checks whether it's value is a right DATE? Something like
I would like to check and make sure that features like partitioning, Tuning, Diagnostics etc. have NOT been used. In Oracle 10 + we have the dba_feature_usage_statistics.
i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not
