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
22 views

Replace function with dynamic query on a stored procedure (Oracle) [closed]

I have a field street name that would accept names like : O'connor but I have tested with replace function that works fine, but not sure if this is going to cause an exception with numeric values(int)....
0
votes
0answers
6 views

Efficient Method to retrieve Child Records in PL/SQL based on parent record Result set

I am writing a PL/SQL procedure to delete records from several tables where I have one parent table and several child tables with FK to parent record Id. First will do a select on parent table to get ...
0
votes
0answers
17 views

Declare parameter created within the procedure from the outside parameter sent to procedure

I am not familiar with the PL/SQL so I need a help with this PL/SQL procedure: CREATE PROCEDURE insert( ID INTEGER, XML clob) AS DECLARE txt_XML varchar2(32767); BEGIN ...
0
votes
0answers
64 views

What are PDE files and how can I use them?

I've got a legacy DB which was given to me as a collection of PDE files. I've googled around and didn't come up with any useful information. Saw this question: Import .pde file on PL/SQL Developer .....
0
votes
1answer
34 views

Abstracting away filtering behavior from Oracle SQL Function

I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records. In an application ...
1
vote
1answer
37 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
53 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
0answers
65 views

Can't run procedure on Oracle SQL Developer [closed]

Since yesterday, I have not been unable to run procedure on Oracle SQL Developer. When I click on Run button, progress bar with "Launching" text appears on the right bottom corner of the SQL Developer,...
1
vote
0answers
26 views

EXTPROC issue during 10g to 11g database

I am currently 10g to 11g database migration and trying to configure EXTPROC. EXTPROC32 was configured as part of 10g to execute C++ library and working fine. It appears that 11g does not provided ...
1
vote
1answer
65 views

Oracle - execute DML query as different user

I would like to be able to run DML queries (INSERT,UPDATE,DELETE) by executing a procedure on a given schema. Those queries need to change data on different schemas on the same instance. So, basically,...
0
votes
3answers
41 views

Simple PL/SQL code to throw NO_DATA_FOUND exception

In a recent interview I was asked to write a simple PL/SQL code to throw NO_DATA_FOUND exception without actually raising it from the code. I am wondering what could be a simple pl/sql snippet to do ...
0
votes
0answers
45 views

Using a trigger to check whether a matching record exists

I have a database model set up as in the picture above, modelling a student's enrolment into a unit with an associative entity to facilitate the m:n relationship. I wish to code a trigger that will ...
1
vote
1answer
25 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 ...
0
votes
3answers
89 views

Get current time, on commit, with oracle database

