In my pl/sql procedure I'm calling a webservice and it is returning me a varchar2 which contains xml tags.I want to pull values for each element. any one can provide me procedure/function to pull data from it.Below is the output from webservice.
connect the following concepts/information I've been collecting. This is not my field but I'm interested in filling some of mine conceptual/technical gaps.
From a JDBC perspective, one of the benefits of Prepared (and so Callable) statements have over the regular ones is that the statement is "compiled"(*) once and then reused (performance gain).
(*) for SQL statements: building of parse tree and exec.plan
In which way can this notion be extrapolated to invocation of Oracle Stored Procedures through CallableStatements? (After clearing my doubts, I may end concluding that the only relevant feature of CallableStatements is their capacity to deal with stored procedure invocations)
According to procedure's precompiled execution plan SQL compilation implies execution plans generation PL/SQL compilation implies P-code generation and, SQL statements (from PLSQL code) are treated no differently by Oracle than SQL from Java or C/C++. These SQLs will be parsed and execution plans for those SQLs created. ... When the PL code executes the SQL statement, only then does the SQL engine receive the SQL, parse it, and create an execution plan for it.
Therefore, even when the stored procedure can be parsed and cached in SGA (through the OracleConnection.preparedCall("proc") invocation), the SQL statements won't be effectively compiled until they are executed, right? And going deeper, will those SQL statements be cached to be reused in future invocations of the containing stored procedure? Is this a characteristic of the regular stored procedure execution in Oracle? or is it due to the CallableStatement "origin"?
One of the procedures that am working on is failing with ORA-0000: normal, successful completion error.
The procedure has got several update and delete statements and have logging enabled after each step. The problem with that again is, each time the log table gets updated thereby losing the history of until what point the procedure ran successfully.I have this issue only in production environment and unable to simulate it in dev environment which limits my options of troubleshooting the procedure code. I was using SQLERRM in the code.
Is there a way I can identify the bad records/ record causing this issue? Am very new to PL/SQL and do not know how to proceed with this.How do you debug this sort of issues??(where one procedure internally invokes another one which again invokes other one etc)
I have an table 'tbcontrol' where for each row I can have one or more sql statements (DML or DDL) in an column.
Example:
task | sql_scripts 1 | create table t1 (c1 number); insert into t1 (c1) values 100; 2 | create table t2 (c2 number); alter table t1 modify c1 not null default 0; 3 | alter table t2 modify c2 not null default 0; alter table t1 add c10 varchar2(10); create table t3 (c3 number not null default 0;
I need to create a procedure where I can pass an task number to execute the sql statements which are in the column 'sql_scripts'. Something like this:
create or replace sp_run_scripts (v_task number) is v_scripts varchar2(2000); begin select sql_scripts into v_scripts from tbcontrol where task = v_task execute immediate v_scripts; end;
But, here I'll have some problems:
- The script will have semicolons (EXECUTE IMMEDIATE doesn't support); - If I put a begin/end like a PL/SQL script, I can only run DML statements because DDL statements have implicit commit. - I can't use a cursor for execute each statement at a time because the scripts have multiples-rows. Even if I search for semicolons to 'separate' the scripts in the cursor (using SUBSTR and INSTR functions), I'll have problems with the semicolons between '' (quotations marks).
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
I am developing android application connected to Oracle database using PHP, and I use PL/SQL statements with semantic technologies provided in Oracle(specifically, SEM_MATCH, SEM_MODELS, SEM_RULEBASES or something like this).
However, it doesn't work properly...the connection between android and Oracle works well, though. Also, the query statement is implemented well in the sql developer.The whole php code is shown below :
I am using oracle 10g i have a table on my computer that i made for a friend when i load it on their computer the select statements say no data found if i use select * from table name all the data will show
if a column name select * from table name where duty_date = '05-JAN-11'no data found
I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function.
how can i prove it through examples. can i see the explain plan for a procedure and a function or is there any way to prove which one is faster in execution.
I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.
I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.
The code for the procedure is
CREATE OR REPLACE PROCEDURE add_new_classes (p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number p_course_id classes.course_id%TYPE, p_period classes.period%TYPE, p_frequency classes.frequency%TYPE,
[code]....
I then use this to test it
BEGIN add_new_classes(1002,'first','daily',3002); END;
and the error I get is
Quote:ORA-06550: line 2, column 4: PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES' ORA-06550: line 2, column 4: PL/SQL: Statement ignored 1. BEGIN 2. add_new_classes(1002,'first','daily',3002); 3. END;
I know difference between procedure and function.if we want a return value from procedure, we can have OUT parameter. Similarly with function, in addition to returning a value from function, it can also send an OUT parameter value as a return value. That means, in one or the other way we are able to get a return value from both program units. Normally, I would fill a OUT variable with error message when an exception occurs. I use this varaible,after procedure call, to detect if an exception occurred or not. Similar task can be performed by a function, it returns a true/false and a value thru OUT variables.
both program units return values in the form of OUT parameters. Where exactly should we use a function, where exactly should we use a procedure?
I have question in procedure execution and function execution oracle database. I want know that which is faster in execution procedure or function. Can i see the time taken by procedure and select query only time.
Interviewer asked me "Tell me Diff. between Stored procedure vs. Function ".....I given technical answer which is mentioned in my Faq..But he asked me , dont gv me answer in technical manner..He was interested in which case u use Stored procedure and Function....
difference between Procedure and Function.We Know that Procedure is Basically Used to perform Actions and Functions are Basically Used for Calculations but what would be the exact difference since Actions/Calculations can be done in both Procedure and Functions.Which factors decide whether we need to write a Procedure and in which cases we need Function.
I would like to know if it is possible to get the arguments of a function (or procedure), in the same way as - for example - the "arguments" object in JavaScript.
I have seen the table ALL_ARGUMENTS, for example, that is providing a lot of interesting details, but I still miss the most important for me: the values of the arguments, not only the name, or type, etc...
Something that could be called as soon as the function (or procedure) is run, at the beginning of its process, and that would give a kind of array of arguments values.
i'm trying to make a sp/function for inserting a record and return the new index.The previous code used regular inserts and needed an additional round-trip to get the id before inserting,, since this is part of a import routine performance is an issue.
CREATE OR REPLACE PROCEDURE SaveAisHeader ( P_Id Out Number, P_ImportedOn IN Date, P_Aisimporttypeid In Number, P_Description In Varchar2, P_Importedby In Varchar2 [code].....
How can i test my Procedure /Function/Package, i mean how to unit & integration test my Procedure /Function/Package. Is there any tool available in the market, or we have to test in manually .
I have started reading ORACLE 11g R2 PL/SQL programming doc, and I am stuck at bind variable. I just copied the example in the books and found error -
First I have written below procedure and compiled successfully -
CREATE OR REPLACE PROCEDURE FORMAT_STRING ( string_in IN OUT VARCHAR2) AS BEGIN string_in := '[' || string_in || ']'; END FORMAT_STRING; Then I have written below function and also compiled successfully - create or replace function join_stringsĀ (str1 varchar2, str2 varchar2) [code]......