Use this tag for DB2 SQL Procedural Language (SQL PL) questions
Questions tagged [sql-pl]
40 questions
2
votes
3 answers
Oracle apex authentication schemes login errors
FUNCTION authenticate(p_username IN VARCHAR2,p_password IN VARCHAR2) RETURN
BOOLEAN
is
l_count integer;
begin
select count(*)
into l_count
from STUDENT, ADMIN, ORGANISATION
WHERE upper(Student.STUDENT_ID, ADMIN.ADMIN_ID, ORGANISATION.ORG_ID) =…
Daniel G
- 47
- 8
2
votes
0 answers
How do you insert a line break into a DB2 SQL PL VARCHAR variable?
It seems like this should be tremendously easy, but I've had no luck thus far.
Stephen Frein
- 103
- 1
- 5
2
votes
4 answers
Differences beween 'set' and 'select into' in IBM DB2 SQL PL
When developing in SQL PL, what is the difference between 'set' and 'select into'?
set var = (select count(1) from emp);
select count(1) into var from emp;
Are they completely equivalent? where can I find documention about them?
AngocA
- 7,655
- 6
- 39
- 55
2
votes
3 answers
Calling a stored procedures within the same schema from a SP
How can I call a stored procedure in the same schema without specifying the full schema name when coding another stored procedure. These are SQL PL procedures within DB2.
First SP:
CREATE PROCEDURE MYSCHEMA.SP_TEST
LANGUAGE SQL
BEGIN
…
Udo Held
- 12,314
- 11
- 67
- 93
1
vote
1 answer
SQL Script using Data Pump Import/Export executing in Oracle SQL developer?
I'm trying to better understand the Oracle Data pump functionality used to export data from one database and import it into a different database in a different location. My goal is to create a SQL script so I can automate most of the work and…
Rocketboy235
- 67
- 1
- 6
1
vote
1 answer
DB2 SQLPL - Unable to exit Infinite loop
I have a stored procedure created to calculate the standard deviation by hand by going through each row in Employees Table in DB2 Sample Database -
However, the procedure is stuck in an infinite loop. I am not sure as to why it is stuck as i…
Prateek Narendra
- 1,837
- 5
- 38
- 67
1
vote
1 answer
How can I run a query with iteration or as a function in SQL Plus/SQL-PL?
New to SQL Plus and a little confused on how iteration and functions work with it. Hoping someone could show me how it would be done with the following script:
set embedded on
set echo off
set colsep ,
set pagesize 0
set linesize 1000
set numwidth…
Karl Johnson
- 111
- 1
- 7
1
vote
2 answers
SQL - Limit the values in an Insert from another table
im trying to insert the following:
insert into TABLEA select b.ID,..... from TABLEB b where code = 'NL'
the problem is that select * from TABLEB where code = 'NL'; returns more than 1 value.
is there anyway to limit that to just 1 value?
i tried…
Borja
- 27
- 6
1
vote
2 answers
DB2 SQLCODE -433, SQLSTATE 22001
I have created a trigger, inside the trigger I am appending lot of varchar and creating a new varchar. if I append too many varchars then I am getting below Sql Exception. (if I append 4 or 5 varchar then I am not getting this error)
I have found…
raghav132
- 23
- 1
- 1
- 6
1
vote
1 answer
Return a result set in a SP from a received result set (cursor)
I would like to know if it is possible to re-return a result set (opened cursor) in a stored procedure, that I received from a previous stored procedure.
Let's suppose
CREATE OR REPLACE PROCEDURE SP2 ()
RESULT SET 1
P_SP2: BEGIN
DECLARE…
AngocA
- 7,655
- 6
- 39
- 55
1
vote
2 answers
How to use dynamic SQL in FOR loop statement?
Is it possible to use dynamic SQL in a FOR statement?
I am using a FOR loop to handle a batch of records from a SELECT statement.
However, what if I want the SELECT statement to be framed at run time, such as the table name may be stored in a…
Aobo Yang
- 157
- 1
- 14
1
vote
1 answer
Testing array membership in DB2 SQL PL
I'm migrating stored procedure code from Oracle to DB2 and I need to find a way to test array membership (Oracle has a MEMBER OF operator).
The stored procedure uses among other things an array of users (received as a parameter from Java code,…
Florent Guillaume
- 8,243
- 1
- 24
- 25
0
votes
1 answer
Cannot alter table (add unique constraint) in a SQL PL store procedure for db2
I am trying to create a store procedure in SQL PL for db2. A new field needs to be added as part of the unique key, so I need to drop the previous uk first, and then add the new unique key. That worked fine for MS SQLServer and Oracle, but I cannot…
Oscar C
- 51
- 1
- 7
0
votes
1 answer
Running SQL PL on DB2 z/OS directly without stored procedures
I'm experimenting with a IBM DB2 on z/OS (using the IBM Cloud Wazi / Virtual server instances (s390x) and running some queries directly using DBeaver and IBM Data Studio.
I want to execute a simple SQL PL script directly from a SQL Script, like I do…
Bert
- 43
- 1
- 4
0
votes
1 answer
DB2 SQL PL CONTINE HANDLER DOES NOT CATCH ERROR
Why does the exception handler not work in the following example ?
db2 => BEGIN
db2 (cont.) => DECLARE YADAMU_INSTANCE_ID VARCHAR(36);
db2 (cont.) => DECLARE FUNCTION_DEFINITION VARCHAR(256);
db2 (cont.) =>
db2 (cont.) => DECLARE CONTINUE…
mark d drake
- 1,280
- 12
- 20