I have this table in an Oracle DB which has a primary key defined on 3 of the data columns. I want to drop the primary key constraint to allow rows with duplicate data for those columns, and create a new column, 'id', to contain an auto-incrementing integer ID for these rows. I know how to create a sequence and trigger to add an auto-inc...
Hi, I'm looking for an UPDATE statement where it will update a single duplicate row only and remain the rest (duplicate rows) intact as is, using ROWID or something else or other elements to utilize in Oracle SQL or PL/SQL? Here is an example duptest table to work with: CREATE TABLE duptest (ID VARCHAR2(5), NONID VARCHAR2(5)); run ...
Oracle is giving me an error (ORA-00907: missing right parenthesis) when I run this query: select * from reason_for_appointment where reason_for_appointment_id in ( select reason_for_appointment_id from appointment_reason where appointment_id = 11 order by appointment_reason_id ) However, when I run just the subq...
Is there a way that you can have SERVEROUTPUT set to ON in sqlplus but somehow repress the message "PL/SQL procedure successfully completed" that is automatically generated upon completed execution of a plsql procedure? ...
In MS SQL 2005 or T-SQL, you can do something like: SELECT T.NAME, T.DATE FROM (SELECT * FROM MyTable WHERE ....) AS T I failed to try the similar SQL on Oracle 9i DB. In MS SQL, the nested SQL is treated as a temporary/dynamic view created on fly and destroyed afterward. How can I do the similar thing in Oracle? I really don't wan...
I'm running a PL/SQL block that is supposed to be calling a stored procedure who's output parameters are supposed to be populating variables in the PL/SQL block. The procedure compiles, and the PL/SQL block runs successfully. But I'd like to check the values of the variables populated by the procedure. Is there a way to output these val...
What is the string concatenation operator in Oracle SQL? Are there any "interesting" features I should be careful of? (This seems obvious, but I couldn't find a previous question asking it). ...
In PL/SQL, I would like to pass in a "source" schema as a parameter to a stored procedure. For instance: BEGIN CURSOR my_cursor IS SELECT my_field FROM <schema>.my_table ... I want the 'schema' value to come from an input parameter into the stored procedure. Does anyone know how I could do that? P.S. Sorry if this is a st...
My installation of APEX has come pear shaped on a Oracle 9.2.0.5.0 instance, all the packages are invalid. I've tried recompiling everything with DBMS_UTILITY.compile_schema, but still all the packages are invalid. So, tried recompiling individual packages, SQL> ALTER PACKAGE FLOWS_020000.WWV_FLOW_QUERY COMPILE BODY; Warning: Package ...
I see that in PL/SQL it is possible to set the session state of a particular variable for the remainder of the session's lifetime. For instance: ALTER SESSION SET CURRENT_SCHEMA=<schema> How do I query for the current state of this schema so I can revert back to it after performing some other statements? ...
I believe there is a way to do this, but I'm not familiar with ORACLE 10g as many other people are. Here's the scenario: I'm currently converting Classic ASP pages to ASP.net 2.0. I have a query that is creating a report. It's reporting Sales vs. Previous Sales. What is happening currently is one query is going out to the database and g...
Can PL/SQL procedure in Oracle know it's own name? Let me explain: CREATE OR REPLACE procedure some_procedure is v_procedure_name varchar2(32); begin v_procedure_name := %%something%%; end; After %%something%% executes, variable v_procedure_name should contain 'SOME_PROCEDURE'. It is also OK if it contains object_id of that p...
Hi everyone, I've got a quick question about default values in PL/SQL functions in Oracle. Take this program as an example; create or replace FUNCTION testFunction ( varNumber IN NUMBER DEFAULT 0 ) RETURN NUMBER AS BEGIN dbms_output.put_line(varNumber); RETURN varNumber; END; The idea here being that if no value is specified fo...
I want to use Oracle exception to handle errors that might happen in the code below. If a user provides the book ID and/or employee ID that doesn't exist in the database, NO_DATA_FOUND exception will be raised. Thus, how can I know which statement raises this exception. CREATE OR REPLACE PROCEDURE TEST_EXCEPTION ( book_id_in IN boo...
I'm currently using Oracle 10g. I use DBMS_CRYPTO package to encrypt the passwords of users for login. In order to encrypt or decrypt the data, I must have a key. So where should I put the key in order to hide it from other developers, or is there another way to encrypt data without being able to decrypt back? In SQL Server, I just us...
Can somebody highlight the main differences between PL-SQL and SQL? And which one do you recommend for a newbie who wants to dive into DBA? ...
I have read some PL SQL programming books, and they recommend me to group procedures, functions, cursors, and so on in a package. Packages provide modularity and information hiding, which is the OO design. However, I'm just familiar with stand alone procedures. Would anyone kindly provide some examples in code and how to call package fro...
I think the question title pretty much says it all, I'm preferably looking for a PL/SQL query to accomplish this, but other options might be useful too. ...
Is it possible to write a PL/SQL query to identify a complete list of a stored procedures dependencies? I'm only interested in identifying other stored procedures and I'd prefer not to limit the depth of nesting that it gets too either. For example, if A calls B, which calls C, which calls D, I'd want B, C and D reported as dependencies ...
I have read a book whose title is "Oracle PL SQL Programming" (2nd ed.) by Steven Feuerstein & Bill Pribyl. On page 99, there is a point suggested that Do not "SELECT COUNT(*)" from a table unless you really need to know the total number of "hits." If you only need to know whether there is more than one match, simply fetch twice with a...