I'm guessing this is a matter of figuring out what oracle command to use, but after a couple of hours of googling, I haven't found anything that can do what I need. So here's what I need to have happen in a nutshell: Table 1 Table 2 | PrjID | | PrjID | UserID | |----------| |----------|------...
I am new to Oracle 10g. I have couple of tables as shown below: INCOME_MASTER INCOME_ID NUMBER(10) sEQ NUMBER, INCOME_TYPE VARCHAR2(10), INCOME_DATE DATE INCOME_DETAILS INCOME_DETAILS_SEQ_NO NUMBER(10) SEQUENCE, INCOME_ID NUMBER(10), ITEM_ID NUMBER(10), ITEM_VALUE NUMBER (10,...
Hi, I've CSV string 100.01,200.02,300.03 which I need to pass to a PL/SQL stored procedure in Oracle. Inside the proc,I need to insert these values in a Number column in the table. For this, I got a working approach from over here: http://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i [2) Using SQL's c...
How to invoke a webservice through pl/sql block for which we know url,username and password. And how to see the response? Give some sample code... Thanks in advance I have used the following piece of code: CREATE OR REPLACE FUNCTION READ_DATA_FROM_WS (url IN VARCHAR2, username IN VARCHAR...
Hi! I just wanna to change in this XML (contained in XMLTYPE variable) all nodes named "ChildNode" with "Name"="B" attribute values to "C": <RootNode> <ChildNodes> <ChildNode Name="A"/> <ChildNode Name="B"/> </ChildNodes> </RootNode> DECLARE FXML XMLTYPE; BEGIN FXML := ...; -- see text before -- what next? END; Than...
As the title suggested , I am looking for a function in pl /sql which does something similar like the DateAdd function. I have been looking and I found the add_months function but I would really like one that is a little more variable since I need to be able to add minutes, hours , days etc. ...
Most SQL developers know and use Narayana Vyas Kondreddi's sp_generate_inserts from http://vyaskn.tripod.com/code/generate_inserts.txt Is there something similar for Oracle? ...
In an Oracle package I have defined a type type setTable is table of my_sets.pkey%type; in the package declaration (the non-body part). The pkey column referenced is a number(38). Then in a function in the package body I have ... with d as (select column_value from table(sets)), ... where sets is a parameter to the function of t...
Does anyone know if it is possible to take the date from a random date in pl sql. example. SELECT SYSDATE FROM DUAL and here the output would be say : 26-10-2010 13:30:34 Now I want to have just the date as a number. In this case that would be 26. Or is there some sort of function like IsNum that can recognize it for me. So I ...
I have built a "best guess" query to get the related table.column for a sequence. Is there a better, cleaner method to get this relation? select ut.table_name, ut.table_owner, ut.trigger_name, us.sequence_name ,upper(dbms_metadata.get_ddl('TRIGGER', ut.trigger_name, ut.table_owner )) triger_ddl ,to_char(regexp_substr( upper(d...
I have 2 procedures (A,B) in Oracle 9i. In individual, they all work fine. But I cannot make a procedure C that calls A, which in turn calls B. I put a dbms_output.put_line before C calls A, before A calls B and one in B. Somehow, only the first put_line works. What are the possible reasons why this doesn't work? Thank you, CREATE OR RE...
Our java class calls PLSQL proc which returns date in default format which is defined by NLS_DATE_FORMAT. Our application sets its own Locale for internationalization but I want the date format to remain just 'DD-MON-RR' which is en_US Locale NLS_DATE_FORMAT. Due to the change in locale oracle's fetched Date string differs and subsequent...
I need to pass an array from C#.net application to oracle stored procedure. Can anyone please let me know how to go about it? Also, which OracleType type do I use in C# when passing input parameter to stored procedure? FYI, am using System.Data.OracleClient in my C# app. Thanks. ...
Hey guys, I need to figure out a way to back up and also migrate our Oracle database from our production schema to the dev schema and the other way around. We have bunch of config tables that drive how systems on our platform run, and when setting up new systems or doing maintenance, we need to update our config tables. We want to be ab...
I have a stored procedure with a parameter name which I want to use in a where clause to match the value of a column i.e. something like where col1 = name Now of course this fails to match null to null because of the way null works. Do I need to do where ((name is null and col1 is null) or col1 = name) in situations like this or i...
Any idea to run those kind of scripts from Oracle PL/SQL? Any solution would be appreciated. Thanks! ...
I would like to know how to check if a ref cursor returns data. Let's say I have the following code in a PL/SQL package: type refcursor is ref cursor; procedure Foo(cursorresult out refcursor) is begin open cursorresult for select * from table t inner join t2 on t.id = t2.id where t.column1 is null; end; proced...
I want to update the same record which fires a trigger. I have done that using "BEFORE INSERT" option. But note that I have use a transaction to rollback the operation if there is an any faliure. CREATE OR REPLACE TRIGGER GANUKA.INTF_CONTROLLER_UPLOADER BEFORE insert ON GANUKA.INTF_CONTROLLER for each row DECLARE PRAGMA AUTONOMOUS_...
I would like to do an INSERT / SELECT, this means INSERT in the TARGET_TABLE the records of the SOURCE_TABLE, with this assumption: The SOURCE and the TARGET table have only a SUBSET of common columns, this means in example: ==> The SOURCE TABLE has ALPHA, BETA and GAMMA columns; ==> The TARGET TABLE has BETA, GAMMA and DELTA columns. ...
When faced with the task of copying a record in a database and modifying just a handful of values, I tend to use a temporary table to avoid writing out all of the unchanged columns. Does anyone know how this would affect performance on a large scale system? A quick example (which also shows why I prefer the temporary table method): Le...