Help - Search - Members - Calendar
Full Version: xml output (anoymous blocks)
Oracle DBA Forums > Oracle > Oracle Forum
andy_9
HI,

I would like to get the output of a xmltype-variable in anonymous block. I've tried to get it with dbms_output.put_line but it didn't work (think i've read that it can't display xmltype). Error message is "wrong number or type...".

What else could I do to get the xmltype-variable displayed in an anonymous block?


Cheers!
burleson
>> I would like to get the output of a xmltype-variable in anonymous block

Huh? You mean "from" a PL/SQL block?

Here is how to write out XML:

http://www.dba-oracle.com/t_format_output_html_xml.htm

Note that the XML calls are embedded into the SQL, not PL/SQL.
andy_9
Hiya,

no, i mean something like that:


declare
-- Local variables here
i integer;



FUNCTION func_Database RETURN xmltype
is
xml xmltype;
BEGIN

SELECT xmlelement ("Database",
xmlelement ("CPU_Count", (SELECT value FROM V$PARAMETER WHERE UPPER(name) like '%CPU_COUNT%')),
xmlelement ("Server_Standby_Value", (SELECT value FROM V$PARAMETER WHERE UPPER(name) like '%FAL_CLIENT%')),
xmlelement ("Client_Standby_Value", (SELECT value FROM V$PARAMETER WHERE UPPER(name) like '%FAL_SERVER%'))
)
INTO xml
FROM dual;

RETURN(xml);
END func_Database;


begin

i := func_Database;
dbms_output.put_line(i);


end;

More like that. So, that the function gives you a xmltype variable and you call this variable somewhere else (here with dbms_output.put_line())


Do you know what I mean?

Thanks!
burleson
>> So, that the function gives you a xmltype variable and you call this variable somewhere else (here with dbms_output.put_line())


Like this sample?

http://www.oracle.com/technology/sample_co...type/index.html

CODE
-- Create a database function that returns the inserted record as XMLTYPE.
CREATE OR  REPLACE FUNCTION getPurchaseOrder(reference VARCHAR2)  RETURN XMLTYPE AS    xml XMLTYPE;  BEGIN    SELECT VALUE(p)    INTO xml    FROM PURCHASEORDER p    WHERE EXTRACTVALUE(VALUE(p),'/PurchaseOrder/Reference') = reference;    RETURN xml;  END;
/


and here? http://www.dbasupport.com/forums/showthread.php?t=42033

CODE
create or replace function sp_GetCriticalityXML return XMLType
as
critInstance XMLType;
begin
SELECT XMLELEMENT ( "Criticality", XMLAGG ( XMLELEMENT("option", XMLATTRIBUTES (criticality_id),criticality_name) ) ) AS "result"
INTO critInstance
FROM criticality;
return critInstance;
end;
/
andy_9
No, I don't want to create a function in the database. I want to do it with a private function as I showed in my sample. It's no problem to do it as in your sample. You can retrieve the xmltype variable from the function with select getPurchaseOrder from dual;

But you can't do it like that for a private function which is not created in the database. You can't say select getPurchaseOrder from dual;

My function shouldn't be created in the database...just for the script and I want to retrieve the xmltype-output from that private function in the same script but I don't know how I can select that xmltype-output from the private function.
burleson
Hi Andy,

>> I want to do it with a private function as I showed in my sample

As you know, this is a DBA forum, but I'll see if we can get a developer to pop-in and assist you!
Steve
Hello Andy.

Like Don, I'm not sure why you don't simply have your app do:

select func_Database() from dual;

Unfortunately if you want to make DBMS_OUTPUT work with XMLTYPE, you must write a procedure that will grab the XMLTYPE variable (which you already have), then use the getClobVal() method of the XMLTYPE variable to turn it into a CLOB. Then chunk it up to display nicely with DBMS_OUTPUT using a loop.

Pseudo:

CODE
declare
xml xmltype;
theclob clob;
begin

xml := func_Database;
theclob := xml.getClobVal()

for i in 1..(calculated max) loop
  dbms_output.put_line(use substr and instr with theclob here);
end loop;
dbms_output.put_line(use substr and instr with theclob here to show the rest);

end;
andy_9
Hi Steve,

I don't want to make DBMS_OUTPUT. I think I have to.

