the above query is giving the value of current QTD(on the basis of V_REPORT_DATE)..I need to integrate the same as user want the output as table type and he can also give some filter conditions.
That is I have created the User Defined Data Type as following. CREATE OR REPLACE TYPE Bit_Type AS OBJECT(Bit NUMBER(1,0));
After completing this creation of new UDT, I am trying to create the table with this UDT as follows, CREATE OR REPLACE TABLE Sample_Bit ( RegID Bit_Type);
I received an Error Message like: SQL Error: ORA-22913: must specify table name for nested table column or attribute 22913. 00000 - "must specify table name for nested table column or attribute" *Cause: The storage clause is not specified for a nested table column or attribute. *Action: Specify the nested table storage clause for the nested table column or attribute.
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 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;
I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.
Here is an example.
CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11)); SELECT object_name, object_type FROM user_objects WHERE object_type = 'TYPE'; CREATE TABLE Z (A CHAR(4), B SSN_T);
Through sql*plus, how can I copy a user-defined type from other schema to mine?
I've granted the execute object privilege over the user-defined type to my user, so I can use the type.
I know I can manually write and create the type, but I wanna learn (if exist) a statement to copy the user-defined type, something like: CREATE TABLE x2 AS (SELECT * FROM x1);
I'm testing ORACLE DATABASE 11g R2 over Windows O. S.
I'm trying to deduct x hours from a timestamp value. Everything works fine if x yields a result that is on the same day as the value I'm deducting it from. However, if it yields the day before then it gives the wrong answer using DML (although in SQL it works fine). Let me explain with an example:
Let's say current_timestamp is 20-MAR-12 09.40.00.000000 +00:00
If I deduct 8 hours from this I get the correct answer: 20-mar-12 01:40:00 (syntax: show current_timestamp-8/24)
If however, I deduct 10 hours from it (which takes it to the day before) I get garbage: 20-mar-12 00:20:00 (syntax: show current_timestamp-10/24). It should say: 19-mar-12 23:40:00
In SQL I get the correct result every time. So, my question is, is this a bug or intended behaviour? If the latter then what is it doing and how can I get it to give the answer I'm looking for?
What I'm trying to do is convert a timestamp from one timezone to another using DML. The NEW_TIME function will only give me what I want if I know whether the "to" timezone is currently in daylight saving or not. eg. Must I use CST or CDT?
The full syntax I'm using to try and achieve my aim is below.
show to_char( to_date( '2012-03-19--15-37-23' 'YYYY-MM-DD--HH24-MI-SS' )-(convert( extchars( tz_offset( 'AMERICA/CHICAGO' ) 3, 1 ) int )/24) 'YYYY-MM-DD--HH24-MI-SS' )
where 2012-03-19--15-37-23 is a user-supplied argument.
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 am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.
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 a two different Databases. I created a db link in DB 1 to connect to DB 2 and it is working fine when I select data from any table. but I have one table in the DB2 which has a column with user defined data type . so when I try to select this column from DB 2 by using the DB link it gives me this error :ORA-22804 remote operations not permitted on object tables or user-defined type columns.
How to integrate excel with oracle forms ? that it looks like a detail block. If i give 20 in a test item if i click on a button it should display 20 records in a from 10g that records look like excel records .
how to integrate SQLnet & c and I'm quite losted at the moment.
Searching with google gives some random stuff, which does seems to be irrelevant.some oracle db somewhere and need for good way to use that remote db (one solution seems to be using SQLnet).
We are trying to integrate OBIEE 10g/11g and APEX 4.2. We would like to open report and a form in OBIEE so that users can update/writeback the data from OBIEE. Is there a way we can do this ?
How to integrate Google geocoding as RESTful service introduced by APEX 4.2? my supervisor wanted to use RESTful service for geocoding function. While I am using Google geocoding through simple HTTP request. I even do not know whether I am using SOAP or RESTful with PL/SQL utl_http.request.
PROCEDURE Return_Summary(WX IN dbms_sql.varchar2_table, WX OUT SYS_REFCURSOR) IS
Begin FOR i IN 1 .. Pi_ WX.count LOOP
/* I need to put this results in a temp table or table object Can I use temp table for this or do we have any other recommended method. The loop might execute max of 10 times and for each run it might return 100-200 records. */
select WX_NM, WX_NUM from TAB A, TAB B, TAB C where A.KEY1 = B.KEY1 and B.KEY1 = C.KEY1 and C.WX = WX(i); End Loop; End;
DECLARE JOBSFILE UTL_FILE.FILE_TYPE; -- TAKE ALL JOB TITLES FROM JOBS CURSOR JOBSCUR IS SELECT * -- DDOCNAME,DDOCTITLE,DSECURITYGROUP,DDOCAUTHOR,DDOCTYPE,DINDATE,PRIMARYFILE,EXTRACTIONDATE,BATCH_ID FROM TARGET_UCM ; [code].......
this is my plsql here to print table values i am using many utl_file.put_line statements is there any way to print all table values in a single utl_file.put_line.
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE v_mm number (2); v_yyyy number (4); min_mm number (2); min_yyyy number (4); max_mm number (2); max_yyyy number (4); min_date date; [code]....
A B ---------- ---------------------------------------- 1 Hello 2 Hello 3 No Hello 4 No Hello 5 Hello 6 Hello 7 Hello
I want to print the output of this table as -->>
A B ---------- ---------------------------------------- 1 2 Hello 3 4 No Hello 5 6 7 Hello
To make it more clear, I just want that whenever the value f column "B" changes then only its value should be printed, else it should be NULL. And if "B" has same value for all the records then the value of "B" should be printed at the last.
