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

-1
votes
0answers
27 views

Currently running TOP Sqls [closed]

I am trying to make Top 10 Sql that currently running. I have 2 queries which makes this but i could not understand the logic. I hope you guys can help me to make it. First SQL is ; select ...
0
votes
1answer
823 views

How to select data out of an Oracle collection/array?

Due to needing some complex data manipulation and not being able to use PHP, I crafted a procedure (not stored, it's just run when we run the query) in Oracle to manipulate the data and store it in an ...
4
votes
2answers
2k views

How to separate out individual sql statements from Oracle SQL script for execution from java code

I have Oracle DB 11g Enterprise Edition and I want to execute sql script from my java code. I am reading this sql script from a .sql file and the script contains statements such as create table, ...
0
votes
3answers
44 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
1answer
118 views

fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

As i am from mathematics background and require to do some huge permutations to start in my work kind help. If i have a table by name sample1 with n columns what is fastest way to get all the ...
2
votes
1answer
737 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 ...
3
votes
1answer
183 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 ...
0
votes
2answers
48 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 ...
3
votes
1answer
613 views

How to avoid hardcoding a database link name in a package body

I have a PL/SQL package that copies data from a remote DB2 database using a database link. To keep the package configuration-independent I wrapped the remote table in a view. But a single field in a ...
1
vote
1answer
331 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` ...
0
votes
2answers
181 views

ORA-01410 Error

I am getting ORA-01410 and ORA-06512 for the following block of code in a trigger im firing for deletion of a record in my table. Are the calls correct ? getlong ('xxx', 'YYY', :OLD.ROWID), getlong ...
0
votes
1answer
41 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
225 views

How to call an oracle stored procedure in sql developer tool

I have written an Oracle procedure like below: create or replace procedure Sp_emp(cv_results out sys_refcursor) is begin open cv_results for select * from employee; end; / ...
0
votes
1answer
54 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 ...
0
votes
1answer
422 views

PL/SQL recursive lookup with cursor

I basically have a need for recursing through data in my Oracle table so I can find related rows and send them out. It is a single table, a single ANSI SQL query seems not possible due to turin ...
4
votes
1answer
4k views

Split comma separated entries to rows

I have a Table like this: | ID | OtherID | Data +--------+-----------+--------------------------- | 5059 | 73831 | 5103,5107 | 5059 | 73941 | 5103,5104,5107 | 5059 | 73974 | ...
1
vote
1answer
42 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", ...
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
96 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
74 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 ...
1
vote
1answer
53 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
78 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'.
0
votes
1answer
47 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 ...
1
vote
2answers
35 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
889 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
13 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
27 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
113 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
36 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
17 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
47 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
66 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; ...
0
votes
1answer
3k 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 ...
3
votes
1answer
70 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
76 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
71 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 ...
-1
votes
2answers
35 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 ...
0
votes
1answer
45 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
40 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
43 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
21 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 ...
0
votes
0answers
38 views

Procedure stuck at a bulk collect

Here's a procedure I'm working on. It's called from another procedure, which uses it in a nested BULK_COLLECT loop on every entry in another table. It gets stuck on "FETCH uniquephysicians BULK ...
3
votes
0answers
42 views

UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?

I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible ...
0
votes
1answer
73 views

Execute immediate call causes 'variable not in select list' error

Execute immediate call causes 'variable not in select list' error hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code: set ...
1
vote
2answers
30 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?
3
votes
2answers
168 views

Pass (comma separated) -yet single parameter to cursor

I have to get selection for a given string (with comma separated values) passed to the cursor. There can be multiple values that could be passed via single variable. My code is somewhat like: CURSOR ...
0
votes
1answer
49 views

How do I iterate over 1000 records in a particular order

I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent ...
4
votes
1answer
55 views

Encrypt data with DES_CBC_PKCS5 in DB2

I am using encryption algo DES/CBC/PKCS5Padding. How do I encrypt data using this in DB2? In Pl/SQL I used DBMS_CRYPTO package with DES_CBC_PKCS5 from DBMS_CRYPTO Block Cipher Suites - ...