ALL_SOURCE table - SQL & PL/SQL

Dear all,
Is there any method to hide my package body? I do not want the users to get my source code from ALL_SOURCE table, thanks.
Regards,
George 

Use the wrap utility that Oracle provides to encrypt the package source. It should be in your oracle_home/bin directory. 

Hi,
using wrap utility you will get the the .pll file. Which contains the codein hexadecimal format, when u compile this file instead of the .sql file, your code will be in hexa decimal format, so that you can't figure out the exact text from user_source.
just run this from the ORACLE_HOME/BIN OR SET THE PATH TO THIS DIRECTORY.
wrap iname=inputfilename.sql oname=outfilename
all the best
Khaleel 

Hello,
Thanks for reply.
Actually in my environment, I can select the package (header) from all_source, but I can't find the package body. So I just wonder how the DBA does this.
Regards,
George

Hi George,
I think that's because only the DBA has the privilege to view the source code. Your DBA has set the privilege of the user login so the user login can only view the header.

Related

From PLSQL - Need to write a text file

Hello
I need to write a text file from data retrieved from many tables in PLSQL to the UNIX box.
Thanks
Paul
null 
Check package utl_file 
You can use package UTL_FILE to write out to a file. Your DBA has to make initialization changes to support this. They might already have it setup. Run this query:
SELECT value
FROM v$parameter
WHERE name = 'utl_file_dir';
To see whether it has been configured properly. The value is the directory where you can create and write files.
null

Forms , Reports and SQL Loader

hi! my problem's like this: i have an ACCOUNT_T table which has 500 account numbers. A report (.rep) is made to generate the list of account numbers but there are account numbers that are to be excluded (say, only 350 should be printed). The user has the list for exclusion in excel file (or text file). What should i do to exclude the list of acct numbers in excel by just passing the filename or path as a parameter? is it possible to use sql loader to upload the list to a temporary table (using Forms as the user's input screen)?
Any other suggestions?
Thanks!
Gladys 
Sql Loader will help u to load data in the database table.
To exclude values, u have to place trigger on the account number field in main body.
TRIGGER
<when condition match>
Return (False);
<otherwise>
Return (True);
Ahmer
null 
hi, thanks for your reply.
how do i use the sql loader using Forms? can i use a when-button-pressed trigger to call a procedure that will upload the data?
will that require basic sql statements only?
thanks. 
I don't think you should use SQL*loader. Please try to use Oracle supplied package: UTL_FILE if your file is on the server, TEXT_IO if your file is on the client. 
i've used text_io several times, but i haven't tried using it to upload the data to a database table. is there a way?
thanks! 
I also need to load data from text file. Where I can find Text_IO packege?
I am using form 5.0
Thanks in advance 
hi ming fang! thanks a lot! i read some more documentations about TEXT_IO package. It works! thanks again!
hello gs, you can find and read about text_io package on >documentation > forms
built-in packages, or just type on the search box the key word text_io.regards,
gladys

Rename a Procedure/Function. How?

Hi folks!
what is the statement to rename a procedure whether it is a stand-alone or a packaged one?
I used
ALTER PROCEDURE <existing_procedure_name> RENAME TO <new_procedure_name>;
This statement is throwing some internal error message.
Your favour'll be deeply appreciated.
Thanks,
PCZ. 
You cannot rename a procedure or function. The only option is drop and recreate it. 
I dont' think this is possible.
You should drop the object and recreate it. 
Hi,
You cannot rename a stored PL/SQL program unit, public synonym, index, or cluster.
You can select the text from user_source, spool that to a file, then add "create or replace " to the top of it, and "/" at the end, and have a runnable script, or one that you can easily edit, then run. You may have to set some SQL*Plus settings appropriately to avoid getting long lines wrapped, or getting extra lines included.
OR
1. create a synynom with the name you want that points to the old name
OR
Drop and recreate wihth a new name 
Ok guyz, thanks alot for you suggestions and advices.
Kudos to you all.
Cheers,PCZ.

Create directory issue in Store Procedure

