Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. IBM supports PL/SQL for DB2 since version 9.7. Questions about PL/SQL should probably be tagged "oracle" or "db2" as well.

learn more… | top users | synonyms

0
votes
1answer
78 views

Dividing a column into three columns [duplicate]

I have this table: Name Address Code James CT Homeaddress Mark mark@goole.com Email John John@hotmail.com homeemail ... This is ...
4
votes
2answers
1k views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
2
votes
1answer
169 views

sql query - table@string meaning

I am trying to understand a query . When I run the below query , no rows are fetched. select * from Table But when I run the query , a result is returned having multiple rows. select * from ...
4
votes
1answer
3k views

Does SQL Server 2008 have an equivalent to Oracle's “table%rowtype” syntax or (Data Type)?

In Oracle PL/SQL scripts, you can declare a variables using the syntax tablename%rowtype for its type. This will make that variable have the same "type" as a row of that table (i.e. a it will be a ...
0
votes
0answers
471 views

Creating and tuning a materialized view in oracle for use by Cognos BI

I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to select its contents for fulfilling the incremental load in a timely ...
0
votes
1answer
817 views

Call pl/sql function and discard result

I'm calling a function from pl/sql, but I do not require the result in this case. Is there a cleaner way to call this function without using a dummy variable? Works: junk := performWork(); Does ...
3
votes
1answer
591 views

Are there tips for writing Oracle DDL Triggers for auditing? Or should I just use AUDIT?

I've been tinkering with writing a trigger to log my table creation and manipulation for later tracking and review. The challenge is that my trigger logic is stored in the same schema on which I've ...
0
votes
1answer
2k views

How would I store the result of a select statement so that I can reuse the results to join to different tables?

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor. Below is some pseudo code, in this example I have ...
0
votes
1answer
6k views

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

I need to figure out a way to embed an Oracle PL/SQL sub-procedure call in a SELECT statement, within another procedure in the same package. I am using SQLDeveloper 3.0 with an Oracle 11g database. ...
3
votes
3answers
3k views

how to change a user password while checking the current password in oracle?

In my application I have a change password form which users enter their current password and the new password. Then I change their password using the following command: alter user user_name ...
0
votes
2answers
6k views

How to forcibly flush the undo log/tablespace of UNDOTBS1

I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me ...
1
vote
2answers
780 views

Jobs view is not visible to read-only account users

I am the admin of one of the database, and I have created a view in SYS schema using below code which returns the status of the running jobs: CREATE OR REPLACE VIEW CAF_DB_REFRESH AS SELECT * FROM ...
1
vote
0answers
4k views

PL/SQL extremely slow FETCH performance

I have a stored procedure that includes (among many other things) a dynamic query with a LOOP and a FETCH. The fetch has to potential retrieve a large amount of data as one column contains spatial ...
2
votes
1answer
10k views

Passing parameters to a procedure executed by DBMS_SCHEDULER

I have a scheduler program that runs a stored procedure that requires input parameters. BEGIN DBMS_SCHEDULER.DROP_PROGRAM (program_name => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'); END; ...
0
votes
1answer
3k views

Error in trigger: table is mutating

CREATE OR REPLACE TRIGGER "TRANSACTION" before update of totalunitsbought on SIPHOLDER referencing new AS NEW old AS OLD for each row begin declare mportfolioname varchar2(20); ...
2
votes
1answer
2k views

Testing AFTER INSERT Trigger

I have the following table: CREATE TABLE Train( Train_No integer PRIMARY KEY, Loco_No integer REFERENCES Locomotive(Loco_No), Back_Loco_No float REFERENCES Locomotive(Loco_No), ...
0
votes
1answer
143 views

Combine multiple queries into single query

I am trying to run the following query: SELECT Freight.Freight_No, Freight.Available, Locomotive.Loco_No, Locomotive.Available, Driver.Driver_No, Driver.Name, ...
0
votes
1answer
211 views

Is the data fetched by an explicit cursor immutable in PL/SQL?

This seems like an obvious question, but Googling has so far failed me... Say I have a view which delineates updates that I need to apply and, to this end, I have an SP that opens the view as a ...
1
vote
1answer
346 views

Dynamic where condition in a stored procedure

I am using Oracle 11g. I am trying to get the table data with this procedure: CREATE OR REPLACE PROCEDURE test(p_table IN varchar2) IS v_sqltxt varchar2(4000); BEGIN v_sqltxt:='select count(*) from ...
3
votes
1answer
1k views

PLSQL : DBMS Jobs - parallelization?

I am looping over a cursor and executing a stored procedure; how do I parallelize this process through dbms.jobs? for rec in select column from table1 loop execute stored_procedure( rec.column ); end ...
0
votes
0answers
731 views

Error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

i've problem here. i wanna create a Stored procedure like below, but i encountered error PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records. create or ...
0
votes
1answer
945 views

Oracle's UTL_FILE

I am learning Oracle pl/sql by myself. I need to create a procedure that exports a table to a csv file. For that I am thinking of UTL_FILE.Is there any good book or a site to get me started? I did ...
2
votes
0answers
462 views

Transform XPath map into XML document using relational data [closed]

Background Most modern databases have XML functions that can be used to extract data in an XML format. I want to avoid the task of manually calling XML functions to extract the data. This problem ...
1
vote
1answer
118 views

double date conversion when adding partition

I want to create a procedure that adds new partition in a table partitioned by time. For this purpose I use following commands: s_until := to_char(sysdate+1,' SYYYY-MM-DD HH24:MI:SS'); ...
4
votes
2answers
6k views

Reset every Oracle sequence to the MAX value of its target table PK?

I finally got around to migrating from MySQL to Oracle and was pleasantly surprised just how well of a job the SQLDeveloper Migration tool did. It even took my AUTOINCREMENT fields and created ...
0
votes
1answer
636 views

PL/SQL for Updating 1 Column in Table A Where PK = a row in table B

Using an oracle DB, 11g r2, I need to write a pl/sql which updates one column in table A where the PK exists in table B, with commits ever ~50,00 rows. 3200000 rows are involved in the update, and ...
0
votes
1answer
318 views

Invalid rowid error

I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations. declare cursor update_lock is select empno from emp where deptno=&no for update of sal; num ...
0
votes
2answers
2k views

PLSQL: BEFORE INSERT Trigger

I am attempting to write my first trigger statement. I am trying to create a trigger that performs a SELECT to get my MAX_TOW_WEIGHT for a LOCO_CLASS, then compare that weight to the TRAIN_WEIGHT. If ...
4
votes
2answers
17k views

Why can't we write ddl statement directly into the PL/SQL block

Why can't we write ddl statements directly in PL/SQL block, for example when i write CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table table_name; // error END test; / But, CREATE OR ...
3
votes
2answers
2k views

DB2 UPDATE TRIGGER

I am trying to create a trigger in a DB2 database that runs on the update of a column in one table, and then fills in another table with certain values. For example, there is a power unit table with ...
1
vote
1answer
594 views

need to use PLSQL to insert distinct rows

I need to take some data from Table A, use some logic, and then insert one or more rows into Table B. I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic ...
2
votes
1answer
4k views

how to create and insert into a table in an anonymous block

I have PL/SQL anonymous block that creates a table (using execute immediate) and then inserts data into the table. When the block compiles, it gives me an ORA-00942: table or view does not exist ...
1
vote
2answers
3k views

Oracle insert max from two date

I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like Insert into MYTABLE (DATE_INSERT) VALUE ...
0
votes
0answers
4k views

12154 error when using PL/SQL Developer

I have installed a few versions of Oracle on my machine. I have tried 64 bit and 32 bit versions. I have installed 11G. I need Oracle 10G 32 bit. Using the following guide I removed all versions of ...
2
votes
1answer
2k views

Oracle Packages, Procedures and Functions Not Respecting Roles

When you create a package, sproc or function in one schema (say, SCHEMA1) that accesses objects in another schema (SCHEMA2), even when SCHEMA1 has appropriate permissions to the respective SCHEMA2 ...
6
votes
1answer
961 views

Package state not re-intialized on raise_application_error

When a package has state and the header is changed, the first call gets an error stack something like this: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of ...
4
votes
2answers
292 views

Is there a bug with PL/Scope in combination with associative arrays?

I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something. I have the following package create or replace package tq84_pkg_c as ...
3
votes
1answer
2k views

halt on error in a sqlplus script

I am deploying pl/sql code using several sql files that are called with @@file. If a package got a compilation error the script continues to the end. Is there a way to stop on every compilation ...
2
votes
1answer
3k views

How to check parameter value existence in a procedure?

The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking: ...
2
votes
1answer
748 views

How to speed up a query with a lot of subqueries

I have a query which uses a series of functions to return the status of each document for a loan. SELECT loan_number, borrower_name, get_application_status(loan_number, ...
1
vote
1answer
563 views

DB stored procedures: how do they help increase security in web systems?

The usage of Oracle PL/SQL procedures for controlling data access is often emphasized in PL/SQL books and other sources as being more secure approach. I'v seen several systems where all business logic ...
1
vote
2answers
549 views

How to isolate general Oracle 10g issues through SQL

Background: I've been working on a performance analysis on a system with major performance issues on a Oracle 10g server. Much of the SQL is badly written, or at least not written with performance in ...
0
votes
1answer
1k views

Massive update to Clob fields oracle 10g

I need to remove a string of characters from 2000+ clobs fields in oracle 10g. How would I do this in PL/SQL? For example, in table t1 there is a clob field called VBase that needs to have the ...
3
votes
2answers
37k views

How to store single result in variable and reuse it in a query (Oracle)?

I have a query like SELECT id FROM xyz WHERE ...; which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a ...
0
votes
1answer
2k views

Find ID columns with null value in oracle database

I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database Any suggestion?
2
votes
1answer
205 views

PLSQL Package - Catching Errors in procedure (Oracle)

I have a problem with my package (and procedure) not catching exceptions. I have the below code: CREATE OR REPLACE PACKAGE process_orders IS PROCEDURE add_order ( order_num NUMBER, ...
5
votes
3answers
6k views

Using the same sequence twice in a PL/SQL statement

I wrote the snippet of PL/SQL today: declare first_id number; second_id number; begin insert into table (sort_nr, text_id, unit_id) values(..., table_seq.nextval, table_seq.nextval) returning ...
7
votes
3answers
21k views

Why use explicit cursors instead of regular loops?

I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data: for i in (select * from STUDENTS) ...
3
votes
1answer
957 views

Where can I find a syntax reference for Oracle SQL Developer's “Generate DB Doc” function?

Oracle SQL Developer (I'm using v3.2) has a feature called "DB Doc", which generates documentation for database objects. I mostly want to use it to generate documentation for my stored procedures, ...
0
votes
3answers
3k views

ORA-01562: failed to extend rollback segment number 11

I´m trying to execute this query into a SP DELETE FROM table SC WHERE SC.evaluationMonth= 'Some month'; COMMIT; But for this month there are something like 10 million records. So I get the ...