I was trying to create types inside package like:
-create or replace package pack1 is
type udtable is table of integer index by binary_integer;
end;
-create or replace package BODY pack1 is
type udtable is table of integer index by binary_integer;
end;
Although this is not exact query what i have doubt in my mind. is it possible to create object types/collections inside/with packages?
I'm making a function A that does many calls to procedures in an other package B. To make this function more readable, I'd like to specify synonyms for the procedures in B. I only need the synonyms inside this function, I don't want to make database synonyms.
For example:
Function get_all_employees return clob
is
v_emp clob;
begin
[Code]....
I want to declare global variable inside package. get the correct query. how to assign value for that variable.
SQL> create or replace
2 PACKAGE new_pack
3 AS
4 g_id employee_details1.employee_id%type;
5 PROCEDURE emp_project(
6 st_date IN DATE,
7 Prj_id out VARCHAR2,
8 prj_name out VARCHAR2,
9 Prj_location out VARCHAR2);
10
11 END new_pack;
12 /
Package created.
SQL> CREATE OR REPLACE
2 PACKAGE body new_pack
3 AS
4 PROCEDURE emp_project(
5 st_date IN DATE,
6 Prj_id OUT VARCHAR2,
7 prj_name OUT VARCHAR2,
8 Prj_location OUT VARCHAR2)
[Code] ..........
Warning: Package Body created with compilation errors.
SQL> show error
Errors for PACKAGE BODY NEW_PACK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/12 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
I have a package which has couple of Procedures and functions. I use some constants between the functions and procedure and also functions r called from the other procedure and stuff...
So I declared all these in the header
Like this
CREATE OR REPLACE PACKAGE "PROCESS_HISTORY" IS
type table_name_i is varray(100) of VARCHAR2(30);
c_add constant number := 1;
c_del constant number := 2;
c_select_frm_tmp constant number := 1;
[code]......
Now is there anyway I could hide the stuff that I don't want outside people to see? or can I declare them (constants and some functions) some where inside the body and use them?
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
I am writing a procedure that will be called from a java wrapper.
The procedure do a lot of data manipulations and in between i am creating global temp table and saving the data into it for each request thats given as a parameter to the procedure. After all the processing i have to write the data from this global temp table into a physical table and atlast drop the temp table.
Create or replace proc_name ()
update table........
delete from ..........
CREATE GLOBAL TEMPORARY TABLE TSAAG
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50)
)
insert into............
drop table TSAAG;
End;
creating a global temp table inside a procedure is expensive...
Do we have anything like creating table before and calling the instanse of it in procedure.
Do we have any alternatives to this..
I'm trying to create a package body but i keep getting an error message saying package created with complication.
CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......
I'm trying to create multiple functions, add them then minus 1 in a package. I'm at my wits end as to what I'm not doing correctly as the package gets created fine but the package body is giving me compilation errors.
create or replace package addfunction as
function q1 (num1 IN NUMBER, num2 IN NUMBER) return number;
function q1 (num1 IN NUMBER, num2 IN NUMBER, num3 IN NUMBER) return number;
END;
/
[code].....
Warning: Package body created with compilation errors
When trying to create package I receive this error...[Error] PLS-00103 (8: 16): PLS-00103: Encountered the symbol "." when expecting one of the following:;
pointing to "." in the last line "END nondrg_prod.Readmissions;"
CREATE or replace PACKAGE nondrg_prod.Readmissions AS
TYPE Readmits IS REF CURSOR RETURN READMISSIONS_DATA_MH%ROWTYPE;
PROCEDURE open_rdmh (rdmh IN OUT rdmhCurTyp);
END Readmissions;
[code]....
I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.
View 4 Replies View RelatedI'm exploring converting our use of pls_integer to simple_integer. My concern is that simple_integer cannot be null. It seems safe to use for count(*), since count(*) cannot return null. But I am not sure about its use in collection methods like i := collection.count or for loops like for i in 1..collection.count
View 5 Replies View RelatedI have a driver table from which I need to update another table while, at the same time, record the fact that I have processed each record on the driver table.
The driver table will contain around 3.5 million records, therefore I intended to handle this using some bulk collections, with a LIMIT option so that I don't hit any memory problems.
I would also prefer to commit in batches, or at least handle exceptions using the SAVE EXCEPTION clause. The problem is I seem to be running into an error when trying to make the update to the driver table (the commented out code). With this in, I get the error:
ORA-01410: invalid ROWID
ORA-06512: at "CUST_MAIL_UPDATE", line 217
ORA-06512: at line 38
Can the CURRENT OF not work with the FORALL? What is my best approach here? If I use a FOR LOOP I lose my SAVE EXCEPTIONS exception handling.
The Procedure is as follows:
-- declare some object structures to hold the retrieved data
TYPE driver_rec IS RECORD (
account_no ext_driver.account_no%TYPE,
update_action ext_driver.update_action%TYPE,
customers_rowid ext_driver.customers_rowid%TYPE);
TYPE driver_recs_tt IS TABLE OF driver_rec;
-- cursor to get the records from the driver table
[code].......
I have one plsql table which is having 10,000 rows, i want to populate this values in a forms datablock, i have used the below query in pre-query of that block
declare
plsql_rec pkg.plsql_tab;
begin
plsql_rec := pkg.func;
set_block_property('blk_name', query_data_source_name, 'select e.ename, e.empno from table(plsql_rec) e');
end;
in the property of the block i set like below
Database Data Block = YES
Query Allowed = YES
Query Data Source Type = FROM clause query
Query Data Source Name = SELECT 1, 2 FROM DUAL
but while executing it says invalid identifier "plsql_rec",
collection1
============
SELECT o.object_id
BULK COLLECT INTO l_obj_info
FROM (SELECT n.node_id, n.object_id
FROM nodes n
START WITH n.node_id = 100
CONNECT BY PRIOR n.node_id = n.parent_node_id) n
INNER JOIN objects o ON n.object_id = o.object_id
WHERE o.object_type_id = 285;
collection2
============
SELECT *
BULK COLLECT INTO l_tab
FROM ((SELECT REGEXP_SUBSTR (i_l_text, '[^,]+', 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR (i_l_text, '[^,]+', 1, LEVEL) IS NOT NULL));
END;
collection3
============
SELECT o.object_id
BULK COLLECT INTO l_fin_tab
FROM objects o JOIN ATTRIBUTES att ON o.object_id = att.object_id
WHERE o.object_id = collection1.object_id
AND att.VALUE = collection2.val;
how to implement for loop in the collection3 to get the values from collection1 and collection2. i have tried in the below way
CREATE OR REPLACE TYPE LIST_OF_ATTRIBUTES_TYPE AS TABLE OF varchar2(4000);/
CREATE OR REPLACE TYPE LIST_OF_OBJECT_IDS_TYPE AS TABLE OF number(9);/
CREATE OR REPLACE FUNCTION f_get_objects_by_type_id ( i_object_type_id IN NUMBER, i_l_text IN VARCHAR2, i_scope_node_id NUMBER)
[Code]....
is it possible to remove duplicate values in plsql collections without using multistage operators ?
plsql collections output:
ID NAME
----- -------
001 A
001 A
002 B
003 C
004 D
005 E
005 E
005 E
expected output
ID NAME
----- -------
001 A
002 B
003 C
004 D
005 E
We can save Associative array in data base, if not why?.
View 3 Replies View RelatedI have a record type and table of records as follows
type rec is record
(
empid employee.empid%type,
ename employee.ename%type
);
type tab_rec is table of rec;
Suppose data from employee table is fetched into this collection
Can you pls clarify if we can refer to all the rows of empid in the above collection ?
something like tab_rec.empid without using the subscript for referring to the nth row
My requirement isto pass this list as input parameters to a procedure(PL/SQL).
I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').
What I would need is something like $$PLSQL_UNIT
I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:
e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);
Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?
The following code works
set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id from employees where first_name=:ab' into a using 'Donald' ;
dbms_output.put_line(a);
end;
but this one doesn't
set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id into :1 from employees where first_name=:2' using a,'Donald';
dbms_output.put_line(a);
end;
Am I not allowed to specify a bind variable with an into clause inside execute immediate ?
I have a cursor returning some value.
for each value returned by the cursor i need to traverse through 31 rows(1 row per day * no of days in the month).
E.g. if cursor returns service_name as xyz then for xyz there can be 31 rows(service may not be used on some days)
I need to go to all of them and take some values and move them to a flat file. how should that be done?
Attached File(s)
Query.png ( 20.99K )
Number of downloads: 9
I am trying to create a view of a query that i would be using regularly...the query contains a function call in it...can i use it..
If yes when i try to do it . It gives out an oRa-01031:in sufficient privileges.
I have a dynamic query which i want to run till it return zero records.
I am using WHILE loop for that but it is giving compilation error:
The query is
execute immediate ' Delete from tbl_archive_trade_list
where deal_id in (
select deal_id from tbl_archive_trade_list where trade_id in (
select trade_id from ' || main_trade_group_table || ' where tradegroup_id in (
select tradegroup_id from ' || main_trade_group_table || ' a , tbl_archive_trade_list b
[Code]...
I want to run this Query in While loop till the above command return 0 records.
I tried giving the above statement inside WHILE loop but it is failing.
Without the WHILE loop the above statement works fine and executed properly.
I have question.Using procedure inside the function ?can I get better performance?
View 8 Replies View Related i am trying to do something the following .. but I can't get the syntax correctly for the select statement inside the secondary_loop ...
EmailBodyHTML := '';
main_loop := '';
secondary_loop := '';
[Code]....
I am having a view say name vw_mytable , i need to call this view inside the stored procedure it is saying as Grant execute on
usera.vw_mytable to userb;ORA-02204: ALTER, INDEX and EXECUTE not allowed for views
how do i create a store procedure to call a view from another user.Usera is having a view and i need to create procedure in userb and call usera.vw_table.
DECODE(FIELD_1,NOT NULL,'working',NULL)
Is it possible to do such? else how to proceed so?
i want to put a variable inside a view. create or replace view loading as
SELECT H.ORGANIZATION_ID ORG_ID,H.HEADER_ID,H.REQUEST_NUMBER, H.DATE_REQUIRED ISSUE_DATE,H.ATTRIBUTE1,
H.ORGANIZATION_ID,L.INVENTORY_ITEM_ID,SS.SALES_ID,S.SECONDARY_INVENTORY_NAME SUB_INV_CODE,S.DESCRIPTION SALESPERSON,I.SEGMENT1 ITEM_CODE,
[code]....
i want this condition to be like and to_date(H.DATE_REQUIRED,'dd-mm-rrrr') = to_date('Vdate','dd-mm-rrrr') i want Vdate as a variable parameter.
how to use desc inside pl sql block?
View 16 Replies View RelatedIn the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.
--drop package pkg_seed--drop type seed_tab CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number); CREATE or replace TYPE seed_tab AS TABLE OF seed_rec; CREATE or replace PACKAGE pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED;END pkg_seed;/ CREATE or replace PACKAGE BODY pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED IS cursor cur_seed(vmaxrow number) is select rownum id, floor(dbms_random.value(1,1000) ) seed from dual connect by level <= vmaxrow; l_seed cur_seed%rowtype; BEGIN open cur_seed(maxrow); LOOP FETCH cur_seed into l_seed; pipe row(seed_rec(l_seed.id,l_seed.seed)); END LOOP; RETURN; -- the function returns a single result END getSeed;END pkg_seed;/ select * from table(pkg_seed.getSeed(200));