When inserting a row I want to make a column, equal to the current time on commit. If I use default value sysdatetime on the column, it uses the current time at insert (which could be significantly ...
2
votes
1answer
34 views

Return only one row from table with similar values in column that match a criteria

I want to run a query from a table with similar values in the same column that match a certain criteria, but only return 1 row per similar value. Let's say I have this simplified table: Column1 ...
0
votes
1answer
31 views

Return output of Oracle query

How to change below query to see results of my_sql (now I see only query, not it results) in SQL Developer? DECLARE my_sql VARCHAR(1000); BEGIN FOR t IN (SELECT t.table_name, t.owner FROM ...
0
votes
1answer
23 views

Procedures In Oracle [closed]

--Procedures Exercise: create or replace procedure Display is cursor ABC is select empno, ename, sal from emp where deptno=10; emp_rec ABC%rowtype; Begin Open ABC; Loop fetch ABC into emp_rec; exit ...
0
votes
1answer
51 views

How to make a query on multiple columns without writting all possible permutations?

I'm at my first question on stackexchange, as i have a few days since I struggle on this matter: I want to make a complex query(PLSQL) on a table that has col1,col2,col3,col4,col5 having values like (...
0
votes
1answer
54 views

How to declare reserved words as variables in Oracle PL/SQL?

What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration? I realize bind and substitution may be acceptable alternatives for an actual query, but what about ...
0
votes
0answers
14 views

Help with logic with cursor

I have a table called chapter_element, that houses the content for a web ebook. (There are other tables, but this is where the content is contained.)This is the table structure. CHAPTER_ELEMENT ...
0
votes
2answers
60 views

Function Variable Number of Arguments plsql

I am looking to do a function with non fixed number of arguments like decode function. The syntax for the DECODE function in Oracle/PLSQL is: DECODE( expression , search , result [, search , result].....
0
votes
3answers
51 views

Oracle: Not able to get value from All_Synonyms in PL/SQL

Dabatase: Oracle User: Not dba In my oracle database, im able to do the following: SELECT * FROM ALL_SYNONYMS But when i do it in pl/sql, it is able to compile and execute, but the dbms.putline ...
0
votes
2answers
79 views

SQL query for matrix multiplication

I am facing a problem in writing a sql query for multiplication of two matrices. Consider that i have a 3x3 matrices A and B, having the following schema: A(i,j,val) and B(i,j,val) where i int: Row ...
0
votes
1answer
75 views

How to grant privileges to use DBMS_OUTPUT in Oracle?

SET SERVEROUTPUT ON, DBMS_OUTPUT work perfect in my user, System or DBA. But when i create new user, it's not work! How to grant privileges to use this?
0
votes
1answer
61 views

How to select sub query in pl/sql? [closed]

I want to select subquery in pl/sql by count where UPI debit card and VISA debit cards as screenshot below: Please helps to check script code as below : for r in (select BRANCHCODE,BRANCHNAME,cntupi ...
1
vote
1answer
148 views

Run Oracle queries on Sublime Text 3

I'm trying to run some queries in Sublime Text3. I downloaded this plugin, and I set up the settings: { "connections": { "Connection 1": { "type" : "oracle", "...
0
votes
1answer
359 views

Does PL/SQL code run differently in sql plus vs sql developer?

Today, I stumbled upon a question someone posted about the / character and ; character. The Answer doesn't seem to be down-voted, so I questioned it a little more as to why it wasn't down-voted. ...
0
votes
1answer
122 views

How to commit transaction on an after update event trigger?

I'm new here in dba.stackexchange.com, so if I were to make any mistake, my apologies in advance. I kinda confused on how trigger work. So I have a trigger called updcredaccess on table A that ...
2
votes
1answer
58 views

Create a table based on a table depending on the values in the initial table column

I have a table like: +----+--------+----------------+ | id | name | address | +----+--------+----------------+ | 1 | Ivan | Moscow,Moscow | | 2 | Petr | Samara,Samara | | 3 | Grigor ...
0
votes
1answer
79 views

Dynamic query with cursors

I need to execute a dynamic query. I need to extract some values of a table. With these a values I can construct the table names where I must do another query and check every value that it gives me ...
0
votes
1answer
227 views

Executing Shell (bash) from PL/SQL

Does anyone know how to execute a shell script from a PL/SQL procedure or function? Let's say the shell script is '/usr/bin/bash/convert.sh'.
1
vote
2answers
40 views

SQLPLUS: SELECT INTO prompting for variable value

Any idea why this is prompting me for the value? I'm calling the script via batch file: DECLARE precount NUMBER; nowcount NUMBER; BEGIN SELECT COUNT(*) INTO precount FROM ...
3
votes
2answers
933 views

How to check if the table has rows?

I am completely new to PL/SQL. I have written the following PL/SQL script. But it doesn't execute and gives compilation error: set serveroutput on SIZE 1000000; IF EXISTS (select * from my_table) ...
0
votes
0answers
15 views

Dynamic Query with User Input 2 on same Input value

Using PL/SQL I have a Dynamic Query that requires users to provide input. How do I set my where condition on an input to ignore when value is null? I have two condition within the same type Model1 ...
0
votes
0answers
30 views

varray and cursor..need help on counter

DECLARE type ABC is array(10) OF number; v2 abc; CURSOR c1 ISSELECT employee_id FROM employees WHERE rownum<10; v2 :=abc(); counter :=1; BEGIN for i IN 1...10 loop ...
4
votes
2answers
117 views

Selecting from random amount of tables with identical structure

I am programming along the lines of an unfavorable design. I have no influence on the schema because it belongs to a readymade standard software. For simplicity let's say this software stores address ...
0
votes
1answer
54 views

Using exception with raise_application_error

I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this: DECLARE my_fault EXCEPTION; PRAGMA EXCEPTION_INIT (my_fault, -20000); BEGIN ...
0
votes
0answers
32 views

PL/SQL: Temporary Table insertion in User Defined Functions

I have made a Global Temporary Table (We'll call it TT) and I am trying to insert values from a permanent table (We'll call it PT) into TP. Right now I am trying to run this certain setup inside my ...
1
vote
1answer
58 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
78 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; ...
3
votes
1answer
74 views

How to compare dates on minute level in plsql?

If I'm correct, then date comparison on seconds level is as simple as SELECT 1 FROM sample_table WHERE sysdate <= sample_table_date_field; but haven't found any simple or stable solution for ...
-3
votes
1answer
78 views

Problem with procedure in plsql [closed]

Create a stored procedure called UPD_JOBSAL to update the minimum and maximum salaries for a specific job ID in the JOBS table. The procedure should provide three parameters: the job ID, a new ...
1
vote
2answers
78 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
votes
1answer
37 views

function execution related question

Create or replace function funleap(a number) return varchar2 is b2 number(5); b3 number(5); b4 number(5); begin b2:=mod(a,4); b3:=mod(a,100); b4:=mod(a,400); if ((b2=0 and b3&...
-1
votes
2answers
36 views

In out parameter : how to execute [closed]

Create or replace procedure sma11( a in number ,b1 out varchar2) is begin b1:=mod(&a,2); if b1=0 then dbms_output.put_line('b1'||'is even'); else dbms_output....
0
votes
1answer
46 views

function return data type?

create or replace function Total_sal return varchar2 is Total number; begin select sum(salary)into total from employees; return total; end; / BEGIN DBMS_OUTPUT.PUT_LINE(TOTAL_SAL); END; ...
-1
votes
1answer
37 views

how to execute this basic parameter

how to execute this one in plsql.. create or replace procedure p11(a in number, b out number) is begin b:=a*a; dbms_output.put_line(b); end; variable a number; variable b number; what statement I ...
1
vote
1answer
43 views

/ Character in a stored procedure

I am trying to recompile/update a stored procedure in SQL Developer that uses a few division operations to populate tables. A sample section looks like this: Insert Into Production(A, B, C) ...
-2
votes
1answer
60 views

plsql block not executing

I am unable to execute this block. it just compiles. what statement I have togive to see output. where I am doing error ? I clicked on output button also
0
votes
1answer
32 views

OR-01422 ORA-06512

Can someone help to solve this error? I couldn't write pl/sql query of this .thanks. spool 150OMGIddl.sql declare c clob; begin SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'150OMGI')...