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
2 answers
can we use default in a variable with function call in plsql?
I have a package, inside i declare g_suppress_all_xlog BOOLEAN := fnc_Suppress() DEFAULT NULL;
FUNCTION fnc_suppress
RETURN BOOLEAN
IS
v_ret BOOLEAN := FALSE; -- assume NO
v_suppress VARCHAR2…
selva
- 66
- 5
0
votes
1 answer
Split the data across multiple columns in student Table using pl/sql or sql
Please help me. Currently I’m working on pl/sql procedure. In sport table has ID and for same id has multiple codes. I need to split these multiple codes and insert them to student tables as code1,codes2,code3.
Source table
ID CODE
----------
222…
Anu
- 11
- 1
0
votes
0 answers
for-loop is not working for the below record type variable in select statement
Please refer to the below code and my expectation is to loop the select statement below which needs to be passed in to the API as record type which is already declared.
BEGIN -- ap_vendors_pkg.update_row
fnd_file.put_line(fnd_file.log,'8 Update…
Prathyusha
- 1
- 1
0
votes
1 answer
Nested decode in where clause
I have a cursor that fetches address for the id passed, based on the latest activity date.
CURSOR get_address_upd_c (
id t1.id%TYPE
) IS
SELECT street_line1,
street_line2,
city,
stat_code,
zip,
…
arsha
- 67
- 6
0
votes
1 answer
Dynamic argument binding in dynamic stored procedure call oracle
I have requirement not only to call a stored procedure dynamically but also to pass the parameters dynamically to that stored procedure.
Arguments are coming from the ALL_ARGUMENTS table which is Oracle data dictionary table. These arguments are…
0
votes
1 answer
Oracle PL/SQL Trigger before/after UPDATE to identify ONLY the columns have been modified in a table
I am trying to develop a trigger to capture the details on update on table - TEST_TABLE. I have created an audit table to capture the details, but need some way to pick up ONLY the modified column and old/new values. Have written a procedure…
Aftab
- 83
- 2
- 6
0
votes
1 answer
How do I find the last DDL of a procedure which is inside an ORACLE package?
I know how to find the last DDL for standalone procedures. Is there a way to do it for procedures written inside a package?
spiritedsea091
- 13
- 3
0
votes
1 answer
Error: Cant create procedure with select in PL/SQL
I am creating a procedure in a package and I want to make a simple select but I get this error.
create or replace PACKAGE PK_MAC AS
/* TODO enter package declarations (types, exceptions, methods etc) here */
PROCEDURE PR_PRUEBAS (
…
Myke Andres
- 25
- 5
0
votes
1 answer
how to Parameterized column name and table name inside package body oracle
--PACKAGE CODE-- the subquery with min aggregate funtion does not work. it can not find table. it is not very clear to me how to paremeterized the column and table name to make it work.
CREATE OR REPLACE PACKAGE TEST_PKG IS
PROCEDURE SP_METRICS…
Isl Rodriguez
- 3
- 1
0
votes
1 answer
How to output different values in a single record
I have a value in a string form. The field name is CF$_LMWEQUIMENT and the output is a single string separated by ^. Here is an example:
^All_Equipm^Consulting^DITCHCLEANER^Friction_M^
I need to output the real values this way of the the database…
JUANLUISSG
- 63
- 1
- 8
0
votes
0 answers
Performance issue on two different instances within the same database
We are running into a performance issue while writing query data to a file from two different instance on same database.
ON DB1 with Instance A with some directory path data writing to file is taking 2-3 min, and Instance B with different directory…
Vikas
- 107
- 7
0
votes
3 answers
WHILE & FOR LOOP
I have question while processing records in two different looping method i.e WHILE & FOR LOOP please refer to following codes
DECLARE
TYPE rc_emp_type IS RECORD ( empno NUMBER,ename VARCHAR(30),sal NUMBER, comm NUMBER);
TYPE rc_emp_tab…
Vikas
- 107
- 7
0
votes
1 answer
I'm trying to do an exception with raise
I'm trying to make an exception with RAISE in PL / SQL. I did the script but when I want to run it does not show me one of the two messages and it just gives me 'anonymous block completed'.
Accept cititor prompt 'Introduceti un cititor';
…
ferye21
- 13
- 1
- 7
0
votes
1 answer
SQL Procedure compiling errors
we have two tables : Account and Client
CREATE TABLE Client ( NumClient NUMBER(3) NOT NULL PRIMARY KEY,
ClientName VARCHAR (25) NOT NULL,
City VARCHAR (25) NOT NULL
);
…
user8590010
0
votes
2 answers
how to generate a liste from a table by plsql function?
I need to create a function to generate a list from a table by a select query. First, I created a package and function to generate a table but I get ERROR.
CREATE OR REPLACE PACKAGE test AS
TYPE date_record IS RECORD(
list_target…
Siham MB
- 29
- 10