I want to use DBMS_OUTPUT.PUT_LINE, but the number of rows exceeds just 1. There is nothing in the table which increments sequentially, so I can't use a loop. Is there some way to print out each row in the table? ...
I have an oralcle SP forced on me that will not accept an empty parameter in an update. So if I wanted to set a value back to the default of ('') it will not let me pass in the empty string. Is there a keyword you can use such as default, null, etc that oracle would interpret back to the default specified for a particular column? ...
I am having the below statement from stored procedure. It's giving Insufficient Privileges. But If i run the create statement alone from the sql prompt it's creating table. execute immediate 'create table TEST_ABC( NO_AC NUMBER(8) , ILL_PER VARCHAR2(15) , INIT_C DATE ...
I've got the syntax down but I'm wondering if somebody can provide an illustrative use case where database synonyms are very useful. ...
Is there a way of finding the name of the server an Oracle database is hosted on from PL/SQL? ...
Please suggest some Freeware both for Client and server side. This is to learn every thing about PL/SQL.. ...
I'm mostly an oracle novice, so forgive me if this is a stupid question... I have a schema called 'CODE' with a stored proc that executes arbitrary SQL (for now, please ignore the potential security issues associated with that). The SQL that is passed in will select data; but all of the data resides in either schema A, B, or C - but th...
I have an application that uses AJAX liberally. I have several places where a single database column is being updated for the record the user is actively editing. So far I've been creating separate stored procedures for each AJAX action... so I've got UPDATE_NAME, UPDATE_ADDRESS, UPDATE_PHONE stored procedures. I was just wondering if...
Does anyone use the PL/SQL Web Toolkit at all? We use it for internal reporting where I work. However, does anyone have any experiences of it for producing client-facing websites? General advantages/disadvantages compared to other web languages, such as JSP, PHP etc ...
Given the following Oracle (10g) package definition: create or replace PACKAGE "foo" AS bar VARCHAR2(32000) := NULL; END; what is the scope of bar? Does each session get its own foo.bar, or is foo.bar global across sessions? Can you quote me chapter and verse from a reference document? ...
We have a lot of SP's and many of them take forever to execute. These SP's are pretty long and its a pain to go through the SP to find out which query is taking long. Whats the best way to find out which query is taking the most time? so that we can just concentrate on that query rather than spending time in research. Currently I am ...
I have two PL/SQL Stored procedure each handling its own Transaction (Begin/Commit and Rollback in case of error). From .Net code I Call these two SP as shown below. using (TransactionScope ts = new TransactionScope()) { CallSP1(); CallSP2(). ts.SetComplete(); } If my Call to SP2 fails will i...
i am very confused please help to clarify these differences! are they all completely different languages? what is the overlap? ...
Is there an efficient way to version store procedures written in PL/SQL? (I only mention PL/SQL because there may exist a particular tool, any answer directed to versioning of stored procedures is ideal). ...
I have a schema like : employees (eno, ename, zip, hdate) customers (cno, cnmae, street, zip, phone) zipcodes (zip, city) where zip is pk in zipcodes and fk in other tables. I have to write an update query which updates all the occurence of zipcode 4994 to 1234 throughout the database. update zipcodes,customers,employees ...
I am into designing ETL with source and target database as oracle Standard Edition. For ETL purpose I need to get the changed data everytime.Client does not want any changes to be made in source objects. Is it feasible to create Materialized view log on source database using dblink to track Inser/Update/Delete on the identified tables....
I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database: SELECT TRUNC( /* Midnight Sunday */ NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL) ) AS week_start, TRUNC( /* 23:59:59 Saturday */ NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1 ...
I know in C like languages logical operators are evaluated one at a time so: false && really_expensive_function() is doesn't result in the function being called (I can't remember the CS name for this). Does the same happen in PL/SQL or do I need to break the IF parts out to separate blocks? ...
Does anyone know of an open source/free tool for converting pl/sql code to a flowchart or data flow or activity diagram? Thanks ...
I have a very simple query that's giving me unexpected results. Hints on where to troubleshoot it would be welcome. Simplified, the query is: SELECT Obs.obsDate, Obs.obsValue, ObsHead.name FROM ml.Obs Obs JOIN ml.ObsHead ObsHead ON ObsHead.hdId = Obs.hdId WHERE obs.hdId IN (53, 54) This gives me a query cost of:...