Hello, I would like to run a PL/SQL procedure involving 80,000,000 records. This PL/SQL procedure deletes about 80,000,000 records, backupping them in a GLOBAL TEMPORARY TABLE created with the clause ON COMMIT PRESERVE ROWS. How can I know how many records can contain this GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS? What is the si...
Hi, guys. Say I have a sample table: id_pk value ------------ 1 a 2 b 3 c And I have a sample PL/SQL block, which has a query that currently selects a single row into an array: declare type t_table is table of myTable%rowtype; n_RequiredId myTable.id_pk%type := 1; t_Output t_table := t_table(); b...
Where can be found implementations of built-in Oracle PL/SQL packages? For example, there can be found specification stdspec.sql with impl. stdbody.sql. But for package DBMS_ASSERT there is only spec. dbmsasrt.sql. Where can be found implementation? (using version 11.2) ...
I need to create a Oracle tables with random number of columns for load testing. I just want to specify number of columns with type NUMBER, number of columns with type VARCHAR2 etc and the fields should be generated automatically. Also, I will be filling up the tables with random data for which I will be using dbms_random. How can I ac...
Few of questions for bulk-bind and trigger (Oracle 10g) 1) Will row level trigger execute in case of bulk binding ? 2) If yes then, is there any option to surpress the execution only for bulk binding ? 3) If no then, is there a way to execute row level trigger in bulk binding ? 4) Will performance hamper in case row level trigger execut...
I need to modify one procedure from within a package. I need to touch both the declaration and the implementation. As I am maintaining patch files for each modification, I would like the changes to be minimal. Can I update the package with just the changed procedure ( if yes, how ? ) , or do I need to supply the complete package definit...
There are parameterized error messages in Oracle database. For example, there is 01919, 00000, "role '%s' does not exist" in oraus.msg. If one issue some nonsense GRANT ... TO ... %s is substituted by this nonexistent privilege. It is possible to raise exception -1919 and supply some string to %s? Code: not_system_privilege EXCEPTION; ...
Does OR clause suppress Indexes, If yes can someone provide appropriate example? ...
Hello, I'm new with PL/SQL and I need last inserted id in data table after insert statement. Like on MS SQL SELECT IDENT_CURRENT(‘tablename’) Thanks in advance! Goran ...
Is there a way to alter an object type to add an attribute only if it doesn't already exist? I am writing a script that will be run on multiple databases. I'm trying to avoid unnecessary PLS-00410 errors (duplicate fields in RECORD,TABLE or argument list are not permitted). Something like the following: ALTER TYPE employee ADD ATTRIBUT...
If I have table with a Date column (Date field) called created_date, with values like "9/2/2010 5:25:42 PM". I want to select all rows from a start_date to a end_date. However, the end_date may be null. In this case, I want to select all rows where created_date is greater than end_date. ...
I have a query select * from table where name in ('52 T&M', '60 T&M'); The "&" is causing the query to expect a parameter. How do I qualify the "&" in the query to sting so that the query can find string with the "&" character in them? ...
I want to generate composite sequences in the following format: <Alphabet><2 digit numeric code> Each alphabet series will have numeric values ranging from 00 to 99. The initial value will be A00, the subsequent values will be A01, A02 and so on. Upon reaching A99, the next sequence should carry-on to B00. When the "B" series is exha...
Here is my object def: CREATE OR REPLACE TYPE FALCON.contacts AS OBJECT (phone VARCHAR2(50) ,phoneusage VARCHAR2(25) ,phonetype VARCHAR2(25) ,email VARCHAR2(150) ,phoneext VARCHAR2(25) ,anytext VARCHAR2(250)) Here is the table def: CREATE OR REPLACE TYPE FALCON.co...
I have a table with this structure: date colname value ---------------------------- date col1name col1's value date col2name col2's value date col3name col3's value Now I need to convert it into this structure: date col1name col2name col3name ----------------------------------------------- date col1's ...
I have write the following pl/sql program and unable to dectect the error : declare variable a number; b number:=2354; begin b:=:a; end; the error in this is SP2-0552: Bind variable "A" not declared. plz help ... ...
Here is a boolean expression that does the trick: nvl(:new.location != :old.location, (:new.location is null) != (:old.location is null)) But I would like to think there was a simpler expression. Any ideas? ...
When rounding up to 2 decimal places, the value 4.01132141 would be rounded to 4.02 because it exceeds 4.01. How can you do this in PL/SQL? ...
I have a pl/sql procedure with the following signature PROCEDURE pr_log_process_started ( p_process_id IN log_process_status.process_id%TYPE, p_run_id IN OUT log_process_status.run_id%TYPE); How can i make a call to this proc via Hibernate and access the value of the second parameter after the call? ...
I want to create a stored procedure like this: PROCEDURE P_CUSTOMER_UPDATE ( pADSLTable IN Table, pAccountname IN NVARCHAR2, pStatus IN NUMBER, pNote IN NVARCHAR2, pEmail IN NVARCHAR2, pMobi IN NVARCHAR2, pServiceTypeID IN NUMBER, pDate IN DATE ) IS BEGIN UPDATE pADSLTable ...