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

2
votes
1answer
76 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
2
votes
1answer
781 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 ...
2
votes
0answers
471 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 ...
2
votes
0answers
530 views

PL/SQL problem statement for homework study [closed]

Good afternoon, I have a PL/SQL (&etc) exam in a few weeks, so I thought it would be good to do a couple of PL/SQL projects/assignments in preparation. Can you give me a good resource of ...
2
votes
0answers
302 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
2answers
361 views

ORM-style server-side programming languages (OO replacement for PL/SQL?)

Good evening, Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]? (for MySQL, PostgreSQL, Oracle or etc.)
1
vote
1answer
21k views

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

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 (...
1
vote
2answers
4k 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 (MAX(...
1
vote
1answer
911 views

Error with Oracle trigger, invalid identifier

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working. DML statement UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT F....
1
vote
2answers
181 views

Functioning of Oracle's Server result Cache

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] : PL/SQL function code can include a request to cache its results. Upon invocation of this function, the ...
1
vote
4answers
6k views

grant all privileges of all tables in database to a user

I use oracle 12c. I try to grant all privileges of all tables to a user. And in future I don't need to add any privileges to that user. I need to do this. I don't want to grant system tables to that ...
1
vote
2answers
716 views

Oracle regexp_like square brackets inside character set

This questions is in regards to Oracle, pl/sql, and the regexp_like function. I am trying to build a character set that will match on all typical special characters. My character set currently looks ...
1
vote
2answers
553 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 ...
1
vote
1answer
30 views

How can I use an array variable inside the IN operator for Oracle SQL?

I declare a type TYPE arr_type is TABLE of VARCHAR2(11 BYTE); and then I initialize it: MY_array arr_type := arr_type();. I insert some varchars into it, and then attempt to use it with an IN ...
1
vote
1answer
24 views

Ranking based off conditional field

I am looking to select only 1 row based off the IMMS_ID field. Which row I select is based off this if statement (excuse the syntax) if (status group based off IMMS_ID) status='DI' select the DI row ...
1
vote
1answer
44 views

Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

Based on the date_entered column, which is a data_type of 'DATE' how would I parse out the data to pull the time. Values in the DATE_ENTERED field (DATA_TYPE is DATE) looks like this 5/15/2015 8:43:...
1
vote
1answer
71 views

How to update table1 field by using other table and function

I have two tables and one function: Table1 contains shop_code,batch_id shop_code| batch_id | regstriy_id 123 | 100 | 12 124 | 100 |13 125 | 100 |12 Table2 contains shop_code,shop_name ...
1
vote
1answer
296 views

How to use variable and drop in test script?

I have to modify certain Oracle Packages. Basically I will be extracting functionality and moving it to C. They provided me with some "Test" scripts that I shoud be able to run to test that my ...
1
vote
1answer
447 views

Pl/SQL web application

I have a PL/SQL web application,got the entire DB dump from remote DB. Its very old application, running on oracle 9i. I basically want to get the application up and running on my laptop,but i am not ...
1
vote
1answer
642 views

Slow join behaviour with 'or' in predicate

I'm faced with a situation which I can't understand and overcome. In short we have left-join query like: select from a left join b on a.key1=b.key1 or a.key1=b.key2 This works VERY slowly, while ...
1
vote
1answer
623 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 ...
1
vote
1answer
581 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
1answer
1k views

ORA-01427:single-row subquery returns more than one row

I wrote in PL/SQL Developer: select * from lc_history2 and I got that error: ORA-01427: single-row subquery returns more than one row Can someone help? It's necessary.
1
vote
1answer
39 views

SP2-0584: EXIT variable was non-numeric error

I'm using following PLSQL loop to delete a large number of records from my table. However it is giving me an error that loop exit condition is not correct. FOR i IN 1..100 LOOP DELETE FROM ...
1
vote
2answers
35 views

Recreate all foreign keys in all tables as deferrable (batch)

I want to make all foreign keys in my database deferrable. But there is no possibility to alter an existing constraint. So I need to drop and add again each foreign key. How to do it automatically?
1
vote
1answer
186 views

How to write a procedure which is replacement of ON DELETE CASCADE?

I want to make write a procedure which will delete rows from parent table as well as child tables. How to achieve that.? In my case I can not to use on delete cascade (No data model changes). If ...
1
vote
1answer
177 views

Batch for PostgreSQL query

1) The pgpass.conf (of PostgreSQL) is: localhost:5432:*:postgres:mypassword I have no experience with batch and I have spent the day searching in the net. I'm using Windows - if that matters. 2) I ...
1
vote
1answer
485 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 ...
1
vote
1answer
58 views

Rollback in Oracle

I am having a problem in Oracle rollback. In my stored procedure I have multiple insert and update statements. If every thing is fine I am sending success message and also I am tracing success status ...
1
vote
1answer
414 views

PL/SQL operating system call

I'm trying to lauch a system call from Oracle Apex, directly or indirectly. It seems to only accept PL/SQL procedures, so I iterated towards this: begin :p3_request_id := sequence.nextval; ...
1
vote
1answer
5k views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
1
vote
1answer
352 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 '|...
1
vote
1answer
645 views

Help with getters & setters in PL/SQL

I am not a db guy, and I have a very beginner's knowledge bout PL/SQL. That being clear now the question: I have 2 tables one file level & another batch level. In a day, only 1 file_id is ...
1
vote
1answer
52 views

Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs

I have a DB table has more than 50 columns. I am querying this table, it should return only one row any time. as below image from sqldeveloper. here I need to construct query/pl-sql block, to find ...
1
vote
2answers
74 views

Varchar2 datatypes are longer than expected

When I run the following script : create table tbl1 ( col1 varchar2(30), col2 varchar2(50), col3 number(4,2) ); desc tbl1; alter table tbl1 modify (col1 varchar2(35)); desc tbl1; drop table tbl1; ...
1
vote
2answers
77 views

Serialize :new in trigger

I have a trigger that needs to create an Oracle Advanced queue message. The payload of that message should contain the new record that was created, that is accessible via :new. What I am looking for ...
1
vote
2answers
87 views

Split varchar2 variable based on vertical line delimiter

I have following plsql code DECLARE v_personal_info VARCHAR2(100); v_name VARCHAR2(100); v_signature VARCHAR2(100); CURSOR c_personal_info IS select personal_info from users where ...
1
vote
1answer
27 views

What Oracle SQL syntax do I need to find transactions that occurred during time intervals of a day for a period of a year?

I am looking at a transactional-level table for which I need to extract all transactions which occurred between certain hours (between 15:00 and 23:59) of a day for the past year. How can I do this ...
1
vote
1answer
624 views

How to drop java source from Oracle properly?

I'm a little stuck here with my Java Source. First I want completly drop it from the database, because I want to recompile it, but I can't drop is, because if I execute this command: drop java ...
1
vote
2answers
1k views

ORA-00942 when trying to select from gv$session inside of procedure

I have been tasked with allowing developers to kill their own sessions in the Development database. I've written a procedure, but for some reason I'm getting PL/SQL: ORA-00942: table or view does not ...
1
vote
1answer
190 views

How do you capture the output message from a successful MERGE statement when using it in a procedure?

Sorry if this sounds like a rather simple question.. When using the console, normally the statement outputs something similar to X records merged How do I capture this output into an out variable ...
1
vote
1answer
998 views

Pass the unix variable to the ddl command inside a pl/sql block

I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from ...
1
vote
1answer
1k views

Insert with different key values to avoid contraint violation

I have a set of Inserts traced of a database and I want to issue them into another similar one. The problem is that I'm getting contraint violation errors due the similar database already has the keys ...
1
vote
1answer
148 views

12c Shell to Linux

I am replacing two Oracle 11g servers with 12c TL/DR - when shelling out to a run an O.S. file, the PATH environmental variable is not being set. On the old servers we use an external library to ...
1
vote
1answer
418 views

How to use the dbms_sql to get the analyze for insert into statement

declare v_cursor number; stmt varchar2(2000); s varchar2(40); cursor cc1 is select voc.SQL_ID from v$open_cursor voc left join v$sql vs on voc.SQL_ID=vs.SQL_ID where voc.SID in(select sid from v$...
1
vote
2answers
833 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
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'); ...
1
vote
1answer
626 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 - 5....
1
vote
1answer
320 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
1k 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 can'...