-
Inserting data into table from storedprocs
Hi
Below code is one of the procedure 'AAINSERT_AA' inside a package named 'PORTF_MIGR'....We are calling the procedure to insert the values into a table...could u pls let me know the logic how the data is getting inserted into the table
DECLARE
I_KEY VARCHAR2(60);
I_KEY_SEQ_NR NUMBER;
I_O_DATE DATE;
I_C_DATE DATE;
BEGIN
I_KEY := NULL;
I_KEY_SEQ_NR := NULL;
I_O_DATE := NULL;
I_C_DATE := NULL;
PORTF_MIGR.AAINSERT_AA ( I_KEY, I_SEQ_NR, I_O_DATE, I_C_DATE );
COMMIT;
END;
-
How about looking at PORTF_MIGR.AAINSERT_AA code?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
The same procedure 'AAINSERT_AA' is called again inside to insert the data and are commiting also...
---create or replace package PORTF_MIGR --package starts
---procedure AAINSERT_AA starts
DECLARE
I_KEY VARCHAR2(60);
I_KEY_SEQ_NR NUMBER;
I_O_DATE DATE;
I_C_DATE DATE;
BEGIN
I_KEY := NULL;
I_KEY_SEQ_NR := NULL;
I_O_DATE := NULL;
I_C_DATE := NULL;
PORTF_MIGR.AAINSERT_AA ( I_KEY, I_SEQ_NR, I_O_DATE, I_C_DATE );
COMMIT;
END;
---procedure AAINSERT_AA ends
---package PORTF_MIGR --package ends
-
Could you please show us the output of...
Code:
select owner,
object_name,
subobject_name
from dba_objects
where object_name in ('PORTF_MIGR','AAINSERT_AA')
;
select *
from dba_synonyms
where synonym_name in ('PORTF_MIGR','AAINSERT_AA')
or table_name in ('PORTF_MIGR','AAINSERT_AA')
;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
hi Pavb,
output for the 1st query is
owner object_name sub_object_name
k port_migr#
k port_migr#
output for the second query is nothing..
-
I'm really sorry Sadha, first query should query: owner, object_name and object_type columns... not suboject_name - my bad.
Could you please run the revised version?
Code:
select owner,
object_name,
object_type
from dba_objects
where object_name in ('PORTF_MIGR','AAINSERT_AA')
;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Owner Object_name Object_type
k port_migr# package_body
k port_migr# package
-
Could you please extract package BODY k.port_migr and take a look at procedure AAINSERT_AA ?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|