In the answer to a previous post (Tuning Rows-to-Cols Query), I learned how to more efficiently construct a row-to-cols query which allows for filtering by date. However, I now need to take this one level further. The schema for the query below is as follows: SAMPLE (1-to-many) TEST (1-to-many) RESULT (1-to-MANY) Each sample has one or...
I have a SQL*Loader control file that has a line something like this: FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '#' Normally, I'd use a quotation mark, but that seems to destroy emacs's python syntax highlighting if used inside a multi-line string. The problem is that we are loading an ADDRESS_LINE_2 column where only 7,000 out...
Hi all. I need to debug in pl/sql to figure times of procedures, I want to use: SELECT systimestamp FROM dual INTO time_db; DBMS_OUTPUT.PUT_LINE('time before procedure ' || time_db); but I don't understand where the output goes to and how can I redirect it to a log file that will contain all the data I want to collect? I use Oracle ...
Suppose I have schemas A and B. In schema A I would like to call package X in schema B. However, there exists a package B in schema A. A: package B B: package X When I call from schema A: begin b.x.foo(); end it looks for procedure X in package B, i.e. A.B.X(), and gets an error. How can I fully qualify the call to force...
I have problem when joining tables (left join) table1: id1 amt1 1 100 2 200 3 300 table2: id2 amt2 1 150 2 250 2 350 my Query: select id1,amt1,id2,amt2 from table1 left join table2 on table2.id1=table1.id2 My supposed o/p is: id1 amt1 id2 amt2 row1: 1 100 1 150 row2: 2 200 2 250 row3: 2...
I need to check if a record exists in the table or not from a SELECT statement. If the record exists, do an update otherwise create a record on the table. I'm trying to but i'm getting PLS-00103 error. These are the errors that I'm getting when i run my code in DBVisaulzier: 18:00:09 [DECLARE - 0 row(s), 0.000 secs] [Error Code: 655...
[Previous essay-title for question] Oracle SQL: update parent table column if all child table rows have specific value in a column. Update RANK of only those students who have 100 marks in all the subjects. If student has less than 100 marks in any subject, his RANK should not be updated. I have a scenario where I have a parent table a...
Is it possible to determine when an object within an Oracle database became invalid? I have tried looking at the DBA_OBJECTS view, but none of its date stamp columns seem to be affected when the STATUS changes to 'INVALID'. ...
I have some SQL scripts that I'm trying to automate. In the past I have used SQL*Plus, and called the sqlplus binary manually, from a bash script. However, I'm trying to figure out if there's a way to connect to the DB, and call the script from inside of the bash script... so that I can insert date and make the queries run relative to a...
This feels like a dumb question, but I see the following in the Oracle concepts guide on transaction management: A transaction ends when any of the following occurs: A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause. A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the cur...
I find the need to send SQL statements by email often and I was wondering if anyone had a tool that would color code the Oracle Reserved Words, Keywords, and Namespaces so that when the code is copied from the tool into say Lotus notes it would appear in the email as it does in the tool. ...
I wrote a package to query rows from a table. This select query will call other functions and returns all the rows from table. But when i write a package with all functions and sprocs , my sproc with select statement gives me an error saying i cannot execute without into statement. But if i use into then it will return only one row. How ...
How can I view the results returned by a pipelined function in Oracle SQL Developer ? I'm invoking the function using a simple select..from dual like select piaa_extract.FN_PIAA_EXTRACT('01-JAN-00','01-JAN-12') FROM DUAL and the result I get is IQCFINAL.REC_PIAA(IQCFINAL.REC_PIAA,IQCFINAL.REC_PIAA,.....,IQCFINAL.REC_PIAA) Allround...
I have an SQL script that is called from within a shell script and takes a long time to run. It currently contains dbms_output.put_line statements at various points. The output from these print statements appear in the log files, but only once the script has completed. Is there any way to ensure that the output appears in the log file a...
In our test bed, a number of test suites will be run in a row (unattended), producing reports for later consumption. I want to include in those reports queries which are candidates for further investigation, along with the data that justifies their inclusion in that list. We should be able to associate any query identified this way wit...
We are looking to retire old data in out Oracle warehouse. To give a very simplified overview, a proposal was suggested to develop a process using PL SQL Stored Procedures that, given source/dest table, etc., parameters, use the Oracle ALL_TAB_COLUMNS view to construct a target table that mirrors the source table. If the dest table ex...
Hi, am new to oracle. is there a bulitin keyword does the same job of SQL server APPLY?? ...
I'm trying to load data from oracle to sql server (Sorry for not writing this before) I have a table(actually a view which has data from different tables) with 1 million records atleast. I designed my package in such a way that i have functions for business logics and call them in select query directly. Ex: X1(id varchar2) x2(id varch...
I have 2 tables in the following format: employee (employeeID, EmployeeName, DepartmentID) departments (DepartmentID, DeptName) How many employees there are who work in each of the departments that have more employees than the average number of employees in a department. im looking to the results in the following format: Dept Name...
I have a string from oracle table like this: SELECT col1 FROM MyTable; col1 part1;part2;part3 .... How can I get a substring from col1 such as part2? SELECT col1, INSTR(col1, ';', 1, ...) SubStrPart2 // string func? col1 SubStrPart2 .... part2 Not sure what string functions I can use here so that I can easily upd...