I am trying to put together an XML document from several pieces. To get the data, I had several queries whose results were XMLTypes. I found a function named getNodeFromFragment in the xmldom package that looked like it could take these XMLTypes and return a DOMNode that contained them, but it doesn't seem to work. Simple example here:...
Hello, I need to create a table with the following structure: calendar week; week start date; week end date which contains all weeks beginning in 2007 until the current week. The special thing is, that when an end of month falls within a week, the week is cut in two slices - one record that has a start date that is the beginning of ...
I have pairs of tables in the format TABLE and TABLE_TWIN now TABLE is the main table with lots of data TABLE_TWIN is a table with the exact same fields with a little data (different data) Now I would like to copy all rows from TABLE_TWIN to TABLE using a stored procedure. I have many such tables and could like the stored procedure t...
When writing a procedure in PL/SQL, I can declare a parameter's type as myTable.myColumn%TYPE so that when I alter myColumn's type from say varchar2(20) to varchar2(50) I don't have to change the procedure's parameter type. Is there something similar in T-SQL? ...
I have several scripts that I would like to start from a menu presented to the SQLPlus user. Something like: Please make a selection: 1: Do script a 2: Do script b 3: Do script c I just need a point in the right direction, not a quick answer. ...
Hi I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures. Procedure: create or replace procedure temp_proc is begin DBMS_OUTPUT.PUT_LINE('Test'); end it is created successfully. But when i execute: execute temp_proc; it shows ORA-00900: invalid SQL st...
Why can't we use :new and :old columns in a statement level trigger? ...
On my website I have a .php script to which our customers can post orders. $destname = CreateUniqueOrderFileName(); if (is_uploaded_file($_FILES['file']['tmp_name'])) { if (move_uploaded_file($_FILES['file']['tmp_name'], $destname)) echo "OK"; else echo "ERROR: move"; } So for my ...
As part of a release we run a load of PL/SQL scripts against a database. Recently someone left the ; off the end of a line in one script that was called another script so this meant that script did not get run. Because this did not cause an error, it just didn't get run, it took quite a while to track down what had happened. I want to c...
I'd like to do the following in Oracle 10g (this is a contrived example to show the concepts, not real code) create table orders (order_id NUMBER); insert into table orders values (1); insert into table orders values (2); insert into table orders values (3); TYPE NUMBER_ARRAY_T is TABLE of NUMBER; PROCEDURE VALIDATE_O...
I've defined VARRAY of a user-defined type such as this : CREATE OR REPLACE TYPE TEST_T AS OBJECT (C1 VARCHAR2(20 BYTE), C2 VARCHAR2 (11 Byte)); CREATE OR REPLACE TYPE ARRAY_TEST_T AS VARRAY(200) OF TEST_T; Is it possible to create a java stored function/procedure that accepts VARRAY or user-defined type as IN parameter ? If it...
I'm trying to randomly select a card from a table of cards with columns c_value and c_suit using a procedure. After selecting it, the procedure should update that entry's taken field to be 'Y'. create or replace procedure j_prc_sel_card(p_value OUT number, p_suit OUT number) AS CURSOR CUR_...
I am trying to write a data migration pl/sql script to transfer some of the data in one schema to a different schema on another server. The second database started as a subset of the original database, but we have modified the schema. So I can't just use the following for each table: Insert into DB2.table_name select * from DB1.table_n...
I'm getting some strange behaviour from an Oracle instance I'm working on. This is 11gR1 on Itanium, no RAC, nothing fancy. Ultimately I'm moving data from one Oracle instance to another in a data warehouse scenario. I have a semi-complex view running over a DB link; 4 inner joins over large-ish tables and 5 left joins over mid-size tab...
Hey guys, We are having some major performance issues with SELECT queries out one of our databases. Please see the simple procedure and associated code below. In the code the ExecuteReader() method is executing in around 10 seconds on a query returning 30K records. Iterating the Reader takes 2 minutes (even when I am not pumping the ...
I want to be able to insert a variable number of rows into a table based on user input? eg. Please enter value, enter "done" when no more values: value 1 Please enter value, enter "done" when no more values: value 2 Please enter value, enter "done" when no more values: done 2 Rows inserted successfully. I'm not sure how to store the ...
Suppose I have a BOOLEAN variable within a PL/SQL block in an Oracle Form: DECLARE is_viewable BOOLEAN; BEGIN is_viewable := ...; IF NOT is_viewable THEN raise_my_error(); // pseudo-code END IF; END; After stepping through this code several times with a debugger, I have determined that raise_my_error() never gets called. ...
what are the queries to do the following in Oracle? get names of all views which are present ? (Similar to select * from view, but i want to get views not tables) See source of a particular view. ...
The purpose is to send extra information to triggers like current user id from a web application. Since a connection pool is used, and same user id is used for all connections how do I pass the original web user id to trigger? This I need to implement without touching application code. It is a java based application. John ...
I have quite a few lines of PL/SQL code in Oracle 10g, and we are going to move to 11g. I expect my code to work there too, but: Are there any pitfalls to expect, any problems you encountered? What new features are you using? ...