SQL & PL/SQL :: Find Names Of All Stored Proc Invoked On Execution Of Main Api?
Oct 1, 2013
I have a main procedure in oracle which invokes many procedures inside it. These internal procedures also calls functions and procedures inside it.This continues to many levels.
For ex:
Proc A
call c
call d
end............
[code]...
This loop goes on and on . I want to find the names of all procedures invoked at run time when main api is executed. Is it possible to find all of them using toad ? Is there any tool for doing this ?
I have 20 tables. In all 20 tables, some of column names are same and some are different. I need to find all column names in all 20 tables that have same names.
Having: 3 databases: CYR1,CYR2,UTF Database links was established CYR2 -> CYR1, UTF -> CYR1 Function GETALL is on the CYR1 site SQL> desc GETALL Parameter Type Mode Default? --------- -------- ---- -------- (RESULT) VARCHAR2 FMT VARCHAR2 IN
[code]....
My questions: 1) What could be reason that oracle would look so deeply in source of REMOTE procedure, especially as because remote procedure is in VALID, compiled state?!! 2) Could we classify such restrictions as a bug?
This is how I call the remote function: declare v varchar(300); begin v:= getall @orcl8.oegc.tgk11.com@csk ('%slu%',129); dbms_output.put_line(v); end;
This is "offending" statement in getall (basic static SELECT), that I had to throw away for call to getall to succeed: select .... from ПРАВА_ПРОГ_ДЕТАЛИ ...
ПРАВА_ПРОГ_ДЕТАЛИ would occupy exactly 32 byte in UTF8 (2*15_cyrillic_chars + 2 underscores), but in CYR1, this table name is absolutely valid, only 17 chars long, because Cyrillic characters are represented by one byte!
In my Organization I am trying to install oracle ODTwithODAC112030 but one Error showing that is java virtual machine could not find main class program will Exit.
Server Configuration Dell power Edge r510 Windows 2003 64 bit serv PAck 2 Citrix Presentation server 4.5 .
I created the following stored procedure which I am calling from a script. I compiled my Stored Procedure with Debug Info. For some reason Execution jumps from the second BEGIN to the END statement.
Since the SP compiles w/o any errors, I suspect I have a logic error.
Stored Procedure: CREATE OR REPLACE PROCEDURE VALIDATE_PATIENT_NEW ( VALIDATED OUT int, LAST_NAME IN VARCHAR2 DEFAULT NULL, FIRST_NAME IN VARCHAR2 DEFAULT NULL, DOB date DEFAULT NULL, PAT_NUMBER OUT int, FACILITY_KEY OUT CHAR ) AS BEGIN /* SELECT * */ BEGIN [code].......
I have a set of code being perform in the package body of a program unit I want to debug step by step and see what values are being stored during executing or running the form
/* Without HP and deposit, Down Payment(GST) = Total(GST) */ if nvl(v_deposit, 0) = 0 and nvl(v_loan_amt, 0) = 0 then v_gst_fr_dps := 0; v_gst_hp := 0; v_gst_bal_pay := v_total_gst; [code].........
Like in the above example I want to see the value in v_gst_fr_dps,v_gst_hp,v_gst_bal_pay etc when executing and display in the screen.
I have a requirement of pulling the data from a field in horizontal form of Advanced Supply Chain Planning responsibility(ASCP). When I go into help-> Diagnostics, it shows the block name of the form as 'Horizontal Plan' and field name as 'Pivot Table'. Not pretty sure on how to pull the data source into horizontal form with the info I have.
Is there any oracle dictionary view which captures the queries being run by users on the database and time taken to execute those queries?We need to find out the OS user not the database user since we have to identify the users who are executing long running queries.We require this basically to monitor the long running queries on the database.
There is one DBMSJOB i created schedule to run every day at 22:00 hrs. The jobname is IT_TO_DUM_LOADING and the job type is Stored procedure.
I queried all_source and found stored procedure does not exist in database.
SQL> select text from all_source 2 where type = 'PROCEDURE' 3 and name ='IT_TO_DUM_LOADING';
no rows selected
But i could see the job details in dba_scheduler_jobs view. I cannot use WHERE clause in dba_scheduler_jobs to restrict and find only the job row as i receive time zone error. How it is possible to view the job in dba_scheduler job view whereas i could not find the stored procedure of its in all_source?
I'm using Oracle 11g enhanced ADD COLUMN Functionality. Adding new columns with DEFAULT values and NOT NULL constraint no longer requires the default value to be stored in all existing records.
Sometimes we change DB columns from NOT NULL with DEFAULT to NULL with DEFAULT. This operation "materialize" column default. Is there an easy way (Dictionary view) how to find, that COLUMN default value is stored as metadata or is "materialized" ?
Oracle RDBMS version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE OR REPLACE FUNCTION fn ( p_salesrep_id IN jtf_rs_salesreps.salesrep_id%TYPE, p_org_id IN jtf_rs_salesreps.org_id%TYPE, p_cnf_date IN emcint_ord_headers_all.creation_date%TYPE
[Code]....
Invoking Functions
select fn(-3,293,'1/1/1952'), resource_id from jtf_rs_salesreps where rownum < 5 ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type
I turned on ARCHIVELOG in my main database, anticipating GoldenGate replications and standing up a physical standby database. However, during the process I forgot to create a new SPFILE for my database, meaning that the next time the database cycled, the archive log destination would revert to the Oracle default (rather than the mount point I had set up to receive them) $ORACLE_BASE/fast_recovery_area
. I discovered this because my database hung..... In looking at the ALERT LOG, I discovered that the logs were going to fast_recovery_area, and it had run out of room. OK, so I did an alter system set db_recovery_file_dest_size= 5G scope=both; at which point in time I was notified that no active SPFILE existed! I moved out all of the logfiles that were in the fast_recovery_area, but the database seemed to still think that it was full.
I've managed to get everything fixed and put back together, but my remaining question is: After I freed up all sorts of space in the fast_recovery_area, why did/does the database still think that area is full?
I have 3 main tables as projects, tasks, clients. Then I have a history table for each. I created a trigger on update/delete that takes the old records and inserts into the history.
Now, I need to retrieve the history but I have no clue how to do so. I need to join these 3 history tables with:
1. select from projects_history with projectID = 1 2. if no records, then go back to projects and get projectID = 1
3. select from task_history with taskID = 1 4. if no records, then go back to tasks with taskID = 1
5. select from client_history with clientID = 1 6. if no records, then go back to client with clientID = 1
where client.taskID = task.taskId and task.projectID = project.projectID.
select * from (select pa_request_id ,max(status) status ,max(approved_amount) approved_amount ,min(level_id) level_id ,max(req_amount) req_amount from target_aggregation_attendee group by pa_request_id) where status = 'Approved')
My Main Query:
select TARGET_EMPLOYEE.FIRST_NAME||' ' ||TARGET_EMPLOYEE.LAST_NAME as Requestername, TARGET_EMPLOYEE.GE_ID as requesterGEID, TARGET_ATTENDEE.FIRST_NAME||' ' ||TARGET_ATTENDEE.LAST_NAME as AttendeeName, TARGET_ATTENDEE.ATTENDEE_TYPE_FLAG as Attendeetyflg, TARGET_ATTENDEE.US_GO_ATTENDEE_FLAG as usgoflg, TARGET_ATTENDEE.COMP_GOVT_AGENCY_DEPT as Atcomp,
on clicking select.show,while running the form data(DATE and APPLICANT_NO) is being accesed from local database but after uploading the form the data is not coming from main database,even if the data is present in the main database.
Reference: Accessing PL/SQL Stored Procedures using a Web Service (<i>Oracle® XML DB Developer's Guide</i>).
The feature works fine. Have tested a number of custom written PL/SQL procs this way - using it as a web service, and SoapUI and UTL_HTTP procedures as clients, calling the web service.
Question.
Can one support WSS (Web Services Security) with this feature?
I've added a WSSE to the SOAP envelope header when making the call - using standard password (no digest) and default addressing. The orawsv XDB servlet accepts the call, parses the SOAP envelope, and successfully executes the relevant PL/SQL procedure.
It however uses Basic Authentication (schema name and password). Not WSSE.
As the WSSE authentication data is part of the SOAP header (and not envelope body), the relevant PL/SQL procedure of course does/can not see the WSSE details. (also would not make sense ito how XDB abstracts orawsv as a web service interface and allows standard vanilla PL/SQL procedures and functions to serve as web service endpoints).
So if WSSE is to be supported, it would likely mean it needs to be supported in XDB itself. And that is outside my little area of Oracle expertise.
Not much on the net (lots about UTL_DBWS), and just a couple of basic orawsv supports notes on Metalink.
I have a requirement to generate the XML file from a pl/sql procedure. The methods which I have searched are either generating the XML from a single query or they are explicitly adding the nodes and elements. I need the method where I can dynamically append child nodes to the file.
I have test in the main section of the layout in forms. I want to print the footer on the first page only. If I go to properties and select print object on first page, it still prints the footer in all pages. How can I force the footer on the first page only.
i have generate a report using report wizard but i want to know how join main section and head section , what ever you do in report wizard impact in main section ,i have done some head section ,when i run report, head section run first and main section in next page .. how to join both sections in single page output..
