Procedural Language/Structured Query Language is Oracle Corporation's procedural language extension for SQL. Questions about PL/SQL should probably be tagged "oracle" as well.

learn more… | top users | synonyms

1
vote
1answer
6k 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; ...
1
vote
1answer
103 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'); ...
1
vote
2answers
3k views

Why does dbms_scheduler max_run_duration not raise event JOB_OVER_MAX_DUR?

Starting from this question and this example I've tried to create an event based dbms_scheduler job, which waits for event JOB_OVER_MAX_DUR raised from jobs, which exceeded their max_run_duration. ...
1
vote
1answer
507 views

Improve performance for my plsql block

First of all let me make kind of a disclaimer here: 1) i'm a developer, not an administrator; 2) and i dont have admin rights on my database. 3) table specs : -- oulp_path_attendance_base_t - ...
1
vote
1answer
242 views

Update statement errors, parenthesis, failed validation ORACLE

i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value. Original statement The statement below works flawlessly, ...
1
vote
2answers
858 views

Is Using Execute Immediate Bad Practice in Oracle?

So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data. As the sequence ...
1
vote
1answer
139 views

how to write pl/sql named programme for this [closed]

I have a table M_PARAMETER which has a column MMRUN. When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification. ...
1
vote
0answers
19 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
1
vote
0answers
19 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
1
vote
0answers
84 views

How to Avoid Mutating Error in Oracle

