A PL/SQL package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use this tag for questions referring to the PL/SQL packages usage and behaviour.
Questions tagged [plsql-package]
240 questions
0
votes
1 answer
DBMS_SQL.COLUMN_VALUE throwing - > PL/SQL: numeric or value error: character string buffer too small error
I am trying to dynamically execute a SQL in anonymous block and fetch the records using ':::' delimiter for external program.
It is failing when more than 13-15 records are fetched.
declare
mysql varchar2(3000) := 'select…
Hari
- 47
- 4
0
votes
1 answer
How to get output REFCURSOR in package oracle with golang ("github.com/sijms/go-ora/v2")
i have a package in oracle
CREATE OR REPLACE PACKAGE PKG_TEST IS
TYPE REFCURSOR IS REF CURSOR;
PROCEDURE PROC_GET_DUAL(P_CUR OUT REFCURSOR);
end PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST is
PROCEDURE PROC_GET_DUAL(
…
htan
- 79
- 6
0
votes
0 answers
ORA-29279: SMTP permanent error: 503 5.5.2 Send hello first [LO4P123CA0158.GBRP123.PROD.OUTLOOK.COM]
I am trying to trigger email from PlSql using SMTP server and getting this error.
ORA-29279: SMTP permanent error: 503 5.5.2 Send hello first [LO4P123CA0158.GBRP123.PROD.OUTLOOK.COM]
Below is my code.
l_mail_conn := UTL_SMTP.open_connection(host,…
Kushal Shah
- 3
- 3
0
votes
1 answer
PLSQL PROCEDURE. I AM NEW TO TO PLSQL
Create a procedure ADD_DEPT. This procedure will take 3 IN arguments DEPTNO,DNAME,LOC. Insert these values into the DEPT table. Handle the exception if the user tries to insert a duplicate row using User Defined Exception Handler.
Ishika
- 1
0
votes
3 answers
How to catch error and store to Error table | PL/SQL |
I have been writing a code to achieve this but fully stuck
Need you helping hands for this scenario
I have created a application with Oracle APEX with interactive report and form in my page
My procedure to sent mail
CREATE OR REPLACE PROCEDURE…
kiric8494
- 195
- 1
- 7
0
votes
1 answer
Get Output of stored procedure using PutSql in Nifi
I have to get the output of Oracle stored procedure in Nifi.
I've tried PutSql with the following sql statement :
declare out VARCHAR2 ; begin PKG_TEST.P_TEST(1,out); end;
It works fine but it just executes the script.
How can I get the value of…
KnowledgeSeeker
- 77
- 1
- 12
0
votes
1 answer
Pipelined function gives "PLS-00630" for one example but not for another similar example
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
What am I missing here? When I try and create two different but similar (in my mind) packages I get an error for one but not the other. What is the difference in the…
0
votes
0 answers
Granting execute privileges on a procedure, but only inside a trigger
I need for a non-admin user to execute a particular procedure which fills an application context, but for security reasons, I want him to only be able to execute it inside a trigger, and not outside it.
Is that possible in some way?
Thanks
Unziello
- 103
- 8
0
votes
1 answer
I'm trying to use a local variable's value in another query in PLSQL package but every time the 2nd query is returning null value
BEGIN
--{
SELECT CONCAT(CONCAT('''',REPLACE(VALUE_1, ',', ''',''')),'''') into glv_v_PROC_FEE
FROM TABLE_A ;
EXCEPTION WHEN NO_DATA_FOUND THEN
glv_v_PROC_FEE := ' ';
…
Abhishek Kumar
- 3
- 1
0
votes
2 answers
Managing version of pl/sql procedures/functions
I am searching for best practices for supporting multiple versions of procedures/functions.
For example, I have a procedure that generates complicated json output with oracle apex packages, which is used by application to draw some front-end.
And…
John Doe
- 57
- 8
0
votes
1 answer
Display the employee detail with department name of given Fname if he is accountant
input from
tblemp(empid number primary key,
fname varchar2(20),
lname varchar2(20),
jdate date, salary number,
mid number,
post varchar2(20),
did varchar2(10) references tbldept1 (did)) ;
tbldept1(did varchar2(10) primary key,dname…
gazal gauswami
- 1
- 1
0
votes
2 answers
Issues with using function to validaate a value using oracle regex_like
I have a PL/SQL funtion where I validate a field value using Regex.Below is the if statement I have in the PL/SQL function. where Im trying to verify if the Input is alphanumeric with atleast one number and one…
vr3w3c9
- 1,118
- 8
- 32
- 57
0
votes
0 answers
Evening_package is inconsistent in running
We have evening package that is scheduled to run during evening and usually completes by midnight.
We recently migrated our Oracle ODA's to Exadata cloud and did containerization and oracle patching. After our database migration it stopped the next…
Khush11
- 1
0
votes
1 answer
How to secure my oracle apex source code deployed user's machine
I created Oracle Apex application 21.1.7. If I deploy my app to a user's cloud ATP database, how can I prevent the db admin from viewing the application's source code?
ie. Packages deployed, apex views (APEX_APPLICATION_PAGE_DA,…
1L0pth
- 58
- 4
0
votes
1 answer
Create a PL/SQL block to display all the department names from the Department table using cursors. The department names should be displayed in
Create a PL/SQL block to display all the department names from the Department table using cursors. The department names should be displayed in ascending order.
Column Name 1 - DEPARTMENT_ID NUMBER(5) PRIMARY KEY
Column Name 2 - DEPARTMENT_NAME…
Ram Kumar Shukla_016
- 11
- 1
- 4