As I said the "select func_Database() from dual;"-Statement works just for a function which you store in the database with "Create or Replace function...". If you just want to create this function in your script with "function ..." then you can't call this function with "select func_Database() from dual;" because you didn't create it in the database.

Do you know what I mean?

My script shouldn't store anything on the database. That's why I don't want to create the function in the database with "create or replace". I just want to have this function created (and available) in my script which should run against the database.
aussie_dba
"My script shouldn't store anything on the database."

OK, so you want to write the equivalent of the XML translater.

What's your question?

If you are asking for the PL/SQL source code for these, it isn't published.

The Oracle supplied stored procedures are pre-complied executable code.

You could, however, use Jato code, and embed it in PL/SQL.

http://www.javaworld.com/javaworld/jw-05-2...0525-jato3.html
andy_9
CODE
declare
-- Local variables here
i integer;


FUNCTION func_Database RETURN xmltype
is
xml xmltype;
BEGIN

SELECT xmlelement ("Database",
xmlelement ("CPU_Count", (SELECT value FROM V$PARAMETER WHERE UPPER(name) like '%CPU_COUNT%')),
xmlelement ("Server_Standby_Value", (SELECT value FROM V$PARAMETER WHERE UPPER(name) like '%FAL_CLIENT%')),
xmlelement ("Client_Standby_Value", (SELECT value FROM V$PARAMETER WHERE UPPER(name) like '%FAL_SERVER%'))
)
INTO xml
FROM dual;

RETURN(xml);
END func_Database;


begin

i := func_Database;
dbms_output.put_line(i);


end;


This is what I want to do
- Define a function in a sql-script without storing the function in the database (so, no "create or replace function...")
- retrieve the xmltype output from that function in the same script

Is that possible or do I have to create the function in the database (with "create or replace function...") to retrieve it (i.e. select func_Database() from dual)?
HAL9000
You are asking how to call an external procedure from PL/SQL?

Why?

You can cal shell scripts from PL/SQL:

C approach:
1. Create a file for the external procedure code:

shell.c
=======
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void sh(char *);
void sh( char *cmd )
{
int num;
num = system(cmd);
}

2. Compile and link the C code into a shared library:

make -f $ORACLE_HOME/rdbms/demo/demo_rdbms.mk extproc_nocallback \
SHARED_LIBNAME=shell.so OBJS=shell.o

Log into SQL*Plus to perform the remaining steps.

3. Define the shared library in Oracle:
CREATE LIBRARY shell_lib IS '<full path to shared lib>/shell.so';
/

4. Create the PL/SQL wrapper procedure:

CREATE OR REPLACE PROCEDURE shell (cmd IN CHAR)
AS EXTERNAL
NAME "sh"
LIBRARY shell_lib
LANGUAGE C
PARAMETERS (
cmd STRING);
/

5. Call a shell script:

SQL> exec shell('sh myscript.sh');

PL/SQL procedure successfully completed.
burleson
Hi Andy,

>> This is what I want to do - Define a function in a sql-script without storing the function in the database (so, no "create or replace function...")

But why? PL/SQL is native to Oracle, and you cannot execute an anonymous block with Oracle?
andy_9
QUOTE (burleson @ Jul 18 2008, 02:31 PM) *
Hi Andy,

>> This is what I want to do - Define a function in a sql-script without storing the function in the database (so, no "create or replace function...")

But why? PL/SQL is native to Oracle, and you cannot execute an anonymous block with Oracle?


Hm...because I don't want to store anything in the database. That's the reason. I don't want to leave any traces.
aussie_dba
"because I don't want to store anything in the database. That's the reason. I don't want to leave any traces.

Then you should not use a PL/SQL anonymous block. PL/SQL is interpreted, and it is read into Oracle for execution, where the source remains for awhile.

"I don't want to leave any traces."

Is this for some unsavory purpose?
andy_9
QUOTE (aussie_dba @ Jul 18 2008, 08:38 PM) *
....

"I don't want to leave any traces."

Is this for some unsavory purpose?


No, definetly not. The person who owns the database will know about that script and what it will do. I think its better to leave no traces because it just has to run for one time and thats it.

What I could do is to create and store the functions in the database and delete them at the end of the script. But the other way without storing anything would be great.
aussie_dba
If you want to protect your source code, maybe consider compling the PL/SQL into a binary executable.

http://www.dba-oracle.com/t_compiled_pl_sql.htm
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.