Hi, I have a bunch of sql scripts that create / drop sequences, users and other objects. I'm running these scripts through liquibase, but they fail because oracle complains when I try to drop a non existing sequence, or create an existing user. Is there an oracle way to prevent errors from happening? Something of the sort Create ...
I am trying to get my stored procedure working in Oracle and am getting an Underflow error. I am trying to delete related information from six different tables. I can run the delete statements separately in SQL Developer without error. When I try and run the procedure from my C# codebehind I get an exception returned with the Underflo...
Using Oracle 10.2.0. I have a table that consists of a line number, an indent level, and text. I need to write a routine to 'natural' sort the text within an indent level [that is a child of a lower indent level]. I have limited experience with analytic routines and connect by/prior, but from what I've read here and elsewhere, it seems ...
Hello all, I'm executing sql queries in oracle 10g. I want to join 3 tables into a single new table containing all the records from the 3 individual tables. The balance should be Summed up wherever the reg_id is duplicated between the 3 tables, such that there is just one entry per reg_id with the summed balance in my new table. Sampl...
I'm producing XML right from PL/SQL in Oracle. What is the preferred way of ensuring that outputted strings are XML-conformant, with regards to special characters and character encoding ? Most of the XML file is static, we only need to output data for a few fields. Example of what I consider bad practice: DECLARE @s AS NVARCHAR(100...
I have the following table and trigger. CREATE TABLE LogSchema ( user varchar2(100), date_ date, operation varchar2(100), obj_type varchar2(100), obj_name varchar2(100) ); CREATE OR REPLACE TRIGGER LogSchema AFTER DDL ON USER1.SCHEMA /* can't use ON DATABASE or else it will log everything that happens on all sc...
I've got a PL/SQL VArray that I'm filling with a BULK COLLECT query like this: SELECT id BULK COLLECT INTO myarray FROM aTable Now I'd like to pass a slice of this collection into another collection, something like this: newarray := myarray(2..5) This should pass the elements 2,3,4 and 5 from myarray to newarray. I could write a l...
I am using Oracle SQL Developer, but I am having an issue seeing results from a package that returns a ref cursor. Below is the package definition: CREATE OR REPLACE package instance.lswkt_chgoff_recov as type rec_type is record ( source_cd lswk_tpr.gltrans.tpr_source_cd%TYPE, ...
Okay, I have reached a sort of an impasse. In my open source project, a .NET-based Oracle database browser, I've implemented a bunch of refactoring tools. So far, so good. The one feature I was really hoping to implement was a big "Global Reformat" that would make the code (scripts, functions, procedures, packages, views, etc.) standard...
I'm currently looking for a good (free) tool to do some PL/SQL development on Windows against an Oracle XE database. I'm looking to develop some stored procedures, functions and packages, and need decent debugging capabilities (breakpoints, variable inspection etc.). For the SQL side of database stuff, I've been using the TOADfree tool,...
How to make procedure that update 5 table's ? for ex: ....update table1 set field1 = ....... ....update table2 set field2 = ....... ....update table3 set field3 = ....... in oracle 10g - pl/sql ...
In Oracle, is it possible to INSERT or UPDATE a record (a row) through a view? ...
I have a table in my database, user_answers that stores users answers to a series of questions, with rows; user_id, question_id, answer_id and text_entry. Question text and answer text (if any) are stored in lookup tables. There are three types of questions, single-answer questions, multiple-answer questions and text-entry answer quest...
hi i try to understand procedure..... but still dont..... i need simple sample for procedure. i need procedure that i'll insert Fname and Lname, and i get table with the result search how i can do it ? i need package ????..... or cursor ????........ work on oracle 10g thank's in advance ...
I have made stored procedures in oracle. I'm calling it through my asp.net code. The procedure is : PROCEDURE prc_GetNewQuestionNo(iNextQuestionNo IN OUT NUMBER) IS iQuestionNo NUMBER ; BEGIN Select MAX(QUESTIONNO)+ 1 INTO iQuestionNo from tblIFFCOQUESTIONMASTER; iNextQuestionNo:=iQuestionNo; END prc_GetNewQues...
Similar question, but for Oracle. Why would I not always want to choose VARCHAR(MAX)? ...
I am writing a stored procedure for which I need to populate a table based on the data being reported on. In this situation, I will be pulling in three values per day for a certain code in a date range. Say on a certain run of this stored procedure, I have code values X, Y, and Z for a date range as so: select abc.code, abc.da...
hi guys, i'm a pl/sql newbie. now i have a question about oracle type. i saw there are two types of type : CREATE OR REPLACE TYPE "TYPE_NAME1" AS OBJECT ( temp_trans_id number(10), trans_id number(10), resion_id number(10) ) or type new_type_name is record( column1 number, co...
Hi, Below is my Oracle Procedure. When i call this procedure using java, it throws error like this. CREATE OR REPLACE PROCEDURE sp_Get_Menu_Parents ( v_inMenuID IN VARCHAR2 DEFAULT NULL, cv_1 IN OUT SYS_REFCURSOR ) AS BEGIN OPEN cv_1 FOR SELECT MenuItemId, MenuItemName, MenuItemDisplayName, ...
Here is my simple PL/SQL program: DECLARE CURSOR c1 is SELECT typ, specifikacia_typu, spz FROM Auta WHERE (substr(spz, 1, 2) = 'KE' OR substr(spz, 1, 2) = 'KS') AND ROWNUM <= 2; CURSOR c2 is SELECT typ, specifikacia_typu, spz FROM Auta WHERE substr(spz, 1, 2) <> 'KE' AND substr(spz, 1, 2)...