This is related to question: How to store unlimited characters in Oracle 11g? If maximum I need is 8000 characters, can I just add 3 more varchar2 columns so that I will have 4 columns with 2000 char each to get 8000 chars. So when the first column is full, values would be spilled over to the next column and so on. Will this design have...
Hi, Is there any way of running the $ORACLE_HOME/rdbms/admin/awrrpt.sql so that it doesn't require any input parameters, as in automatically collects a report for the previous hour? /j ...
We are facing problem in checking output parameters for “DBNull”. “DBNull” value returned by Oracle stored procedure or function is treated as “null” string by oracle 11g client/ODP.Net provider. This works fine with oracle 10g client as it returns “DBNull”. Because of this all our “DBNull” check fails ...
hi, i want to update the following procedure in the oracle table..but it is throwing error CREATE OR REPLACE PROCEDURE update_keywords (aKEYWORD IN VARCHAR2, aCOUNT IN NUMBER) AS BEGIN update searchable_keywords set KEYWORD =:new.aKEYWORD or COUNT =:new.aCOUNT where KEUWORD_ID = : old.KEYWORD_ID; END; this is my procedu...
When I execute a package body DDL statement SQL Developer warns, Warning: PLW-06015: parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL=1 How can SQL Developer be configured to not use PLSQL_DEBUG? PLSQL_DEBUG is set to false in an sql*plus session using the same connection details, > show parameters plsql NAME ...
Is there a way to give a subquery in Oracle 11g an alias like: select * from (select client_ref_id, request from some_table where message_type = 1) abc, (select client_ref_id, response from some_table where message_type = 2) defg where abc.client_ref_id = def.client_ref_id; Otherwise is there a way to join the two subque...
i want to delete all the data rows in the table. but without dropping the table. delete statement has a where clause so i have to give one by one for each row. is there any other way of deleting the whole data rows in the table. this is Oracle and using sqlplus ...
Hi I'm trying to develop an application that just before quit has to run a new daemon process to execute the main method of a class. I require that after the main application quits the daemon process must still be in execution. It is a Java Stored Procedure running on Oracle DB so I can't use Runtime.exec because I can't locate the ja...
MySQL supports events where i can set up something to happen in a specified time period Eg: Update the salary's every month Eg: Update something every year Eg: Change the status of a book to "overdue" in a week Etc etc etc How do I do this in Oracle? ...
TYPE point IS RECORD ( X NUMBER, -- The X co-ordinate of the point Y NUMBER -- The Y co-ordingate of the point ); This is defined in my package header. It is then used in a procedure defined in the same package. Is it possible for me to call the procedure via ODP.net? ...
Is it possible to correctly pass an OracleParameter to a boolean parameter in a pl/sql stored procedure? ...
i want to transfer my oracle database to another PC. What is the easiest way to do it? Are there any GUI tools to do it? ...
I need to retrieve data from an oracle table using a pl/sql stored procedure and odp.net. What is the standard way of doing this? ...
I want to show the BookTitle, Firstname along with a COUNT of Copies. When I run the query, it gives an error saying to group. But when I group it says not a expression to group. Can someone help me? SELECT bk.BookTitle, au.FirstName, COUNT(bkc.BookCopyID), rb.ReservedDate FROM Book bk, Book_Author ba, BookCopy bkc, ReserveBook rb, Aut...
I want to retrieve rows from an oracle table and convert them into objects. I am currently using a refcursor result and a datareader in c# to manually convert the rows to objects but this seems messy. Is their a better way of converting rows from a relational table to objects? EDIT: The project I'm on is not using any ORM tools so unf...
Hi everyone, i am creating a trigger and receiving some error, which i m not able to understand. Pls can anyone help me with that. create or REPLACE TRIGGER trig_data BEFORE INSERT ON data_db REFERENCING OLD AS OLD AND NEW AS NEW FOR EACH ROW BEGIN SELECT RAHUL_SEQUENCE.NEXTVAL INTO :NEW.USERID FROM DUAL; END; E...
Hi all, i'm stucked on a Date Column that stored a wrong Date. i'm using VS 2010 with .NET 4 and oracle 11g (Oracle.dataAccess.dll version 2.112.0.1) and i have to fill a column with a full date (dd:mm:yyyy hh24:mi:ss) I use a complex query with a lot of parameters in that way: cmd.Parameters.add(":date", OracleDBType.Date, myDate, Pa...
I've got an SQL-table with some million entries and I tried to query how much entries are older than 60 days (Oracle 11.2.0.1.0). For this experiment I used 3 different versions of the select-statement: (The cost-value is given by TOAD for Oracle V. 9.7.2.5) select count(*) from fman_file where dateadded >= (select sysdate - 60 from d...
I have been trying to move away from using DECODE to pivot rows in Oracle 11g, where there is a handy PIVOT function. But I may have found a limitation: I'm trying to return 2 columns for each value in the base table. Something like: SELECT somethingId, splitId1, splitName1, splitId2, splitName2 FROM (SELECT somethingId, splitId ...
Hi i have the following sql query SELECT @weekenddtm = CONVERT(VARCHAR,DATEADD(DD,(7 - DATEPART(DW,@p_end_dtm)),@p_end_dtm),111) and i tried converting it into oracle using this query v_weekenddtm := CAST(p_end_dtm + NUMTODSINTERVAL((7-TO_NUMBER(TO_CHAR(p_end_dtm,'D'))),'DAY') AS DATE); but it is giving me error..any idea how t...