I am using Oracle 11g. I have a this trigger: create or replace TRIGGER TRG_UPD_RAPRJCT11 AFTER UPDATE OF CURRENT_STATUS ON AGL_RAPROJECT FOR EACH ROW BEGIN IF (:OLD.CURRENT_STATUS='Review2' and ...
1
vote
0answers
32 views

Allowing users access to SQLPLUS and to compile PLSQL [duplicate]

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
1
vote
0answers
69 views

Adding custom meta data to a table in oracle

I'd like to know if there's a possibility to add some custom metadata to a oracle database table. The problem at hand is the following: I'm importing a table from another DB. It might happen that I ...
1
vote
0answers
161 views

PLSQL Compilation error for other users/

I created a plsql procedure that queries the database and dumps the result onto a CSV file. I am an application developer and I have dba rights to my oracle database. I'm able to compile and run the ...
1
vote
1answer
163 views

Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

I have the following tables: MySQL table: jti_member_interact CREATE TABLE `jti_member_interact` ( `INT_MEMBER_ID` int(11) NOT NULL, `INT_ID` int(11) NOT NULL AUTO_INCREMENT, `INT_SOURCE` ...
1
vote
1answer
100 views

How to create a function(table_name) that return a collection in Oracle?

I need to create a function in Oracle that accepts a table name and return a collection the content of which is based the table that accepted. I've been doing some search, many examples are of the ...
1
vote
0answers
122 views

dbms_audit_mgmt.clean_audit_trail not purging

my current database version is 11.2.0.3.0. I'm trying to purge my audit trail with a procedure. In my procedure I got this part of code: SYS.dbms_audit_mgmt.clean_audit_trail( audit_trail_type ...
1
vote
1answer
63 views

How can I send an email when objects get invalidated?

I have couple stored procedures and a package. Whenever this stored procedure and package goes invalid, I need to send an email. How can I achieve this? Can you please give me an idea how this ...
1
vote
0answers
148 views

Redefining a table with a user-defined type using DBMS_REDEFINITION

The application which we developed has one schema, USER1, which has tables, types, packages etc. I have to move some of the user-defined types to a different schema, The reason for doing this is to ...
1
vote
0answers
269 views

How to display '0' where no data found in PL/SQL Oracle Reports

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 ...
1
vote
1answer
331 views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
1
vote
0answers
2k 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 ...
1
vote
0answers
529 views

How can one reliably update a LOB column with data from another LOB column in Oracle?

I have tried: UPDATE "SCHEMA".TABLE SET LOB_COLUMN = (SELECT LOB_COLUMN FROM "SCHEMA".TABLE) I have tried: CREATE OR REPLACE PROCEDURE COPY_LOB_DATA IS OLD_BLOB BLOB; NEW_BLOB BLOB; ...
1
vote
0answers
275 views

Which are the Oracle basics required for advanced Oracle.DataAccess usage? [closed]

Successful use of [Oracle.DataAccess.Client] is based on proper understanding of three separate parts of Oracle: SQL PL/SQL sql*plus Here on dba.exchange the following questions target this topic: ...
1
vote
1answer
79 views

Oracle PLSQL code recovery

I cannot connect to my database as admin, even using connect descriptor neither via RMAN. I am getting TNS-12514 error. I consider to install new instance. Questions: 1) Can I restore/recover PLSQL ...
1
vote
1answer
64 views

Mutating Error , How to solve it

CREATE OR REPLACE TRIGGER LOAN_RP AFTER INSERT OR UPDATE ON FUND_DISBURSE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE V_FA FUND_DISBURSE.FUND_AMT%TYPE; V_RC NUMBER (10); ...
0
votes
3answers
59 views

Allowing users to compile / run PLSQL and SQL

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
0
votes
2answers
382 views

table does not exist

CREATE OR REPLACE PROCEDURE ProcedureX() TYPE AVERAGE_COUNT_TBL IS TABLE OF INTEGER; AVERAGE_COUNT AVERAGE_COUNT_TBL; BEGIN INSERT INTO AVERAGE_COUNT VALUES(10); END ProcedureX; It ...
0
votes
2answers
138 views

Cycle or something similar in PL/SQL

Quite often it is necessary to do some repetetive operations / few line queries against set of objects. Let's say you have a list of accounts to unlock via alter user %username% account unlock and ...
0
votes
2answers
336 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
0
votes
2answers
56 views

Duplicate data in a table given table name

For a performance testing application we need to have lot more data in our tables than what we have now. How do I duplicate the data in a table. In the process of duplicating I also need to set new ...
0
votes
2answers
68 views

Decode Function in Oracle

I have a table named as transaction,in that table trans_type column is there.I want to check the trans_type like bellow if(trans_type = 'AR','AD','AF','AG') then trans_type:='A'; elsif trans_type = ...
0
votes
1answer
321 views

converting varchar to number while passing to IN clause

I am really stuck here.. Given below is the miniature of what I am trying to achieve. set serveroutput on; declare rajesh varchar2(20); x number(10); begin select wm_concat(stocks_id) into rajesh ...
0
votes
1answer
135 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 ...
0
votes
1answer
672 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 ...
0
votes
2answers
635 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 ...
0
votes
1answer
338 views

calculate and return the total estimated chargeable cost

I had a question regarding a small task I've been assigned. Below I have pasted the question and a screenshot of the database layout. Now I just don't understand what exactly I'm meant to create, a ...
0
votes
1answer
59 views

Problem with procedure performance

I'm having problem in Oracle, where I have two procedures that are executed one after the other. Where the first procedure, I have a cursor that does insert into a table. This insert has about 30 ...
0
votes
1answer
860 views

How to reference a table from another schema inside a package?

I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema. The package has a procedure that uses a cursor. When I create the package I get ...
0
votes
1answer
2k views

ORA-01410: invalid ROWID

I get ORA-01410: Invalid ROWID error when executing the following piece of code: DECLARE CURSOR c1 IS SELECT e.empno, e.ename, e.sal, d.dname ...
0
votes
2answers
212 views

How to fetch file path from a folder through PL/SQL

We upload files on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible. If possible, please suggest what ...
0
votes
1answer
272 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 ...
0
votes
3answers
1k 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 ...
0
votes
1answer
7k views

Oracle: Output not shown even if SERVEROUTPUT variable set to ON

I was wondering why the output is not being shown even if I set the SERVEROUPUT variable to ON. I have the following annonymous block code: DECLARE v_rec_count number:=0; BEGIN for rec in ...
0
votes
1answer
46 views

Alter trigger Oracle

Does alter trigger hang if a table is locked by some DML operation ? What kind of exception does it throw? Resource busy 0054 or some other exception...? I have tried to reproduce this scenario but ...
0
votes
1answer
100 views

Why is there a difference in the character limit for VARCHAR2 in SQL and PL/SQL?

While working with some large scripts I encountered errors related with the 4000 character limit that VARCHAR2 has in SQL but not in PL/SQL. As explained here the VARCHAR2 datatype has a 4000 ...
0
votes
1answer
131 views

How to create script file with PL/SQL?

I have to update several objects in many instances. In order to achieve that I am using DBMS_METADATA to create the DDL statements. So far I have the DDL statements and I save them in a TABLE. I ...
0
votes
1answer
175 views

Compare two big tables in PL/SQL

I need to implement comparing 2 tables by set of keys (columns of compared tables). By this comparing i should check equal records (equal = equal by set of defined keys (columns in these input ...
0
votes
1answer
29 views

Enumerate Oracle tables in PLSQL

How do you run a query to list all tables, or all tables where tableName like '%abc%'. Just poking around, I tried the following with no success: select * from SYS.TAB$ select * from SYS.OBJ$ ...
0
votes
2answers
168 views

Is there anyway to search PL/SQL reserved words with contains function?

Is there anyway to search PL/SQL reserved words with the contains function in the Oracle? We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions ...
0
votes
2answers
94 views

How to detect in which schema a procedure is called?

I'm struggling with a problem in Oracle 12.1 regarding to procedures. We are using a data isolation approach based on schemas. We use one schema as PUBLIC and each project has exactly the same ...