Hi Guys,
can any one help?
I am using XP, Oracle 10g, sql developer and Sql Plus.
I am trying to create directory in storeprocedure, but it not let me to do that thing. below is the statement i am using
create or replace directory public_access as '/tmp/public_access';
please let me know hoe can i initialize my dir object in store procedure.
Thanks 
user13012136 wrote:
Hi Guys,
can any one help?
I am using XP, Oracle 10g, sql developer and Sql Plus.
I am trying to create directory in storeprocedure, but it not let me to do that thing. below is the statement i am using
create or replace directory public_access as '/tmp/public_access';
please let me know hoe can i initialize my dir object in store procedure.
ThanksTypically best NOT to do something like this in a procedure, but there are cases where it's needed.
In those cases, you need to use Native Dynamic SQL.
execute immediate 'create or replace directory public_access as ''/tmp/public_access''';  Assuming your syntax was correct before (i just copied and pasted what you had, adding quotes where needed). 
If you use exp, then you should do something like this:
CREATE OR REPLACE DIRECTORY PUBLIC_ACCESS AS 'C:\oracle\tmp';
For example. That directory has to exist already in the OS and the user oracle needs privileges to it.
Regards,
Mario Alcaide
http://marioalcaide.wordpress.com 
Thanks,
Where do i need to put this transaction
Create storeprocedure
As
begin
end; 
user13012136 wrote:
Thanks,
Where do i need to put this transaction
Create storeprocedure
As
begin
end;Do you REALLY need to do this in a procedure?
If so ... it would go somewhere after the BEGIN statement. 
Between begin and end.
Regards,
Mario Alcaide
http://marioalcaide.wordpress.com 
No Guys, its not working.
it is giving me same error.
Please any other way i can complete this thing today. 
it is giving me same error.please post that error... 
Invalid path. Create directory or set UTL_FILE_DIR. 
user13012136 wrote:
Invalid path. Create directory or set UTL_FILE_DIR.You realize that the folder (where your directory points to) needs to be on the SERVER, and NOT the Client machine? 
That folder is on server.
I am working on server.
Thanks 
user13012136 wrote:
That folder is on server.
I am working on server.
ThanksAnd do you have permissions on the folder /tmp/public_access on the server?
And is the server a windows machine? Because in your initial post you say
"
I am using XP, Oracle 10g, sql developer and Sql Plus.
"
and /tmp isn't a windows valid path as far as i'm aware. 
Hi Tuby,
I had put that line as example
Oracle not let me to enter the statement
Create or replace directoy abc as 'c:\Temp';
because this line is not there dur to system not let me to put that transaction i am getting invalid path error.
we need to find out how to put above transaction in store procedure.
or any sugessions. 
I think it's time to send you here ...
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5007.htm#SQLRF01207
That in conjunction with previous answers will be your final answer. 
Hi Tuby,
This link hasnt got any thing relating about "how to use create or replace directory abc as 'c:\Temp" in procedure.
I am getting error on how to declare that statement.
Please u can try on your machine as well, if this statement works in store procedure then i have some problem in access if not then we need to look at how to use this statement in store procedures.
I need to resolve this issue on very high priority, so please any help will highly be appreciated.
Thanks

used SPOOL commnad in procedure

Dear All.
I have tabel with millions of records and wants to create a seperate files for given criteria. to do this i'm planing to use a procedure with cursor. pls let me know how to use spool command in procedure or pls sugest alternative way.
Thank you 
Bagiya wrote:
Dear All.
I have tabel with millions of records and wants to create a seperate files for given criteria. to do this i'm planing to use a procedure with cursor. pls let me know how to use spool command in procedure or pls sugest alternative way.
Thank youHi,
You can use UTL FIle package to store the output onto the file
Genric code
CREATE OR REPLACE PROCEDURE test_prc AS
v_output UTL_FILE.FILETYPE;
BEGIN
v_output:=UTL_FILE.FOPEN('/rman/report/textfiles','TEST.TXT','W');
UTL_FILE.PUT_LINE(v_output,'HI !!!Its Test');
UTL_FILE.FCLOSE(v_output);
END test_prc;Hope this helps
Regards,
Achyut 
spool is an sqlplus command afaik.
use UTL_FILE inbuilt package instead.
see docs for example
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/u_file.htm#i1004619 
thanks for reply. this is writing to oracle directory. i need to save out put in local pc 
refer this
Re: Read and write files from procedure
regards
HN 
create own directory ;:
create or replace directory dir_temp as 'c:\temp';
for local client pc:
create or replace directory dir_temp as '\\hostname\temp';
try this it will work for client m/c.
Edited by: 856991 on May 10, 2011 1:28 AM
Edited by: 856991 on May 10, 2011 1:33 AM 
Bagiya wrote:
thanks for reply. this is writing to oracle directory. i need to save out put in local pcIn this case you need a client tool. SQL*PLUS is such a client tool and SPOOL is the proper command to do it. However you can't call SPOOL inside pL/sql. You must find a way to move the data from pl/sql to Sql*plus. The best way is simply to write the required output into a table (insert command in pl/sql). Then SPOOL + SELECT in sql*plus to write the output to a local file.
Even better would be to use a browser based framework like Apex4.0. There you would put the select into a report and then let the user decide wether they want the report as a pdf, csv file or as an email sent regularily. 
856991 wrote:
create own directory ;:
create or replace directory dir_temp as 'c:\temp';Totally wrong. The server has no access to the client! 
Bagiya wrote:
thanks for reply. this is writing to oracle directory. i need to save out put in local pcSo, you're expecting the oracle database (which is where your procedure is running) to go out across a network, bypass all network security and all computer security to break into a client computer and write files to it's hard drive?
You're thinking the wrong way around. If you want files on a local pc, you need to have a locally running application (which may be a web browser based application too) which queries the data and writes the file locally. You can't expect the processes on the oracle server to do it.
There are ways to get files to a local pc if needed as Hitesh has indicated with the link provided, i.e. using FTP or using a mapped drive, but ideally you should take a step back and look at your requirements. Files shouldn't really be written to local PC's, but should be stored centrally and accessed from there i.e. the mapped network drive should be to a location on the server, not the other way around. 
got ur solution????if not what is the problem u r facing now??? 
getting following error
PLS-00201: identifier 'UTL_FILE' must be declared
Edited by: Bagiya on May 10, 2011 3:19 PM 
go through this dis:
identifier 'UTL_FILE' must be declared

Categories

Resources