Hi to all, I have a stored procedure with an IN OUT parameter declared like follows: create or replace PROCEDURE RIFATT_SEGN0_INS(pIdRifattSegn0 in OUT NUMBER, pNumDossier IN VARCHAR2 , pNumConsegna IN NUMBER, pDtConsegna IN DATE, [..] ) AS [..] Whene...
I have a a cursor loop that's building a string by concatenating the contents of a table together, using code along these lines: OPEN cur_t; LOOP FETCH cur_t INTO v_texttoadd; v_string := v_string || v_texttoadd; EXIT WHEN cur_t%notfound; END LOOP; The problem is, of course, that the last item gets added twice because the sys...
Hi All, If 'Associative Array variable' is declared globally, able to use that in OPEN CURSOR USING statement. If 'Associative Array variable' is declared within package, while use in OPEN CURSOR USING statement, getting compilation error. More details provided below I am storing some values in one Associative Array variable. Late...
I need to call a number of different procedures within an Oracle package successively within a transaction. In fact, I need to call them about 5000 times. I was wondering what syntax I could use to batch these calls to Oracle, so I only need one round trip. I've tried "PACKAGE.PROCA :1, :2; PACKAGE.PROCB :3, :4, :5;" but I get back OR...
This is my first attempt at creating a package, so I must be missing something really really obvious (nothing that I've Googled for seems to even consider it worth mentioning). Obviously, if you have procedures in your package body that are not included in the specification section, then those procedures are private. The problem I've g...
Hi all, I have this SELECT below which i have to run it in a LOOP for every activity_id(record) that i get from the join. Also,this SELECT has to be run in multiple places in a procedure to get details and for that records-->I run the assign_ course or assign_test as below. Can anyone please help me write a function which would have t...
I have a simple SQL Statement that works perfectly in SQL Server: DECLARE @ID VARCHAR(10) SET @ID = '12345' SELECT * FROM theTable WHERE ID = @ID Can someone please tell me how to do this extremely basic thing in Oracle? ...
(Oracle PL/SQL) If I have a simple SQL statement that is throwing an error, ie: DECLARE v_sql_errm varchar2(2048); BEGIN UPDATE my_table SET my_column = do_something(my_column) WHERE my_column IS NOT NULL; EXCEPTION when others then -- How can I obtain the row/value causing the error (unknown)? v_sql...
I have this server with the following system: Windows 2003 R2 Ent Edition SP 2 Oracle 1.0.2.0.4.0 Databases: 5 The databases have their archivelogs located in flash recovery area as shown by the NAME column of v$recovery_file_dest and also by invoking "show parameter db_recovery_file_dest" from sqlplus. I'd like to monitor the space u...
Problem: I have a large database table (~500k records) which has a list of dates stored in a varchar2(15) column. These dates are stored in varying formats, ie. some are yyyy-mm-dd, some are mm/dd/yyyy, some are dd/mm/yy, some are mm/dd/yy, etc. Ie: 1994-01-13 01/13/1994 01/13/94 13/01/94 13/01/1994 etc I need to be able to shift thes...
Hi, I have a stored procedure that takes in two dates and passes back a cursor. The stored procedure compiles however I am having trouble writing the correct pl/sql to 'see' what is returned. I have tried the following: DBMS_OUTPUT.PUT_LINE('MY_CURSOR=' || MY_CURSOR) declaring local variables that represent the columns contained in ...
In Oracle, is there a function that calculates the difference between two Dates? If not, is a way to display the difference between two dates in hours and minutes? Check query below. SELECT Round(max((EndDate - StartDate ) * 24), 2) as MaximumScheduleTime, Round(min((EndDate - StartDate) * 24), 2) as MinimumScheduleTime, ...
I am looking for a static analysis tool for Python, Ruby, Sql, Cobol, Perl, PL/SQL, SQL similar to find bugs and check style. I am looking for calculating the line count, identify bugs during the development, and enforcing coding standard. ...
The results of my two PL/SQL select statements are integers 27 & 50, I want their division (27/50) 0.54 at output...how to do that? I have tried select * from ((select....)/(select ...)) but it does not work!! ...
I have a simple oracle statement in my procedure: update org.security_training_question a set a.actv_indr = 'N' where a.qstn_id in (v_qstns_to_delete); v_qstns_to_delete is a parameter being passed. It is a varchar2 field and a.qstn_id is a numeric field. When calling the Stored Procedure, for v_qstns_to_delete I am passing the fol...
Hi all, I might be overlooking something due to deadline stress. But this behaviour amazes me. It looks as if the cursor caches 100 rows and the continue statement flushes the cache and begins with the first record of a new cache fetch. I narrowed it down to the following script: drop table test1; create table test1 (test1_id NUMB...
Hi Folks, Simply querying running jobs using something like select * from dba_jobs_running; works fine when executed in my sqldevelopers SQL console. However, it does not work, when having exactly the same statement within a procedure. Compilation fails with PL/SQL: ORA-00942: table or view does not exist Any ideas? Is there so...
Hi, I'm developing this app where I have to call a function written in PL/SQL that returns a boolean. As I understand, bool is not a type in SQL, but in PL/SQL, so what will the return type for the function be? command.Parameters.Add("P_RETURN", OracleType.???); (For the record: I have no control over the PL/SQL end of things, so I am...
Hi, I have a XMLTYPE with the following content: <?xml version="1.0"?> <users> <user> <name>user1</name> </user> <user> <name>user2</name> </user> <user> <name>user3</name> </user> </users> How can I loop in PL/SQL through all the elements "use...
Is it possible to return an Oracle Ref Cursor to a caller that is in SqlServer T-SQL? When dealing with a normal .Net program there is this Knowledge Base article: http://support.microsoft.com/kb/322160 But is this same type of thing possible from T-SQL? ...