I have a function that returns an object that has 3 values. Is there a way to call that function from a select statement and have each value be a different column? I could break it into 3 functions, but the values are related so I wanted to keep it as one for performance reasons. (So oracle doesn't have to call 3 very similar complex fun...
I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column. How is this possible in pl/sql, oracle? (I did code something similar using checksum in T-SQL but not sure how to do in pl/sql) ...
SCENARIO: I have two tables, table1(col1 date) and table2(col2 varchar(20)). REQUIREMENT: Whenever anyone updated the value in col2, todays date should be inserted/updated in col1. It should be done without using triggers. Now, I cannot think of anything possible to do it. So, I need your help; PLEASE. Thank you. ...
Hi, I have a simple query in a cursor Cursor some_cursor IS select sum(some_field) from some_table table_1 where table_1.TYPE =1 AND TO_CHAR(table_1.date,'YYYYMMDD') = '20090905' AND table_1.f2 = 962 AND table_1.f3 = 41813; Then i do fetch some_cursor into some_var;--some_var is o...
Hey all, I have a requirement where I need to generate a report about current software projects. Two of the columns are the date of the latest milestone and the date of the milestone previous to that. Obviously, milestones are stored in a transaction table so we can have many milestone per project. I've gotten to here so far, but now...
I have to perform many selects from an Oracle external table. I have 10 cursors that look a lot like this (ext_temp is the external table) CURSOR F_CURSOR (day IN varchar,code Number,orig Number) IS select NVL(sum(table_4.f),0) from ext_temp table_4 where --couple of conditions here, irrelevant for the question ...
The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technically, it is possible to use SELECTs withou...
Here is: declare v_str1 varchar2(80); begin v_str1 := 'test'; print :v_str1; end When I run it using SQLDeveloper just in a sql worksheet I get this: Bind Variable "v_str1" is NOT DECLARED anonymous block completed ...
I want to perform a search for a specific value "04L" in the column across all the tables,procedures,functions etc in the DB. I also want that output ( table,procedures that have same value) to the text file i.e the tables , procedures functions that have that value. Any help appreciated. ...
After I installed DB2 9.7 Express, I tried to enabled DB2 to support PL/SQL by following command: DB2SET DB2_COMPATIBILITY_VECTOR=ORA then I got result as below: DBI1301E Invalid value. Explanation: The value specified for the registry variable is invalid. User response: Refer to the DB2 Information Center to determine the valid...
I have a table say tb_load_files. It contains fields file_name, file_date,file_loc,file_status Now, File_status cane be x or y. The requirement is there can be multiple records for x status for combination of (file_name, file_date,file_loc) but only one record for y status. e.g. file_name file_date file_loc status abc.txt ...
Hello all, Here is an example of some TSQL that I would like to rewrite in PL/SQL. DECLARE @xml XML SET @xml = '<theRange> <theRow><First>Bob</First><Last>Smith</Last><Age>30</Age></theRow> <theRow><First>Sue</First><Last>Jones</Last><Age>34</Age></theRow> <theRow><First>John</First><Last>Bates</Last><Age>40</Age></theRo...
Hi, Im evaluating liquibase for a project starting today. Has anybody used it to create procedures, functions, basically all of the plsql stuff? If not, is it possible to write embedded sql code in the xml files? Thanks in advance. ...
I've declared the following types in my PL/SQL package: TYPE t_simple_object IS RECORD ( wert NUMBER, gs NUMBER, vl NUMBER); TYPE t_obj_table IS TABLE OF t_simple_object INDEX BY BINARY_INTEGER; Then I declare a variable: obj t_obj_table; However, when I want to use the variable, I cannot initialize or extend ...
Hello Oracle Experts I have a Stored Procedure like this procedure P_IssueUpdate ( Id in integer, ModifiedDate in date, Solution in varchar2 ) AS BEGIN update T_Issue Set ModifiedDate = ModifiedDate, Solution = Solution where id = id; END P_IssueUpdate; my problem is that the parameter name is the same name as the Tab...
I'm trying to turn free-form text into something more structured. I have a complex pattern that matches the great majority (well above the minimum acceptable limit) of the data available, and I'd like to use that to assist in structuring the data, rather than parsing the text character-by-character. The problem that I've just run into is...
Can someone help me correct the Trigger below? I am assigned this problem but my technical skill is limited. My script hit the error below (ORA 4091): Processing STANDARD PO Number: 27179 ...................................... Updating PO Status.. Done Approval Processing. dist id 611294gl amount 10000.88 bill_del_amount 0 l_amoun...
I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests): create or replace procedure p_xml_test_1( p_xml out nocopy clob ) is begin p_xml := '<?xml version="1.0" encoding="utf8" ?>' || '<test><something>some value</something></test>'; end p_xml...
I have a query where I want to replace avg(j2) with avg(case when j2 <> 0 then j2 else 0 end) The above is a specific example but the pattern is the same with all the replacements. It's always a word followed by a number that needs to be replaced with the case statement that checks if the number is not 0. I tried the following for...
There is something wrong with this trigger. But what? CREATE TRIGGER MYCOOLTRIGGER AFTER INSERT ON MYCOOLTABLE REFERENCING NEW AS newRow FOR EACH ROW DECLARE BEGIN END MYCOOLTRIGGER; SQL Developer output: Warning: execution completed with warning TRIGGER MYCOOLTRIGGER Compiled. Is there any way to get more info on this Warning? ...